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] Sinisa Milivojevic
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] Sinisa Milivojevic
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] Sinisa Milivojevic
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] Sinisa Milivojevic
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.