Mysql 死锁案例1-记录锁读写冲突

码上得天下

已于 2024-03-10 16:41:37 修改

阅读量275

点赞数 1

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

于 2024-03-10 16:04:35 首次发布

本文链接：https://blog.csdn.net/meser88/article/details/136603470

版权

Mysql 专栏收录该内容

107 篇文章 14 订阅

订阅专栏

死锁复现

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t` */

insert  into `t`(`id`,`c`,`d`) values (0,0,0),(5,5,5),(10,10,10)

	事务1	事务2
T1	START TRANSACTION ; SELECT * FROM t WHERE id =5 LOCK IN SHARE MODE
T2		START TRANSACTION ; SELECT * FROM t WHERE id =5 LOCK IN SHARE MODE
T3	SELECT * FROM t WHERE id =5 FOR UPDATE （阻塞）
T4		SELECT * FROM t WHERE id =5 FOR UPDATE (死锁)

死锁分析

T1事务1加id=5主键索引读锁成功
T2事务2加id=5主键索引读锁成功
T3事务1加id=5主键索引写锁阻塞
T3事务2加id=5主键索引写锁阻塞，进入循环等待，死锁

SHOW ENGINE INNODB STATUS 查看加锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-10 15:49:31 0x10bc
*** (1) TRANSACTION:
TRANSACTION 486540, ACTIVE 21 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4, OS thread handle 7296, query id 2804 localhost ::1 root statistics
SELECT * FROM t WHERE id =5 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486540 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000046952; asc     iR;;
 2: len 7; hex 4b0000019207d7; asc K      ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 486541, ACTIVE 14 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5, OS thread handle 4284, query id 2809 localhost ::1 root statistics
SELECT * FROM t WHERE id =5 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486541 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000046952; asc     iR;;
 2: len 7; hex 4b0000019207d7; asc K      ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 638 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 486541 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 000000046952; asc     iR;;
 2: len 7; hex 4b0000019207d7; asc K      ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------