maria-developers team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

Kazuhiko Shiozaki <kazuhiko@xxxxxxxxxx> writes:

>> It's REPEATABLE-READ.
>
> And  innodb_locks_unsafe_for_binlog is ON, could it be the reason ?

Yes.

    A> SELECT @@global.innodb_locks_unsafe_for_binlog;
        @@global.innodb_locks_unsafe_for_binlog
        1
    A> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    A> BEGIN;

    B> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    B> BEGIN;
    B> DELETE FROM t1 WHERE a=1;

    A> DELETE FROM t1 WHERE a=1;

    B> INSERT INTO t1 VALUES (1);
    B> COMMIT;

    A> INSERT INTO t1 VALUES (1);
        ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

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.

Hope this helps,

 - Kristian.