| Bug #116459 | Deadlocks in transaction using Insert Ignore Statements post Upgrade to Mysql 8 | ||
|---|---|---|---|
| Submitted: | 23 Oct 12:01 | Modified: | 23 Oct 13:04 |
| Reporter: | Kishan Kumar | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.28 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysql8 | ||
[23 Oct 12:13]
Kishan Kumar
The schema name shows "masters" in the lock information instead of "datalocktest", please ignore this typo of database schema name. "datalocktest" was just a representible name for creating the bug report.
[23 Oct 12:39]
MySQL Verification Team
Hi Mr. Kumar, Thank you for your bug report. However, it is not a bug. First of all, you are not getting any deadlocks. You are only getting timeout on waiting for the lock. InnoDB Storage Engine is a very safe engine and it follows all the standards necessary for the safe transactional engines. Regardless of whether you set IGNORE or not, all locks have to be respected. The fact that you get lock wait timeout, means that InnoDB is functioning properly and in accordance with MVCC standards. Do not forget that you can always tune lock_wait_timeout, as described in our Reference Manual. In short, this is not a bug, this is expected , standardised behaviour that only proves that InnoDB is in compliance with all safety standards. It is possible that earlier versions have not been fully compliant, but this is fixed now ..... Not a bug.
[23 Oct 12:50]
Kishan Kumar
Some More insights. Locks Info post Session 1 transaction insertion with duplicate UNIQUE Key SELECT ENGINE_TRANSACTION_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks; +-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+ | ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+ | 2178 | products | NULL | TABLE | IX | GRANTED | NULL | | 2178 | products | sku | RECORD | S | GRANTED | 'sku1', 1, 100 | | 2178 | products | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | +-----------------------+-------------+------------+-----------+-----------+-------------+------------------------+ 3 rows in set (0.00 sec) Locks Info post Session 2 transaction insertion with different uniqe dataset SELECT ENGINE_TRANSACTION_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks; +-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+ | ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+ | 2179 | products | NULL | TABLE | IX | GRANTED | NULL | | 2179 | products | PRIMARY | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record | | 2178 | products | NULL | TABLE | IX | GRANTED | NULL | | 2178 | products | sku | RECORD | S | GRANTED | 'sku1', 1, 100 | | 2178 | products | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record | +-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+ 5 rows in set (0.00 sec) We see that the second transaction is on Waiting state to acquire the lock ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+ | 2179 | products | NULL | TABLE | IX | GRANTED | NULL | | 2179 | products | PRIMARY | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record |
[23 Oct 13:02]
MySQL Verification Team
Hi Mr. Kumar, These are not duplicate unique indices. These are just several locks on the single row , including gap locks. Not a bug.
[23 Oct 13:03]
Kishan Kumar
Thanks Mysql Team for the quick response. We would like to know some more insights into this change since its a drastic change which we are seeing post migration in our Production databases. Can we have some documentation around what change was done and what was the issue which was resolved using this fix. We would also like to know some information on lock changes. Is there any ,mysql documentation link which can be provided for further analysis
[23 Oct 13:04]
Kishan Kumar
Also please let us know if there is any flag to turn off this behaviour.
[23 Oct 13:26]
MySQL Verification Team
Hi Mr. Kumar, First of all, InnoDB is a transactional storage engine and there is no flag by which you can turn it into something else, like non-transactional SE. Second, changes that were made to InnoDB were due to the bugs. Some of the crashing bugs and data corruption bugs have occurred due to the inadequate locking. Fixing those bugs required improving locking. Hence, what you observe is a positive change, which avoids data corruption or crashing in many cases. All of those bug fixes represent a major improvement for InnoDB storage engine. We do not group those bug fixes in a separate category. You can look through our changelogs and count a number of crashing and data corruption bugs in InnoDB. Our ChangeLogs are here: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/ Not a bug.
Description: We recently upgraded our Mysql DB from 5.7.11 to Mysql 8.0.28. Post upgrade we are seeing too many deadlock issues being reported from the applications. We run huge imports into the Database to the tune of 100000 to 1M datasets in the batches of 10000 data set. Prior to upgrade we were not seeing any issues when these huge imports are running and parallely other applications are accessing the Database of read or write operations. Post upgrade we see when the Imports are running , other applications trying to insert data into the table needs to wait until the import transaction is completed. Eventually the other application transaction gets timedout waiting. What we observed is , there is a supremum pseudo-record lock being taken on insert ignore statements that too when we have some duplicate entries in the dataset for the unique key column in the table . Details of how to reproduce this steps are below How to repeat: Create a Database say "datalocktest" 1. create database datalocktest; 2. use datalocktest; 3. CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `brand_id` int(11) NOT NULL, `sku` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`,`brand_id`), UNIQUE KEY `sku` (`sku`) ); 4. The isolation level is REPEATABLE READ. 5. Open 2 sessions of mysql in 2 different terminals. 6.Terminal 1 ----------------------------------------------------- Session 1: set autocommit=0; start transaction; INSERT IGNORE INTO products (brand_id, sku) VALUES (100, 'sku1'); commit; Step2: ----------------------------------------------------- Session 1: set autocommit=0; start transaction; INSERT IGNORE INTO products (brand_id, sku) VALUES (100, 'sku1'); Do not run the commit statement yet 7. Termial 2 ----------------------------------------------------- Session 2: set autocommit=0; start transaction; INSERT IGNORE INTO products (brand_id, sku) VALUES (101, 'sku2'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Observe that we get a timeout after sometime. The first session when it is trying to insert a duplicate record (by unique key on `sku`) it acquires "supremum pseudo-record" lock with lock mode X. Because of this the other insert which has different data is blocked and eventually times out. See the lock data below after the session 1 intitates the insert query: select * from performance_schema.data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140628559916440:1066:140628570745488 ENGINE_TRANSACTION_ID: 2128 THREAD_ID: 47 EVENT_ID: 91 OBJECT_SCHEMA: masters OBJECT_NAME: products PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140628570745488 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140628559916440:3:5:2:140628570742496 ENGINE_TRANSACTION_ID: 2128 THREAD_ID: 47 EVENT_ID: 91 OBJECT_SCHEMA: masters OBJECT_NAME: products PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: sku OBJECT_INSTANCE_BEGIN: 140628570742496 LOCK_TYPE: RECORD LOCK_MODE: S LOCK_STATUS: GRANTED LOCK_DATA: 'sku1', 3, 100 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140628559916440:3:4:1:140628570743184 ENGINE_TRANSACTION_ID: 2128 THREAD_ID: 47 EVENT_ID: 91 OBJECT_SCHEMA: masters OBJECT_NAME: products PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140628570743184 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: supremum pseudo-record 3 rows in set (0.00 sec) Also to make a note, if we do not have duplicates in the UNIQUE Key column in this case `sku`, we do not see any issue. Observe below that if there are no duplicated inserted on Session1 , we see only 1 lock being acquired in this case. Now if we insert a new dataset in Session 2, it will be successfull. Issue is seen only with duplicate entry in Unique Key. start transaction; INSERT IGNORE INTO products (brand_id, sku) VALUES (100, 'sku2'); Query OK, 0 rows affected (0.01 sec) select * from performance_schema.data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140628559916440:1066:140628570745488 ENGINE_TRANSACTION_ID: 2130 THREAD_ID: 47 EVENT_ID: 95 OBJECT_SCHEMA: masters OBJECT_NAME: products PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140628570745488 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL 1 row in set (0.01 sec) Suggested fix: Why does insert ignore taking this lock and blocking other insertion? Is this an expected behaviour? Is there any locking behaviour documentation related to "INSERT IGNORE INTO" statement that can be referred? We were not expecting this behaviour from mysql and what is the way to avoid this deadlock issue? We would expect the same behaviour in Mysql 8 as we observed in our prior Upgrade version which is 5.7.11