Your cart is currently empty!
Laravel Database Pesimistic Locking
| get() | sharedLock() | lockForUpdate() | update() | |
| SELECT… | OK | OK | OK | OK |
| SELECT… LOCK IN SHARE MODE | OK | OK | WAIT | WAIT |
| SELECT… FOR UPDATE | OK | WAIT | WAIT | WAIT |
| UPDATE…, DELETE… | OK | WAIT | WAIT | WAIT |
| id | name | balance |
| 1 | Anto | 25,000 |
| 2 | Budi | 80,000 |
| … | … | … |
CASE #1
MariaDB (connection1) > START TRANSACTION;
MariaDB (connection1) > SELECT * FROM wallets WHERE id=1;| id | name | balance |
| 1 | Anto | 25,000 |
MariaDB (connection2) > UPDATE wallets SET balance=30000 WHERE id=1;
MariaDB (connection2) > SELECT * FROM wallets WHERE id=1;| id | name | balance |
| 1 | Anto | 30,000 |
Connection #2 is updating the balance of user 1. The change reflects on connection #2 but does not reflect for connection #1 (which hangs in the middle of transaction).
MariaDB (connection1) > SELECT * FROM wallets WHERE id=1;
MariaDB (connection1) > COMMIT;| id | name | balance |
| 1 | Anto | 25,000 |
Connection #1 does not know that the balance of user 1 already changed.
MariaDB (connection1) > SELECT * FROM wallets WHERE id=1;| id | name | balance |
| 1 | Anto | 30,000 |
Connection #1 figures out that the balance already changed after ending the transaction.
In the case connection #1 updating the balance of user 1 before connection #2, connection #2 will wait until connection #1 do the commit and then continue to run the update from connection #2 (which replaces the recently updated balance from connection #1). The end result is the balance set from update by connection #2.
CASE #2
MariaDB (connection1) > START TRANSACTION;
MariaDB (connection1) > SELECT * FROM wallets WHERE id=1 LOCK IN SHARE MODE;| id | name | balance |
| 1 | Anto | 25,000 |
MariaDB (connection2) > SELECT * FROM wallets WHERE id=1;| id | name | balance |
| 1 | Anto | 25,000 |
MariaDB (connection2) > SELECT * FROM wallets WHERE id=1 LOCK IN SHARE MODE;| id | name | balance |
| 1 | Anto | 25,000 |
MariaDB (connection2) > SELECT * FROM wallets WHERE id=1 FOR UPDATE;The above statement will not execute until connection1 is doing commit or rollback.
MariaDB (connection2) > UPDATE wallets SET balance=30000 WHERE id=1;The above statement will not execute until connection1 is doing commit or rollback.
CASE #3
MariaDB (connection1) > START TRANSACTION;
MariaDB (connection1) > SELECT * FROM wallets WHERE id=1 FOR UPDATE;| id | name | balance |
| 1 | Anto | 25,000 |
MariaDB (connection2) > SELECT * FROM wallets WHERE id=1;| id | name | balance |
| 1 | Anto | 25,000 |
MariaDB (connection2) > SELECT * FROM wallets WHERE id=1 LOCK IN SHARE MODE;The above statement will not execute until connection1 is doing commit or rollback.
MariaDB (connection2) > SELECT * FROM wallets WHERE id=1 FOR UPDATE;The above statement will not execute until connection1 is doing commit or rollback.
MariaDB (connection2) > UPDATE wallets SET balance=30000 WHERE id=1;The above statement will not execute until connection1 is doing commit or rollback.
Leave a Reply