mysql deadlock、Lock wait timeout解决和分析

项目上线线上遇到大量的deadlock 和wait timeout 但是看程序没什么问题问dba也不能给出很好的解决方案！最终自己去了解mysql锁以及看mysq锁日志如果了解mysql锁的机制下分析就很好解决

mysql的几种锁

X锁(排他锁) :

　　与其他X锁和S锁互斥

S锁(共享锁):

　　与X锁互斥当一个事物获得S锁别的事物可以继续获得S锁但是不能加X锁 X锁与X锁和S锁互斥

IX(意向排他锁)

　 IX是表级的　mysql引擎自动控制在获得X锁之前会先获得IX锁 IX只会与表级的S,X锁互斥. 当mysql对表级进行加锁(X或者S)的时候不用一行一行对数据判断是否加了X锁直接根据是否有IX锁来进行判断，提高了效率

IS(意向共享锁)

IS是锁是表级的 mysql引擎自动控制在获得S锁之前会先获得IS锁 IS锁只会与表级X锁互斥当mysql锁对表级进行加X锁是不用一行一行对数据判断是否加了S锁直接判断是否存在表级的IS意向锁

gap(锁)

间隙锁用于在指定索引位置区间加锁 (只会在插入是互斥)

id	age
1	10
2	20
3	30
4	40

这个时候gap锁就有[无穷小,10]，[10,20]，[20,30],[30,40][40,无穷大]

如果在RR模式下delete table where age=20 将不能插入10~20之间 20~30到之间的值

gap锁只会在insert的时候互斥 (可以理解为gap在非Insert获取的都是共享锁在Insert时获取的是排他锁)

next-key(X锁和gap锁的组合)

X锁和gap锁的组合

当前读与快照读

快照读

简单的查询 select * from student where ?

值得注意的是在RC模式下每次读取都是新的快照在RR模式下当一次快照读后后面都会读快照

session1	session2
select * from student;
	inser into student(name) values('小明')
	update student set name='小明' where id=1;
	delete student set name='小明2' where id=2;
	commit;
select * from student;
commit;

RC模式 session1第二次查询将会受到session2的操作的影响因为RC模式每次读取是读取新的快照

RR模式 session1第二次查询不会受到session2的影响因为后面每次读取都是读取快照(session1 update insert delete也会更新快照)

ps:以上结果经过在mysql innodb模式实践

当前读

select * student where ? lock in share mode 共享(S)锁

select * student where ? for update( 排他(X)锁

insert

delete

update

快照读是不加锁的当前读都会获取相应的锁

聚簇索引以及二级索引

mysql加锁不是锁住某一行数据而是在表的索引上面加锁理解聚簇索引和二级索引能够很好的帮助我们理解锁

什么是聚簇索引?

在innodb下数据的存储顺序跟索引的存储存储顺序是一样聚簇索引的页节点就指向数据，每个表都会有一个聚簇索引默认在主键上如果没有找到将会在表中的唯一非空的列上加上聚簇索引如果没有mysql将自动维护一个隐式的列作为聚簇索引

二级索引(非聚簇索引)

二级索引的页节点存储的是聚簇索引当查询一条非聚簇索引的列会先根据索引找到聚簇索引再根据聚簇索引查找数据

mysql RC和RR隔离级别的加锁方式

现在有student表有以下数据

id	name
1	a
2	b
3	d
4	f

查看和设置默认事物隔离级别

SELECT @@tx_isolation;

对当前session设置隔离级别(如果不加session就是global)

可选:READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

set session transaction isolation level read uncommitted;

RC模式

delete student where id=1;

如果id列是主键

将会在id为1的的聚簇索引上加上X锁

如果id列为唯一索引

将会在唯一索引上加上X锁同时根据唯一索引找到聚簇索引并加锁为什么在唯一索引上面加了X锁还要在聚簇索引上加锁因为如果这个时候另外一个事物根据聚簇索引更新数据将感知不到锁

比如上面表 name为聚簇索引 delete student where id=1; id=1的索引将加上锁这个但是聚簇索引name没有加上 update student id=2 where name=a 这个时候 name=a 获取锁成功能将能修改成功

上面的解释但是会有疑惑既然加锁都加载聚簇索引上为什么还要多此一举的在非聚簇索引列上加锁(个人理解判断锁互斥时直接根据条件的索引而不用再次根据索引找到聚簇索引)

如果id列非唯一索引

跟唯一索引加锁机制一样

如果id列无索引

将在走聚簇索引全表扫描不管是否满足条件的数据都会加上X锁但是RC模式有优化不满足条件的加锁之后又会释放

RR模式

如果id列是主键

将会在满足条件的聚簇索引上加上X锁

如果id列为唯一索引

在id列上加上X锁同时在对应的聚簇索引加上X锁

如果id列非唯一索引

将在id列上加上next-key锁同时在聚簇索引加上x锁

如果id列无索引

将在走聚簇索引全表扫描不管是否满足条件的数据都会加上next-key锁跟RC模式不同的是将不会释放

deadlock分析

环境:RR隔离级别

线上报大量的deadlock 通过命令登录mysql 通过此SHOW ENGINE INNODB STATUS\G将打印最近一次死锁

定位到代码有这样一个操作

1.delete from dealer_order_item where dealer_order_code='1111'

2.insert dealer_order_item(dealer_order_code,.....) values('1111',....)

dealer_order_code 没有索引

通过上面的锁分析 RR模式下如果当前读没有满足条件的数据整个表每一条数据都将会加上next-key锁如下

session1	session2
delete from dealer_order_item where dealer_order_code='1111'
	delete from dealer_order_item where dealer_order_code='2222'
insert dealer_order_item(dealer_order_code,.....) values('1111',....)

结果:session1执行insert会死锁 session2执行delete会等待

1.session1 delete全表扫描获得所有行的gap锁和x锁(gap锁是共享的)

2.session2执行 delete全表扫描获得gap锁然后锁等待session释放x锁(gap锁是共享)

3.session2执行 delete 尝试获得next-key锁,因为gap锁是共享的所以成功拿到,但是X锁session1还没有释放所以等待

4.session1执行insert 获取next-key锁。成功获取X锁，因为session1 insert获取gap锁是X锁。发现session2没有释放他获取的gap共享锁。形成相互等待死锁

情况2(已经重现)

session1:

delete from prm_booking_wine_coin_record_item where type=0;

session2:

delete from prm_booking_wine_coin_record_item where type=3;

非insert模式下申请的gap锁都是共享锁

session1

insert prm_booking_wine_coin_record_item valuse(21,2) 等待session 2释放 3的gap锁

session2

insert prm_booking_wine_coin_record_item valuse(22,2) 等待 session 1释放2的gap锁

死锁 session2释放锁 session提交成功

waitlock分析

表数据id为主键索引 name为非唯一索引

select * from information_schema.innodb_trx;表

trx_id(事物id)	trx_state(事物状态)	trx_started(事物开始时间)	trx_requested_lock_id	trx_wait_started(事物开始等待时间)	trx_weight	trx_mysql_thread_id(事物线程id)	trx_query(具体sql)	trx_operation_state(事物当前操作状态)	trx_tables_in_use(事物中多少个表被使用)	trx_tables_locked（事物锁了多少个表）	trx_lock_structs	trx_lock_memory_bytes(事物锁住的内存大小)	trx_rows_locked(事物拥有多少个锁)	trx_rows_modified(事物修改的行数)	trx_concurrency_tickets(事物并发票数)	trx_isolation_level(事物隔离级别)	trx_unique_checks（是否唯一检查）	trx_foreign_key_checks(是否外键检查)	trx_last_foreign_key_error(最后的外键错误)	trx_adaptive_hash_latched	trx_adaptive_hash_timeout	trx_is_read_only	trx_autocommit_non_locking
7842656	LOCKWAIT	2019-01-14 10:22:04	7842656:25:4:4	2019-01-14 10:22:04	8	3733599	update demo set name='5555' where name='3333'	updating or deleting	1	1	5	1136	10	3	0	REPEATABLE READ	1	1	NULL	0	0	0	0
7842647	RUNNING	2019-01-14 10:21:37	NULL	NULL	7	3733596	NULL	NULL	0	1	4	1136	7	3	0	REPEATABLE READ	1	1	NULL	0	0	0	0

select * from information_schema.innodb_lock_waits;表

requesting_trx_id(请求锁的事物id)	requested_lock_id(请求锁的锁id)	blocking_trx_id(当前拥有锁的事物id)	blocking_lock_id（当前拥有锁锁id）
7842656	7842656:25:4:4	7842647	7842647:25:4:4

select * from information_schema.innodb_locks;表

lock_id(锁id)	lock_trx_id	lock_mode(锁模式)	lock_type(锁类型)	lock_table(被做锁的表)	lock_index(被锁的索引)	lock_space(被锁的表空间号)	lock_page(被锁的页号)	lock_rec(被锁的记录号)	lock_data(被锁的数据)
7842656:25:4:4	7842656(拥有锁的事物id)	X,GAP	RECORD	`dms`.`demo`	index_name	25	4	4	'6666', 2
7842647:25:4:4	7842647	X	RECORD	`dms`.`demo`	index_name	25	4	4	'6666', 2

1. 先看表1 事物id 7842656等待7842647释放锁 trx_rows_locked字段看命名像是锁了多少数据不过我根据数据分析应该是获得了多少个锁 name索引加上聚簇索引id的锁+3个gap锁等于10

ctrl+f 搜索:7842647 看关系更佳

2.分析表2 事物id7842656等待事物id7842647释放7842647:25:4:4这个锁

3.分析表3就清晰很多了事物id7842656是请求这个锁7842656:25:4:4

4.结论 session 2 尝试修改name为5555会获得索引为5555的x锁和gap锁但是被seesion1获得没释放所以造成锁等待

mysql 锁等待分析相关表

information_schema.innodb_trx表

包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务

information_schema.innodb_lock_waits表

包含了blocked的事务的锁等待的状态

information_schema.innodb_locks表

主要包含了InnoDB事务锁的具体情况，包括事务正在申请加的锁和事务加上的锁。

如何避免deadlock和wait lock

delete update 避免使用非索引字段为条件

RR隔离级别将会走聚簇索引全表扫描为每一行加上next-key锁注:RC隔离级别会逐行加X锁并释放

可以这样理解:delete update 扫描一条就会为一条加上锁当没有索引会全表扫描 RR隔离级别扫描一条就会为这条加上锁并不会释放 RC隔离级别扫描一条就会为这条加上锁如果不满足条件的加上锁之后会自动释放

name非索引条件用于修改条件虽然有满足条件数据也会导致全表扫描并逐行加X锁

我们为name加上索引条件再进行测试

alter table demo add index index_name(name);

可以发现修改成功并不会等待

避免批量修改删除避免无序

1.session修改id为1的数据 session2修改id为4的数据各自拿到next-key锁

2.session1 修改id为4的数据等待session2释放next-key锁 session2修改id为1的数据等待session1释放next-key锁造成死锁

批量修改或者删除统一排序一下比如这里根据id 就不会出现交叉修改依赖

避免隐式转换

session1 name为varchar 确传入number导致隐式转换走聚簇索引全表扫描导致整个表都锁了

记录一个小插曲

可以看到 name有索引也没有隐式转换也锁了整个表。因为这个是mysql的优化机制当扫描的数据超过全表的20%~30%时即便有二级索引也会走扫描整个聚簇索引( 个人测试针对当前读是这样 select不受影响)

避免where条件全表扫描

其实总结上面可以发现当前读是根据where条件扫描一条就加一个锁

避免操作不存在的数据

修改或者编辑最好先判断数据不存在

我们观察一下锁的情况

可以发现操作不存在数据会触发gap对应索引排序的gap锁锁无穷大影响插入数据(应该在只会在RR模式上出现不过在不确定数据是否存在操作之前先判断是否存在是个好习惯)

死锁日志阅读

**************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2022-07-29 11:12:50 7f59259f7700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 14740 srv_active, 0 srv_shutdown, 1509 srv_idle
srv_master_thread log flush and writes: 16248
----------
SEMAPHORES
----------
......省略 
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-07-29 11:03:54 7f5a16625700  //死锁发生时间
*** (1) TRANSACTION: //事物一
TRANSACTION 18426449113 /**事物id**/, ACTIVE 0 sec starting index read //事务1从开始执行到被死锁检测经历的时间，生产环境一般看到的是0  
mysql tables in use 1, locked 1 //事务1当前查询使用到的表，以及已经加锁的表。分别是trx->n_mysql_tables_in_use，trx->mysql_n_tables_locked
LOCK WAIT/**表示事务状态处于锁等待**/ 2 lock struct(s)/**表示此事务中的锁结构数目**/, heap size 360 /**代表内存堆的byte长度**/, 1 row lock(s) /**是锁了一行**/
MySQL thread id 8760 /**对应的操作系统的线程结构（pthread_t)**/, OS thread handle 0x7f5928f55700, query id 115289603 10.7.61.60 /**来源ip**/ root /**用户名**/ updating
UPDATE ticket SET updated_at='2022-07-29 11:03:54'  WHERE id=211358
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  //此事务要加的锁，但是被阻塞。
RECORD LOCKS space id 650 page no 8242 n bits 144 index `PRIMARY` of table `appliance`.`ticket` trx id 18426449113 lock_mode X /**获取X锁**/ locks rec but not gap waiting /**不加gap锁**/
Record lock, heap no 29 PHYSICAL RECORD: n_fields 38; compact format; info bits 0
 0: len 4; hex 8003399e; asc   9 ;;
 1: len 6; hex 00044982b1db; asc   I   ;;
 2: len 7; hex 2b000029891a39; asc +  )  9;;
 3: len 4; hex 62e2250a; asc b % ;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 8; hex 800000000150daf8; asc      P  ;;
 7: len 1; hex 03; asc  ;;
 8: len 1; hex 05; asc  ;;
 9: len 5; hex 99ad78deef; asc   x  ;;
 10: SQL NULL;
 11: SQL NULL;
 12: len 4; hex 8000bc97; asc     ;;
 13: len 21; hex e6808ee4b988e68f90e78eb0e6b4bee8b4b9e38082; asc                      ;;
 14: SQL NULL;
 15: len 4; hex 80004ba5; asc   K ;;
 16: len 4; hex 803cb5fa; asc  <  ;;
 17: len 4; hex 8009dd74; asc    t;;
 18: len 4; hex 800069e4; asc   i ;;
 19: len 4; hex 80013190; asc   1 ;;
 20: len 4; hex 803cb5fa; asc  <  ;;
 21: len 4; hex 80034582; asc   E ;;
 22: SQL NULL;
 23: len 4; hex 800339e7; asc   9 ;;
 24: len 13; hex 3232332e3130342e3139332e36; asc 223.104.193.6;;
 25: len 23; hex 7b27736572766963654465736b4964273a32373130387d; asc {'serviceDeskId':27108};;
 26: SQL NULL;
 27: len 1; hex 00; asc  ;;
 28: len 30; hex 646161313434343030653339313165646135303465313634663662633063; asc daa144400e3911eda504e164f6bc0c; (total 32 bytes);
 29: SQL NULL;
 30: SQL NULL;
 31: SQL NULL;
 32: SQL NULL;
 33: len 1; hex 00; asc  ;;
 34: len 12; hex e5b1b1e4b89ce6bd8de59d8a; asc             ;;
 35: len 6; hex 333730373030; asc 370700;;
 36: len 6; hex 333730303030; asc 370000;;
 37: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 18426448577, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 6768, OS thread handle 0x7f5a16625700, query id 115289609 10.7.66.63 root updating
UPDATE ticket SET updated_at='2022-07-29 11:03:54'  WHERE id=211358
*** (2) HOLDS THE LOCK(S): /**持有的锁**/
RECORD LOCKS space id 650 page no 8242 n bits 144 index `PRIMARY` of table `appliance`.`ticket` trx id 18426448577 lock mode S /**S锁**/ locks rec but not gap
Record lock, heap no 29 PHYSICAL RECORD: n_fields 38; compact format; info bits 0
 0: len 4; hex 8003399e; asc   9 ;;
 1: len 6; hex 00044982b1db; asc   I   ;;
 2: len 7; hex 2b000029891a39; asc +  )  9;;
 3: len 4; hex 62e2250a; asc b % ;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 8; hex 800000000150daf8; asc      P  ;;
 7: len 1; hex 03; asc  ;;
 8: len 1; hex 05; asc  ;;
 9: len 5; hex 99ad78deef; asc   x  ;;
 10: SQL NULL;
 11: SQL NULL;
 12: len 4; hex 8000bc97; asc     ;;
 13: len 21; hex e6808ee4b988e68f90e78eb0e6b4bee8b4b9e38082; asc                      ;;
 14: SQL NULL;
 15: len 4; hex 80004ba5; asc   K ;;
 16: len 4; hex 803cb5fa; asc  <  ;;
 17: len 4; hex 8009dd74; asc    t;;
 18: len 4; hex 800069e4; asc   i ;;
 19: len 4; hex 80013190; asc   1 ;;
 20: len 4; hex 803cb5fa; asc  <  ;;
 21: len 4; hex 80034582; asc   E ;;
 22: SQL NULL;
 23: len 4; hex 800339e7; asc   9 ;;
 24: len 13; hex 3232332e3130342e3139332e36; asc 223.104.193.6;;
 25: len 23; hex 7b27736572766963654465736b4964273a32373130387d; asc {'serviceDeskId':27108};;
 26: SQL NULL;
 27: len 1; hex 00; asc  ;;
 28: len 30; hex 646161313434343030653339313165646135303465313634663662633063; asc daa144400e3911eda504e164f6bc0c; (total 32 bytes);
 29: SQL NULL;
 30: SQL NULL;
 31: SQL NULL;
 32: SQL NULL;
 33: len 1; hex 00; asc  ;;
 34: len 12; hex e5b1b1e4b89ce6bd8de59d8a; asc             ;;
 35: len 6; hex 333730373030; asc 370700;;
 36: len 6; hex 333730303030; asc 370000;;
 37: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED://加锁但是被阻塞等待
RECORD LOCKS space id 650 page no 8242 n bits 144 index `PRIMARY` of table `appliance`.`ticket` trx id 18426448577 lock_mode X /**X锁**/ locks rec but not gap waiting /**不获取gap锁**/
Record lock, heap no 29 PHYSICAL RECORD: n_fields 38; compact format; info bits 0
 0: len 4; hex 8003399e; asc   9 ;;
 1: len 6; hex 00044982b1db; asc   I   ;;
 2: len 7; hex 2b000029891a39; asc +  )  9;;
 3: len 4; hex 62e2250a; asc b % ;;
 4: SQL NULL;
 5: SQL NULL;
 6: len 8; hex 800000000150daf8; asc      P  ;;
 7: len 1; hex 03; asc  ;;
 8: len 1; hex 05; asc  ;;
 9: len 5; hex 99ad78deef; asc   x  ;;
 10: SQL NULL;
 11: SQL NULL;
 12: len 4; hex 8000bc97; asc     ;;
 13: len 21; hex e6808ee4b988e68f90e78eb0e6b4bee8b4b9e38082; asc                      ;;
 14: SQL NULL;
 15: len 4; hex 80004ba5; asc   K ;;
 16: len 4; hex 803cb5fa; asc  <  ;;
 17: len 4; hex 8009dd74; asc    t;;
 18: len 4; hex 800069e4; asc   i ;;
 19: len 4; hex 80013190; asc   1 ;;
 20: len 4; hex 803cb5fa; asc  <  ;;
 21: len 4; hex 80034582; asc   E ;;
 22: SQL NULL;
 23: len 4; hex 800339e7; asc   9 ;;
 24: len 13; hex 3232332e3130342e3139332e36; asc 223.104.193.6;;
 25: len 23; hex 7b27736572766963654465736b4964273a32373130387d; asc {'serviceDeskId':27108};;
 26: SQL NULL;
 27: len 1; hex 00; asc  ;;
 28: len 30; hex 646161313434343030653339313165646135303465313634663662633063; asc daa144400e3911eda504e164f6bc0c; (total 32 bytes);
 29: SQL NULL;
 30: SQL NULL;
 31: SQL NULL;
 32: SQL NULL;
 33: len 1; hex 00; asc  ;;
 34: len 12; hex e5b1b1e4b89ce6bd8de59d8a; asc             ;;
 35: len 6; hex 333730373030; asc 370700;;
 36: len 6; hex 333730303030; asc 370000;;
 37: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 18426617096
Purge done for trx's n:o < 18426614085 undo n:o < 0 state: running
History list length 1054
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 21294, OS thread handle 0x7f59259f7700, query id 118914682 db2 10.7.63.64 root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 21290, OS thread handle 0x7f5925973700, query id 118462473 10.7.56.64 root cleaning up
ser` u WHERE u.`status` = 1 AND u.valid = TRUE AND u.deleted = FALSE AND u.suspended = FALSE AND u.provider_id = 19365 AND ( ( u.email = '01809980' AND u.email_examined = TRUE ) OR (u.mobile_phone = '01809980' AND u.mobile_phone_examined = TRUE) OR (u.external_id = '01809980') ) LIMIT 1
---TRANSACTION 18426597114, not started
mysql tables in use 1, locked 0
MySQL thread id 8676, OS thread handle 0x7f592f1d1700, query id 118867982 10.7.66.63 root Sending data
SELECT * FROM `user` u WHERE u.`status` = 1 AND u.valid = TRUE AND u.deleted = FALSE AND u.suspended = FALSE AND u.provider_id = 19365 AND ( ( u.email = '01811970' AND u.email_examined = TRUE ) OR (u.mobile_phone = '01811970' AND u.mobile_phone_examined = TRUE) OR (u.external_id = '01811970') ) LIMIT 1
......省略部分

posted @ 2018-12-17 20:38 意犹未尽阅读(7819) 评论(0) 编辑收藏举报

刷新页面返回顶部

意犹未尽

只要在学习，则在进步。