Hi Kristian, On 21/02/2013 13:31, Kristian Nielsen wrote:
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
I don't know.
I am not sure what the semantics is for setting innodb_locks_unsafe_for_binlog at the same time as SERIALIZABLE. They seem kind of contradictory to me.
Do you still get the deadlocks if you use SERIALIZABLE without innodb_locks_unsafe_for_binlog?
Sorry for late reply. Yes still deadlocks happen often with using SERIALIZABLE without innodb_locks_unsafe_for_binlog. Considering our use cases, it seems that using REPEATABLE-READ with innodb_locks_unsafe_for_binlog is faster than other options we tried, even though our application should handle "Duplicate entry" well to retry transactions, because this way has much less deadlocks.
By the way, you do not need to set SERIALIZABLE globally. Why not set it only for that particular kind of transaction?
Yes I know. I set it globally only because it was the easiest way without modifying the application at all... 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/