I am currently learning about MySQL's transaction and lock features.

Are transactions with the isolation-level SERIALIZABLE and statements between a LOCK and UNLOCK statement on the same table executed mutually exclusive?

EDIT 1: For the transaction thing with isolation level SERIALIZABLE, is it even possible to determine whether the transaction is actually mutually exclusive or just the requirements like no phantom reads are fulfilled? Or do these two properties imply the same behavior?

EDIT 2: Oh, and are the mechanisms that provide the given isolation-level of a transaction only active if two or more transactions actually work on the same data so that the read phenomena could actually happen?

解决方案

In InnoDB, all SERIALIZABLE does is turn a SELECT into an implicit SELECT...LOCK IN SHARE MODE. So this only affects INSERT/UPDATE/DELETE against the same row(s).

You can observe the locks in SHOW ENGINE INNODB STATUS:

---TRANSACTION 14594, ACTIVE 5 sec

2 lock struct(s), heap size 360, 8 row lock(s)

MySQL thread id 24, OS thread handle 0x7f65c8624700, query id 324 192.168.56.1 root cleaning up

TABLE LOCK table `imdb`.`kind_type` trx id 14594 lock mode IS

RECORD LOCKS space id 24 page no 4 n bits 80 index `kind` of table `imdb`.`kind_type` trx id 14594 lock mode S

All I did was SELECT * FROM imdb.kind_type after setting tx_isolation=SERIALIZABLE and autcommit=0.

You see that it holds an IS table lock, which will block a LOCK TABLES kind_type WRITE.

But if you're executing the transaction autocommit mode, it doesn't even do that; it just acts like REPEATABLE-READ.