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