MySQL - why one insert locks second insert?

Question

I want to insert row with unique value in non-primary key field. I cannot use unique index (because of NULL values in multiple field index, but it is not important here).

I use INSERT/SELECT WHERE NOT EXISTS as an atomic (I hope) operation - see below.

I will describe problem on simplified example:

I have an empty table:

CREATE TABLE Test ( Id int(11) NOT NULL AUTO_INCREMENT, A int(11) DEFAULT NULL, PRIMARY KEY (Id) ) ENGINE=InnoDB;

... and 2 sessions with autocommit mode switched off:

In first session I execute:

INSERT INTO Test (A) SELECT A FROM (SELECT 1 AS A) Inn WHERE NOT EXISTS (SELECT A FROM Test WHERE A = 1 FOR UPDATE);

In second session I execute:

INSERT INTO Test (A) SELECT A FROM (SELECT 2 AS A) Inn WHERE NOT EXISTS (SELECT A FROM Test WHERE A = 2 FOR UPDATE);

As you can see, I'm inserting independent values. But this second session is locked by the first one. Why? Maybe it is because of gap/next-key lock.

How else i can insert records with "manual" avoiding of duplicate keys and without mutual locking?

Default transaction isolation level is REPEATABLE READ and I don't want to change default settings of database, because other applications use it.

This seems quite a complex way to do an INSERT IGNORE. What is your reason for this approach? — Arnout, Apr 03 '14 at 14:52
Because I can have NULLs in some unique index columns. Do you have any idea how else I can insert unique values? — Marek, Apr 23 '14 at 11:01

score 2 · Answer 1 · edited May 23 '17 at 12:05

Regarding the gap locking for the SELECT FOR UDPATE, there are locks with a unique index, gap locks with a non-unique index, and table locks without an index :

How do I lock on an InnoDB row that doesn't exist yet?

In addition, InnoDB places locks when using auto_increment to preserve the sequence of keys, which is important during replication.

This is from the documentation (emphasis mine):

When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column. Nevertheless, two transactions cannot have the AUTO-INC lock on the same table simultaneously, which can have a performance impact if the AUTO-INC lock is held for a long time. That might be the case for a statement such as INSERT INTO t1 ... SELECT ... FROM t2 that inserts all rows from one table into another.

There were some performance improvements in MySQL v5.1.22, which may use a faster method, but there is still some wait between statements.

However, if you aren't using replication, you can improve performance by allowing interleaved auto_increment values which improves performance with concurrency:

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

MySQL - why one insert locks second insert?

1 Answers1