"Heikki Tuuri" <Heikki.Tuuri@innodb.com> writes:
> if you set the transaction isolation level SERIALIZABLE in MySQL/InnoDB,
> then InnoDB uses next-key locking in every SELECT, and transactions really
> are serializable in the mathematical sense.
My understanding is that next-key locking only helps when all your
predicates are point or range searches against an available b-tree
index. While that might cover many practical cases, it can hardly
be called a complete solution to the problem of serializability.
Another serious problem with predicate locking in general is that it's
prone to creating deadlocks. I gave an example here:
http://archives.postgresql.org/pgsql-general/2003-01/msg01581.php
Since next-key locking is just an approximate form of predicate locking
(approximate in the sense that it may map many different predicates into
the same lock), I'd expect it to generate even more deadlocks than true
predicate locking would.
In short, next-key is not a perfect solution either.
> Another note: Joshua Drake claimed that InnoDB deadlocks if you try to do
> inserts concurrently to a table with a primary key. I guess he refers to
> some old version, where InnoDB still used
> SELECT MAX(auto_inc_column) FROM table FOR UPDATE;
> to determine the next auto-inc key value. Because the execution has to be
> serializable :), it is not that easy to make this algorithm to avoid
> deadlocks if inserts are made to the end of the index.
Yup, pretty much the same point I made above. Inserting at the end of
the index requires a next-key lock on a (notional) infinite key.
So all those inserts need the same exclusive lock.
> But the InnoDB algorithm was changed a long time ago. Nowadays innoDB uses
> an internal counter. I ran Joshua's test with MySQL-4.0.15 with the default
> my.cnf settings, and no deadlocks were generated.
Should I read that as saying that you fail to take out the required
next-key lock when inserting an autoincremented value? Tsk tsk.
The inserts may not conflict with each other, but I think you'll find
that serializability is violated for concurrent selects looking at
the table.
regards, tom lane