Bug #81563 Insert operation will add S-lock on one record after deleted one.
Submitted: 24 May 2016 8:18 Modified: 26 May 2016 2:07
Reporter: cyx chen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.21,5.6.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: deadlock, delete, insert

[24 May 2016 8:18] cyx chen
Description:
After delete one record,and then insert the same one(uk is same, primary id is diff),there will try to add S-lock on next physical record after deleted one. 

In the repeat steps, transaction 1 first delete records where shop_id='H000331857',this will add X lock on deleted records. then transaction 2 delete records where shop_id='H000331858', and add X lock on records two.

Then,transaction 1 try to insert new record,which has same uk with deleted one.In this step,transaction 1 try to add S-lock on transaction 2 deleted records.It's amazing!

How to repeat:
[Prepare Data]
CREATE TABLE `apsc_shop_recommend` (
  `recommend_id` varchar(128) NOT NULL COMMENT '推荐菜id',
  `shop_id` varchar(128) NOT NULL COMMENT '店铺id',
  `description` varchar(2048) DEFAULT NULL COMMENT '描述',
  `type` varchar(32) NOT NULL COMMENT '类型',
  `name` varchar(128) NOT NULL COMMENT '名称',
  `photos` varchar(4096) NOT NULL COMMENT '图片',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `support_type` varchar(32) NOT NULL COMMENT '推荐来源(商家或消费者)',
  `price` varchar(1024) DEFAULT NULL COMMENT '价格',
  PRIMARY KEY (`recommend_id`),
  UNIQUE KEY `apsc_shop_recommend_u` (`shop_id`,`name`,`support_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='推荐菜表';

INSERT INTO `apsc_shop_recommend` VALUES ('20160429110000000009088000100495865', 'H000331859', null, 'DISH', 'recommend_2', '[{\"length\":238,\"url\":\"http://imgfp.hotp.jp/IMGH/17/69/P022891769/P022891769_238.jpg\",\"width\":238}]', '2016-04-29 16:42:06', '2016-04-29 16:42:06', 'shop', null), ('20160429110000000009088005200495866', 'H000331859', null, 'DISH', 'recommend_1', '[{\"length\":238,\"url\":\"http://imgfp.hotp.jp/IMGH/17/73/P022891773/P022891773_238.jpg\",\"width\":238}]', '2016-04-29 16:42:06', '2016-04-29 16:42:06', 'shop', null), ('20160429110000000009088006900495870', 'H000331856', null, 'DISH', 'recommend_1', '[{\"length\":238,\"url\":\"http://imgfp.hotp.jp/IMGH/17/41/P022891741/P022891741_238.jpg\",\"width\":238}]', '2016-04-29 16:42:11', '2016-04-29 16:42:11', 'shop', null), ('20160429110000000009088009300495869', 'H000331856', null, 'DISH', 'recommend_2', '[{\"length\":238,\"url\":\"http://imgfp.hotp.jp/IMGH/17/36/P022891736/P022891736_238.jpg\",\"width\":238}]', '2016-04-29 16:42:11', '2016-04-29 16:42:11', 'shop', null), ('20160522110000000009088003700084880', 'H000331857', null, 'DISH', 'recommend_2', '[{\"length\":238,\"url\":\"http://imgfp.hotp.jp/IMGH/17/73/P022891773/P022891773_238.jpg\",\"width\":238}]', '2016-05-22 15:26:29', '2016-05-22 15:26:29', 'shop', null), ('20160522110000000009088006400084881', 'H000331857', null, 'DISH', 'recommend_1', '[{\"length\":238,\"url\":\"http://imgfp.hotp.jp/IMGH/17/73/P022891773/P022891773_238.jpg\",\"width\":238}]', '2016-05-22 15:26:29', '2016-05-22 15:26:29', 'shop', null), ('20160522110000000009088007800084882', 'H000331858', null, 'DISH', 'recommend_2', '[{\"length\":238,\"url\":\"http://imgfp.hotp.jp/IMGH/17/69/P022891769/P022891769_238.jpg\",\"width\":238}]', '2016-05-22 15:26:29', '2016-05-22 15:26:29', 'shop', null), ('20160522110000000009088009700084883', 'H000331858', null, 'DISH', 'recommend_1', '[{\"length\":238,\"url\":\"http://imgfp.hotp.jp/IMGH/17/69/P022891769/P022891769_238.jpg\",\"width\":238}]', '2016-05-22 15:26:29', '2016-05-22 15:26:29', 'shop', null);

[Repeat steps]
[Transaction 1] delete from apsc_shop_recommend where shop_id='H000331857';
[Transaction 2] delete from apsc_shop_recommend where shop_id='H000331858';
[Transaction 1] INSERT INTO apsc_shop_recommend (recommend_id, shop_id, description, price, type , support_type, name, photos, gmt_create, gmt_modified) VALUES ('20160522110000000009088003400084902', 'H000331857', null, null, 'DISH' , 'shop', 'recommend_2', '[{"length":238,"url":"http://imgfp.hotp.jp/IMGH/17/73/P022891773/P022891773_238.jpg","width":238}]', current_timestamp(), current_timestamp());
[Transaction 2] INSERT INTO apsc_shop_recommend (recommend_id, shop_id, description, price, type , support_type, name, photos, gmt_create, gmt_modified) VALUES ('20160522110000000009088005600084903', 'H000331858', null, null, 'DISH' , 'shop', 'recommend_1', '[{"length":238,"url":"http://imgfp.hotp.jp/IMGH/17/73/P022891773/P022891773_238.jpg","width":238}]', current_timestamp(), current_timestamp());
[24 May 2016 10:23] Peter Laursen
I wonder if this is related to the innodb_autoinc_lock_mode (http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_m...) variable setting? 

-- Peter
-- not a MySQL/Oracle person
[24 May 2016 15:39] MySQL Verification Team
This is not a bug, but a normal locking applied during InnoDB operations. Do read our manual on next-key or gap locks.
[24 May 2016 16:00] cyx chen
@Peter Laursen
My setting of innodb_autoinc_lock_mode = 1.  And primary key of my table is not auto increment.

@Sinisa Milivojevic
The isolation of innodb is RC(read-committed).

Before inserting, it will try to add S next-key lock on record(include S record lock on duplicate records and S gap lock). Under my scenario, S next-key lock(transaction 1) try to add on record of transaction 2,which has nothing to do with record of transaction 1.
[24 May 2016 16:22] MySQL Verification Team
By definition, InnoDB does not function in such a way that transaction A manages locks for the transaction B.

Please, provide proofs of the otherwise.

Also, otherwise your description is exactly how is InnoDB SE designed to work.
[25 May 2016 2:23] cyx chen
@Sinisa Milivojevic

When i execute step 3(transaction 1 try to insert),transaction 1 will be blocked by transaction 2.

mysql> SELECT * FROM information_schema.innodb_lock_waits \G
*************************** 1. row ***************************
requesting_trx_id: 497058
requested_lock_id: 497058:372:6:9
  blocking_trx_id: 497063
 blocking_lock_id: 497063:372:6:9
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.innodb_locks \G
*************************** 1. row ***************************
    lock_id: 497058:372:6:9
lock_trx_id: 497058
  lock_mode: S
  lock_type: RECORD
 lock_table: `apshopcenter`.`apsc_shop_recommend`
 lock_index: apsc_shop_recommend_u
 lock_space: 372
  lock_page: 6
   lock_rec: 9
  lock_data: 'H000331858', 'recommend_1', 'shop'
*************************** 2. row ***************************
    lock_id: 497063:372:6:9
lock_trx_id: 497063
  lock_mode: X
  lock_type: RECORD
 lock_table: `apshopcenter`.`apsc_shop_recommend`
 lock_index: apsc_shop_recommend_u
 lock_space: 372
  lock_page: 6
   lock_rec: 9
  lock_data: 'H000331858', 'recommend_1', 'shop'
2 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.innodb_trx \G
*************************** 1. row ***************************
                    trx_id: 497063
                 trx_state: RUNNING
               trx_started: 2016-05-25 10:02:29
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 6
       trx_mysql_thread_id: 29
                 trx_query: SELECT * FROM information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 4
     trx_lock_memory_bytes: 1184
           trx_rows_locked: 5
         trx_rows_modified: 2
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 497058
                 trx_state: LOCK WAIT
               trx_started: 2016-05-25 10:02:19
     trx_requested_lock_id: 497058:372:6:9
          trx_wait_started: 2016-05-25 10:02:49
                trx_weight: 8
       trx_mysql_thread_id: 28
                 trx_query: INSERT  INTO apsc_shop_recommend (recommend_id, shop_id, description, price, type  , support_type, name, photos, gmt_create, gmt_modified) VALUES ('20160522110000000009088003400084902', 'H000331857', null, null, 'DISH'  , 'shop', 'recommend_2', '[{"length":238,"url":"http://imgfp.hotp.jp/IMGH/17/73/P022891773/P022891773_238.jpg","width":238}]', current_timestamp(), current_timestamp())
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 5
     trx_lock_memory_bytes: 1184
           trx_rows_locked: 6
         trx_rows_modified: 3
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

At the same time, I will upload screenshot files
[25 May 2016 2:33] cyx chen
Reply in [[25 May 2:23] cyx chen]  --transaction 1

Attachment: transaction 1.png (image/png, text), 176.28 KiB.

[25 May 2016 2:34] cyx chen
Reply in [[25 May 2:23] cyx chen] -- transaction 2

Attachment: transaction 2.png (image/png, text), 309.14 KiB.

[25 May 2016 13:42] MySQL Verification Team
Yes, transaction 1 is blocked by transaction 2. That is exactly how InnoDB is designed. You can read about locking in our user manual. It contains all the details.
[26 May 2016 2:07] cyx chen
Explain in detail why S next-key in transaction 1 locks records in transaction 2,please.
I just not found answers about how to add S next-key on uk in RC mode.
[26 May 2016 13:13] MySQL Verification Team
Users can not add the locks by commands, unless some of the locking SELECTs is issued.

All the locking described is done for DMLs automatically by InnoDB SE.