Does MySQL/InnoDB implement true serializable isolation?

Question

It is not entirely clear from MySQL documentation whether the InnoDB engine implements true serializable isolation¹ or snapshot isolation, which is often confusingly called "serializable" too. Which one is it?

If MySQL InnoDB doesn't, are there any completely free, production-quality RDBMS which do?

¹ where "true serializable isolation" means the absence of not only read anomalies as per the SQL standard, but also the write skew anomaly, explained in further detail here.

Sacken

I love hearing this. We need feature X, but definitely don't want to pay for it. – Trescott

Glad I've made you a little happier, @Kibbee. – Sacken

If you define what you mean be "true serializable isolation" maybe people can clear this up for you a little. – Trescott

Answer 1

are there any completely free, production-quality RDBMS which do?

Postgres has support for true serializable isolation starting with version 9.1. It certainly qualifies both as "completely free" and "production-quality".

Sacken

This is no longer true? See comments to regileros answer above - MySQL seems to have fixed the problem in 5.5. – Frank

@PiotrBlasiak It might be that the specific example is fixed, but unless MySQL advertises true isolation, I expect that it only implements the lesser variant. However I'll remove my claim about MySQL since it's now unsourced. – Sacken

Answer 2

UPDATE:

See comments, this seems to be fixed in MySQL 5.5, with these examples we still have a table lock and the index next-key lock cannot be fooled, AFAIK.

Original:

Found your question yesterday and I was wondering about the MVCC seriability model of InnoDb as well.

So I made some tests. MySQL 5.1.37. A good test for the serializability problem is the one provided in postgrESQL 9.0 MVCC documentation, on this chapter Serializable Isolation versus True Serializability we can see the limit of the MVCC model on serializability if no predicate locking is performed.

So let's test it on MySQL:

CREATE TABLE t1 (
 class integer,
 value integer
) ENGINE=InnoDB;

INSERT INTO t1 (`class`,`value`) VALUES
  (1,10),
  (1,20),
  (2,100),
  (2,200);

Now we will open two different connections to have two parallel transactions (T1 and T2):

T1:

SET TRANSACTIOn ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 1;

Result is 30.

T2:

 SET TRANSACTIOn ISOLATION LEVEL SERIALIZABLE;
 BEGIN;
 SELECT SUM(value) FROM t1 WHERE class = 2;

Result is 300.

Now comes the serializability problem. If T1 inserts a row rendering the select from T2 invalid (here T2 does the same).

T1:

INSERT INTO t1 (`class`,`value`) VALUES (2,30);

==> waiting (a lock is in place)

T2:

INSERT INTO t1 (`class`,`value`) VALUES (1,300);

==> ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

T1 now succeeds in his insert, t2 had a ROLLBACK, good serializability.

This would fail on PostgreSQL 9.0 (things are changing on 9.1, but it's another problem). In fact only one of the transactions can perform an insert on the table. Even if we try to insert on class=3 with.

INSERT INTO t1 (`class`,`value`) VALUES (3,30);

We would see a waiting lock, and deadlocks in case of problems. Looks like we have a predicate locking in MySQL... But in fact it's a next-key locking implementation in InnoDB.

Innodb performs row locks with some gaps locked as well on indexes. Here we have no indexes on the table, looks like MySQL decided to lock the table.

So let's try to test the next-key locking to see if this enforce serializability. First Rollback the running transaction (T1). Then create an index.

CREATE index t1class ON t1 (class);

Now redo the test. Success, serializability is still enforced. Good news.

But with the index in place I think the next-key locking and the row locks are made on the index. This mean we should be able to perform insert if it does not impact a parallel transaction... and here comes the big problem.

T1:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(value) FROM t1 WHERE class = 1;

Result is 30.

T2:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 BEGIN;
 SELECT SUM(value) FROM t1 WHERE class = 2;

Result is 300.

Here we gonna make an unrelated insert on T1, now that we have an index this will succeed:

T1:

INSERT INTO t1 (`class`,`value`) VALUES (3,30);

Both can perform the insert (here I made only one), that's normal. The predictive locking isn't applied, no SELECT queries has been made on class=3. Looks like the next-key locking performs better if we give it good indexes (no Table lock on inserts).

Now we try to insert on the next-key lock, On a row matching selection of T2 (class=2):

T1:

INSERT INTO t1 (`class`,`value`) VALUES (2,30);

Ouch. It succeeds!

T2:

INSERT INTO t1 (`class`,`value`) VALUES (1,300);

==> waiting. There is still a lock there. Hopefully.

T1:

COMMIT;

T2: (where the lock has gone, the insert is made)

SELECT SUM(value) FROM t1 WHERE class = 2;
COMMIT;

Still having 300 here. Seems the serializability has gone.

select * from t1;
+-------+-------+
| class | value |
+-------+-------+
|     1 |    10 | 
|     1 |    20 | 
|     2 |   100 | 
|     2 |   200 | 
|     3 |    30 | <-- test
|     2 |    30 | <-- from trans1
|     1 |   300 | <-- from trans2 ERROR!
+-------+-------+

Result: By inserting a new unrelated row before inserting a row impacting a parallel transaction query we have spoofed the next-key locking mechanism. Or at least this is what I understand from my tests. So I would say, do not trust the engine for true serializability. When you have aggregates functions in a transaction the best thing is to manually lock the table, transform your serializability problem in a real only-one-guy situation, no surprises! Other serializability problems in examples are constraint verifications (check that the amount is still positive after your operation), do you own locks on these cases as well.

Tourney

@Seun Osewa: that's right, I tried it as well with 5.5 and it seems the table is locked, maybe the wrong 'index-optimization' has been removed. I cannot get a wrong serialization example anymore. – Tourney

I get some downvotes here, that's fun, no comments, just downvotes... maybe a MySQL developper. – Tourney

If I get this right, your example shows that MySQL's transactional engine InnoDB, praised and gold-coated for its dexterity at handling transactions, miserably fails to guarantee isolation during transactions. In other words, MySQL is not a safe DBMS for transactions. – Pearse

@Pearse true isolation in serializable mode is a complex subject that could feed books. But back in 2011 it was certainly not robust in Innodb. Anyway most users does not even ask for more than repeatable reads isolation, usually without knowing the side effects. – Tourney

Answer 3

Are you sure you're using "serializable" transactions. To be sure, you must use "SET session TRANSACTION ISOLATION LEVEL SERIALIZABLE;" so that entire session becomes serializable and not just next transaction.

I'm testing with 5.5.29 on OSX

and when I try to insert (3,30) in T1, after index creation on class, transaction waits and aborts after lock wait timeout. (T2 is still in progress)

Quadrangle

Answer 4

Reading more at the link you provided, it says that using "repeatable-read" mode (the default for innodb) gets rid of read anomalies, as you mentioned as one of your requirements. Also, reading your second link, it appears that handling write anomalies is shifted to the end user. In the article they mention Oracle's Select for Update, which MySQL also supports. I'm not sure if this fulfills your requirements, but it should help you a little.

Trescott

Yeah... although the second link explains what snapshot isolation does, not whether MySQL does snapshot isolation or true serializable isolation, so I'm still not sure. – Sacken

Answer 5

I do not believe MySQL implements serializable isolation, which, as I understand it, would require the capability to rollback, which it definitely doesn't support. For more info, read here.

Condenser

Right at the top of that article, it states that it is out of date, and does not represent the lastest data concerning MySQL. While MyISAM tables do not support rollbacks, or transactions at all, InnoDB does support transactions with rollback. – Trescott

I know for a fact that MySQL + InnoDB supports transactions; the question is only as to what exactly their "serializable" transactions mean. – Sacken

MySQL indeed supports transactions. MyISAM table handler does not support row level locking, but InnoDB fixes that, too. – Sahaptin

The link you mention states "This Document was written in May 2000. Thus, it is outdated and does not represent the latest data concerning MySQL.", which makes it 11 years old at the time you wrote your answer. Almost all of the features the post author mentions have been implemented since, in the InnoDB engine. – Belindabelisarius