O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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'; ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required