Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html or any other version) says:
"INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row."
and then later:
"If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock."
Note that in the above (or anywhere on that page) there is no clarification on what kind of key this "duplicate-key error" applies, PRIMARY or some secondary UNIQUE key (there can be many).
Now, for INSERT ... ON DUPLICATE KEY UPDATE manual just says:
"INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive next-key lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs."
One may expect to see this next-key lock for ANY kind on INSERT ... ON DUPLICATE KEY UPDATE statement, while in reality it does NOT happen:
mysql> create table tc(id char(2) primary key, c1 int, c2 char(2));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into tc values ('aa', 0, 'aa'), ('ab', 0, 'ab'), ('az', 0, 'az');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table innodb_lock_monitor(id int) engine=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tc values('az', 0, 'aa') on duplicate key update c1=c1+1;
Query OK, 2 rows affected (0.03 sec)
mysql> select * from tc;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| aa | 0 | aa |
| ab | 0 | ab |
| az | 1 | az |
+----+------+------+
3 rows in set (0.00 sec)
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2015-12-16 12:32:31 dec INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 14824 srv_idle
srv_master_thread log flush and writes: 14828
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 11
OS WAIT ARRAY INFO: signal count 11
Mutex spin waits 109, rounds 252, OS waits 3
RW-shared spins 8, rounds 212, OS waits 7
RW-excl spins 0, rounds 30, OS waits 1
Spin rounds per wait: 2.31 mutex, 26.50 RW-shared, 30.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 147718
Purge done for trx's n:o < 144648 undo n:o < 0 state: running but idle
History list length 44
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 147717, ACTIVE 10 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0xdec, query id 52 localhost 127.0.0.1 root
init
show engine innodb status
TABLE LOCK table `test`.`tc` trx id 147717 lock mode IX
RECORD LOCKS space id 42 page no 3 n bits 72 index `PRIMARY` of table `test`.`tc
` trx id 147717 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 2; hex 617a; asc az;;
1: len 6; hex 000000024105; asc A ;;
2: len 7; hex 050000013a2d4d; asc :-M;;
3: len 4; hex 80000001; asc ;;
4: len 2; hex 617a; asc az;;
...
As you can see in the example above, the only row lock set is "lock_mode X locks rec but not gap" on the PRIMARY (and only) key. This is totally against expectation one may get after reading the manual.
Next-key lock in case of duplication is set only on seconday UNIQUE index (if it had caused the duplicate). Look:
mysql> alter table tc add unique key(c2);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tc values('ay', 0, 'aa') on duplicate key update c1=c1+1;
Query OK, 2 rows affected, 1 warning (0.02 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1592
Message: Unsafe statement written to the binary log using statement format since
BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with m
ore than one UNIQUE KEY is unsafe
1 row in set (0.00 sec)
mysql> select * from tc;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| aa | 1 | aa |
| ab | 0 | ab |
| az | 0 | az |
+----+------+------+
3 rows in set (0.00 sec)
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2015-12-16 12:44:50 dec INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 9 srv_active, 0 srv_shutdown, 15558 srv_idle
srv_master_thread log flush and writes: 15567
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 17
OS WAIT ARRAY INFO: signal count 17
Mutex spin waits 115, rounds 299, OS waits 4
RW-shared spins 13, rounds 362, OS waits 12
RW-excl spins 0, rounds 30, OS waits 1
Spin rounds per wait: 2.60 mutex, 27.85 RW-shared, 30.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 147730
Purge done for trx's n:o < 147729 undo n:o < 0 state: running but idle
History list length 48
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 147729, ACTIVE 93 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0xdec, query id 66 localhost 127.0.0.1 root
init
show engine innodb status
Trx read view will not see trx with id >= 147730, sees < 147730
TABLE LOCK table `test`.`tc` trx id 147729 lock mode IX
RECORD LOCKS space id 42 page no 4 n bits 72 index `c2` of table `test`.`tc` trx
id 147729 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 2; hex 6161; asc aa;;
1: len 2; hex 6161; asc aa;;
RECORD LOCKS space id 42 page no 3 n bits 72 index `PRIMARY` of table `test`.`tc
` trx id 147729 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 2; hex 6161; asc aa;;
1: len 6; hex 000000024111; asc A ;;
2: len 7; hex 0c0000016c0110; asc l ;;
3: len 4; hex 80000001; asc ;;
4: len 2; hex 6161; asc aa;;
...
Now we have next-key lock on key c2 that was a duplicate.
How to repeat:
create table tc(id char(2) primary key, c1 int, c2 char(2));
insert into tc values ('aa', 0, 'aa'), ('ab', 0, 'ab'), ('az', 0, 'az');
create table innodb_lock_monitor(id int) engine=InnoDB;
start transaction;
insert into tc values('az', 0, 'aa') on duplicate key update c1=c1+1;
select * from tc;
Now, try to find next-key lock in the output of INNODB STATUS:
show engine innodb status\G
Then add the secondary UNIQUE key and observe the next-key lock on it:
rollback;
alter table tc add unique key(c2);
start transaction;
insert into tc values('ay', 0, 'aa') on duplicate key update c1=c1+1;
select * from tc;
show engine innodb status\G
Suggested fix:
Please, document the locks set by INSERT ... ON DUPLICATE KEY UPDATE carefully and properly, covering both the duplicate on PRIMARY key case and duplicate on secondary UNIQUE key case.
