"Heikki Tuuri" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org