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/
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
