MySQL DeadLock

SoulMapper

于 2021-12-10 12:24:42 发布

阅读量838

点赞数

分类专栏： database 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/luckyrocks/article/details/121838310

版权

本文详细介绍了MySQL InnoDB的锁类型，如记录锁、间隙锁、插入意向锁和Next-key锁，以及它们在并发操作中的冲突和死锁原因。在RR隔离级别下，通过`for update`和`insert`语句的示例分析了死锁的产生，并提供了死锁的解决方法。建议深入理解插入意向锁的作用和锁兼容性规则，以便更好地避免和解决MySQL中的死锁问题。

摘要由CSDN通过智能技术生成

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-12-09 06:51:13 7f89cac7a700
*** (1) TRANSACTION:
TRANSACTION 46451826235, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 1867664, OS thread handle 0x7f89d0599700, query id 550365490 10.211.0.0 root update
insert into test_device (create_time, creator, modifier, modify_time, order_id, name) values ('2021-12-09 14:51:12.687', '@test', null, null, '02j2it2341svepb99342', 'iphone13 Pro')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 22123 page no 4 n bits 88 index `UNIQ_ORDER_ID` of table `testdb`.`test_device` trx id 46451826235 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 9; hex 393134303039303039; asc 914009009;;
 1: len 4; hex 80000035; asc    5;;

*** (2) TRANSACTION:
TRANSACTION 46451826237, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 1867668, OS thread handle 0x7f89cac7a700, query id 550365495 10.211.0.0 root update
insert into test_device (create_time, creator, modifier, modify_time, order_id, name) values ('2021-12-09 14:51:12.684', '@test', null, null, '02j2it2341svepb99342', 'iphone13 Pro')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 22123 page no 4 n bits 88 index `UNIQ_ORDER_ID` of table `testdb`.`test_device` trx id 46451826237 lock_mode X locks gap before rec
Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 9; hex 393134303039303039; asc 914009009;;
 1: len 4; hex 80000035; asc    5;;

*** (2) WAITING FOR THIS LOCK TO BE GRAN

最低0.47元/天解锁文章