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 SELECT statements?

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.

Adding the 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 SELECTed or 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 SELECTing.

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 COMMIT.

If it's important other transactions don't see this outdated value during your transaction, be sure to add the FOR UPDATE modifier.