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 you want to know how shared or exclusive lock really work, checkout this link: https://stackoverflow.com/questions/32827650/mysql-innodb-difference-between-for-update-and-lock-in-share-mode
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/