![]() ![]() We can find nothing about this deadlock in the output of SHOW ENGINE INNODB STATUS (as shared at the beginning of this post). ![]() We get the same deadlock error 1213 and LOCK TABLES in the second session completes. | TABLE | test | t2 | 139841688089072 | SHARED_NO_READ_WRITE | TRANSACTION | PENDING | | 46 | 208 || TABLE | performance_schema | metadata_locks | 139841686219040 | SHARED_READ | TRANSACTION | GRANTED | | 45 | 3003 |Īs soon as we try this in the first session: | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | Mysql> select * from performance_tadata_locks We can check what happens with metadata locks using performance_tadata_locks table (as we use MySQL or Percona Server 5.7+, more on setup, alternatives for MariaDB etc here and there). It hangs, waiting as long as lock_wait_timeout. So, let's try the following scenario with two sessions and out InnoDB tables, t1 and t2. I mean Bug #65890 - " Deadlock that is not a deadlock with transaction and lock tables". One may spend notable time trying to reproduce such a deadlock, but (as usual) quck check of MySQL bugs database may help to find an easy to reproduce case. Unlike with InnoDB deadlocks, chances that you've seen deadlocks with metadata locks involved are low. If you use MariaDB 5.5+, in case of InnoDB deadlock special innodb_deadlocks status variable is also incremented. Details of output may vary depending on version (and bugs it has :). In the case above I've used Percona Server 5.7.24-26 (why not). RECORD LOCKS space id 94 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 15003 lock_mode X locks rec but not gap waiting *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 94 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 15003 lock_mode X locks rec but not gap MySQL thread id 9, OS thread handle 139842181510912, query id 8546 localhost root statistics TRANSACTION 15003, ACTIVE 143 sec starting index readģ lock struct(s), heap size 1136, 2 row lock(s) RECORD LOCKS space id 94 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 15002 lock_mode X locks rec but not gap waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: MySQL thread id 8, OS thread handle 139842181244672, query id 8545 localhost root statistics LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) TRANSACTION 15002, ACTIVE 202 sec starting index read We can get the details in the output of SHOW ENGINE INNODB STATUS: It's that simple, one table and two rows is enough. Try to access the row with id=1 asking for incompatible lock in the second session, and you'll get the deadlock error:Īt this moment SELECT in the first transaction returns data: This statement hangs waiting for a lock (up to innodb_lock_wait_timeout seconds). ![]() Now in the first session try to access the row with id=2 asking for incompatible lock: Mysql> select * from t1 where id = 2 for update Mysql> select * from t1 where id = 1 for update ![]() In the first session execute the following: With InnoDB and tables above it's really easy to end up with a deadlock. Purge done for trx's n:o show create table t1\G RW-shared spins 0, rounds 396, OS waits 195 OS WAIT ARRAY INFO: reservation count 326 Srv_master_thread log flush and writes: 14882 Srv_master_thread loops: 59 srv_active, 0 srv_shutdown, 14824 srv_idle Per second averages calculated from the last 12 seconds The first thing that comes to mind in this case is: "OK, we have InnoDB deadlock, let's check the details", followed by the SHOW ENGINE INNODB STATUS check, like this: Probably all of us, MySQL users, DBAs and developers had seen error 1213 more than once, in one context or the other:ĮRROR 1213 (40001): Deadlock found when trying to get lock try restarting transaction ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |