maria-developers team mailing list archive

[Kazuhiko Shiozaki <kazuhiko@xxxxxxxxxx>]

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/