Hi Kristian, On 18/02/2013 17:38, Kazuhiko Shiozaki wrote:
On 18/02/2013 16:59, Kristian Nielsen wrote:
It's REPEATABLE-READ.
And innodb_locks_unsafe_for_binlog is ON, could it be the reason ?
Yes. (snip) With --innodb-locks-unsafe-for-binlog=0, the DELETE from connection A waits for a next-key lock set by the DELETE in B, and there is no error. With --innodb-locks-unsafe-for-binlog=1, no such next-key lock is set. Therefore, you get a duplicate key error, depending on exactly how the statements in the two transactions run.
You can perhaps solve it by setting the transaction isolation level to SERIALIZABLE for just these queries. On the other hand, you probably need to be prepared to handle deadlocks and retry the transactions anyway, so you could just retry in this case also.
Thanks for your help! I forgot that I enabled innodb_locks_unsafe_for_binlog when I posted this question...
I'm trying using SERIALIZABLE isolation level and will check the performance impact with this change.
I tried setting SERIALIZABLE isolation globally and confirmed that (much) more deadlocks happened. But unfortunately "Duplicate entry" error still happens. @@GLOBAL.tx_isolation: SERIALIZABLE @@tx_isolation: SERIALIZABLE @@innodb_locks_unsafe_for_binlog: 1 Is it normal ? Regards, -- Kazuhiko Shiozaki, Nexedi SA Senior Consultant Nexedi: Consulting and Development of Free / Open Source Software http://www.nexedi.com/ ERP5: Full Featured High End Open Source ERP http://www.erp5.com/