maria-developers team mailing list archive

[Kazuhiko Shiozaki <kazuhiko@xxxxxxxxxx>]

Hi Kristian,

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.

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/