MySQL 8 Cookbook

Serializable

This provides the highest level of isolation by locking all the rows that are being selected. This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT...LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, SELECT is its own transaction.

For example:

# Transaction 1	# Transaction 2
BEGIN;	BEGIN;
SELECT * FROM account WHERE account_number='A';	--
--	UPDATE account SET balance=1000 WHERE account_number='A'; # This will wait until the lock held by transaction 1 on row A is released
COMMIT;	--
--	# UPDATE will be successful now

Another example:

# Transaction 1	# Transaction 2
BEGIN;	BEGIN;
SELECT * FROM account WHERE account_number='A'; ...