0

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?

MinecraftShamrock
  • 3,504
  • 2
  • 25
  • 44

1 Answers1

0

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • What about causality in isolated transactions? If one transaction does something with the data and another session does so too, but without being in a transaction, are the read-phenomena requirements still met? – MinecraftShamrock Nov 18 '14 at 20:20
  • 1
    Everything in InnoDB is in a transaction, whether you begin/commit one explicitly or you rely on autocommit. In both cases, they will obey transaction isolation. – Bill Karwin Nov 18 '14 at 21:12