MySQL: Locks & Transactions

Locking and transactions helps to write programs where multiple user may do simultaneous request. Lets checkout some common scenarios and how to handle them.

Single Statements

Two single queries will never be executed parallel. If you call

UPDATE table SET field = field + 1 WHERE id = 1

twice, you will always increment the field by 2 (thus no parallel raise condition is possible).

Another case is that one is inserting a new row where `id` is an auto increment field

     $stmt         = $db->prepare('INSERT INTO table (value) VALUES (?)');
     $stmt->bind_param('i', $value);
     $stmt->execute();
     echo $db->insert_id;

This will generate a separate row with a separate id for each query. Transaction or locking is not needed in these cases.

Multiple Statements

Now lets image we do multiple statements

$balance = "SELECT BALANCE FROM ACCOUNT where id = 1";
$balance = $balance - 20;
UPDATE ACCOUNT SET BALANCE = $balance WHERE id=1;

if this code is executed in parallel, it could be that only 20 € are removed from your balance and not 40 € as expected due to raise conditions. This can be fixed with a transaction and a lock

begin;
$balance = "SELECT BALANCE FROM ACCOUNT where id = 1 FOR UPDATE";
$balance = $balance - 20;
UPDATE ACCOUNT SET BALANCE = $balance WHERE id=1;
commit;

The commands `begin` and `commit` are the boundaries of our transaction. When two transactions are called parallel, they still may interfere each other. Thus its important to lock the row with `id=1` by `FOR UPDATE`. This does prevent anyone from reading or updating this row outside our transaction, until the transaction is committed. If this is new to you, I recommend you to check out https://stackoverflow.com/a/4227957/2311074.

If one wants to make the row readable for other processes, but not writable, one has to change it to

    SELECT BALANCE FROM ACCOUNT where id = 1 LOCK IN SHARE MODE

If a transaction waits a long time for a row to be unlocked, it may rise a timeout error. Causing the transaction to roll back.

Table Locking

If you don't want for another process to update orread any row of a table you may lock the complete table with

    LOCK TABLES table WRITE

then only your session can write and read, other session are not allowed.

If you use

    LOCK TABLES table READ

then everyone can read, no one (not even you!) can write, and no one can WRITE-Lock the table.

The table is unlocked when

• the connection is closed
• or at the start of a new transaction
• or with the command

  UNLOCK TABLES
  

For more see http://www.mysqltutorial.org/mysql-table-locking/