Continuing the discussion around transactions, consistency, and MySQL, we consider transaction isolation levels.
Setting the TXIL Per Session
Remember in MySQL we have
auto-commit=1. When you set the transaction isolation level with a statement, that only lasts one transaction if you don't specify the
session keyword, so the TXIL will revert to the global default immediately, before you can do a "start transaction."
See how the addition of the
session keyword in the following makes things behave as we'd expect:
> set transaction isolation level serializable ; > show variables like '%iso%'; | tx_isolation | REPEATABLE-READ | <--PROBABLY DIDN'T EXPECT THAT > set session transaction isolation level serializable ; > show variables like '%iso%'; | tx_isolation | SERIALIZABLE | <--THIS IS WHAT WE EXPECT
What does "...FOR UPDATE" Do?
When you are running transactions in Repeatable Read TXIL (as we do at Bigcommerce), when do we need to append the
FOR UPDATE modifier to
Remember MySQL works very hard to make sure no reader ever needs to block. This is the snapshot mentality around avoiding concurrency problems. The 1970's thought process used exclusive locks for the same thing. Although less performant, the subtleties around exclusive locks are easier to understand.
FOR UPDATE modifier to a
SELECT will explicitly put an exclusive lock on the rows affected. This changes the effective isolation level SERVER-WIDE for the duration of your transaction. All other transactions, when attempting to read your rows, will block until you
COMMIT. See the following:
T1: > start transaction; select * from test where id=2 for update ; T2: > select * from test where id=2 ; (hangs until T1 issues COMMIT)
What does SERIALIZABLE do?
You can get most of the same benefit by running in
SERIALIZABLE isolation level, except that T2 won't block:
T1: > set session transaction isolation level serializable; > start transaction ; > select * from test where id=2 ; T2: > select * from test where id=2 ; (does not block!) T1: > update test set balance=300 where id=2 ; T2: > select * from test where id=2 ; (does not block!) > update test set balance=400 where id=2 ; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Note what happens is that T2 is allowed to read the values
UPDATEd by T1. But as soon as it tries to
UPDATE a value that's currently being modified by T1, it gets a deadlock and dies. Note that the winning transaction is the one that started first. So if T2 had done a
start transaction before T1 did, then T1 would have received the deadlock error, not T2. It does not matter who
UPDATEd first. Only who
START TRANSACTIONed first.
So in Repeatable Read or Serializable TXILs... I can read an "outdated" value?
Yes. Depending on your definition of "outdated."
In MySQL that no matter what the TXIL, you can read a value that another transaction has updated (but not yet
COMMITted), and when you do, you'll see the value as it was before the other transaction started.
Because each transaction is in its own snapshot version of reality, and its reality will not leak outside its context until it specifies a
FOR UPDATE modifier to the rows it's
So if you are expecting
UPDATEd values from one transaction to be reflected immediately (before transaction end) to other transactions, you will be surprised. All
UPDATEd values will be readable by other transactions in their original un-
UPDATEd form until your
If it's important other transactions don't see this outdated value during your transaction, be sure to add the
FOR UPDATE modifier.