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.