Mysql – LOCK IN SHARE MODE

lockingMySQL

On dev mysql LOCK IN SHARE MODE is described as…

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends.

Does that mean that if I simply call

$dbc -> beginTransaction();

And either commit or rollback then the data read will be the latest given? Because it also says that using FOR UPDATE will block LOCK IN SHARE MODE from reading (ie make sure it is the latest info).

But if I wrap all my important updates and deletes in a transaction then is there still a need to lock rows, as above it says LOCK IN SHARE MODE will wait for a transaction to finish? Surely if it waits for transactions to finish then essentially I will always have the freshest data available by just using transactions and not having to lock when performing updates and deletes?

Best Answer

Performing SELECT...LOCK IN SHARE MODE can block UPDATE...LOCK IN SHARED MODE. Deadlocks can still occur. Needed rows for SELECT or UPDATE can still introduce locks you never intended to issue. What do I mean ?

It is sometimes necessary to internally lock primary key entries for your own good.

I once made three posts helping a developer realize this : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

Related Solutions

Shared Lock on IsolationLevel.ReadUncommitted in SQL Server

What is HOBT lock?

A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.

Why would I still get a S lock?

This happens on heaps. Example

SET NOCOUNT ON;

DECLARE @Query nvarchar(max) = 
   N'DECLARE @C INT; 
     SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';

/*Run once so compilation out of the way*/
EXEC(@Query);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;

PRINT 'READ UNCOMMITTED';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC(@Query);

PRINT 'READ COMMITTED';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC(@Query);

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

Output READ UNCOMMITTED

Process 56 acquiring Sch-S lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on HOBT: 1:72057594038910976 [BULK_OPERATION] (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 1:1163151189:0

Output READ COMMITTED

Process 56 acquiring IS lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring IS lock on PAGE: 1:1:169 (class bit0 ref1) result: OK

Process 56 releasing lock on PAGE: 1:1:169

Process 56 releasing lock on OBJECT: 1:1163151189:0

According to this article referencing Paul Randal the reason for taking this BULK_OPERATION shared HOBT lock is to prevent reading of unformatted pages.

MySQL Transaction Basics – Concurrent Transactions Result

There is no implicit shared lock in REPEATABLE READ isolation level.

You need to change the transaction sequence as follows:

ISOLATION LEVEL

You could switch to the SERIALIZABLE isolation level and disable autocommit. That way, the row is locked in shared mode implicitly.

If you want to stay with REPEATABLE READ or READ COMMITTED, you will have to call SELECT ... FOR UPDATE manually before doing the UPDATE.

QUERY (Optional Suggestion)

You should also experiment with

UPDATE ACCOUNT SET amount = amount - 100 WHERE id = 123;

along with disabled autocommit and SERIALIZABLE isolation level.

I suggested this because you are doing two queries (SELECT and UPDATE) to change the amount. You could just do it with one query.

Your Question

Since locking reads and not implicit with REPEATABLE READ, there is a possibility of 500 being used twice.

You asked

Is it possible to set the isolation level at a connection level?I.e. not touch the global isolation level?

Yes, you can set the isolation level within the session:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL   {
    REPEATABLE READ
  | READ COMMITTED
  | READ UNCOMMITTED
  | SERIALIZABLE    }