| 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: | skye chen | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| 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 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]
skye 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]
skye 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]
skye 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]
skye 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]
skye 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.
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());