mysqldeletein死锁_mysql执⾏delete引发死锁问题
关于mysql事务引发的死锁异常解决
场景
问题
死锁⽇志[^2]
锁类型与隔离级别
InnerDB 锁：
mysql事务
解决⽅案
场景
mysql 5.7
InnoDB存储引擎
jdk 8 springboot
hikari 连接池
spring:
application:
name: product
datasource:
url: jdbc:mysql://123456:3306/productdb?useUnicode=true&characterEncoding=UTF-
8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
userName:
password:
driver-class-name: sql.cj.jdbc.Driver
hikari:
#连接只读数据库时配置为true， 保证安全
readOnly: false
#等待连接池分配连接的最⼤时长(毫秒)，超过这个时长还没可⽤的连接则发⽣SQLException， 缺省:30秒
connectionTimeout: 3000000
#⼀个连接idle状态的最⼤时长(毫秒)，超时则被释放(retired)，缺省:10分钟
idleTimeout: 600000
#⼀个连接的⽣命时长(毫秒)，超时⽽且没被使⽤则被释放(retired)，缺省:30分钟，建议设置⽐数据库超时时长少30秒，参考MySQL wait_timeout参数(show variables like '%timeout%';，⼀般为8⼩时)
#maxLifetime: 600000
#连接池中允许的最⼤连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count)
maximumPoolSize: 200
pool-name: pool-lxl
connectionTestQuery: SELECT 1
minimum-idle: 50
导⼊号和修改⼿机号场景，⾼并发情况下频繁修改数据库同⼀张表。
先通过⼿机号搜索出数据。
数字大小排序在线再通过uin搜索出相关的数据，如果搜索出的数据⼤于1则删除后再添加通过⼿机号搜索出的数据。
语句是delete，通过uin删除数据，当前情景下uin⽆索引。
同样的执⾏操作，并发场景：
登录失败时删除
⽤户⼿动删除数据
问题
mysql删除语句
delete from device where uin = xxxxxxxxxx
等待锁
index PRIMARY of table productdb.device trx id 27264001 lock_mode X locks rec but not gap waiting
等待锁类型1
X locks rec but not gap waiting
死锁⽇志2
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-06-29 12:16:52 0x7f03c2cfd700
*** (1) TRANSACTION:
TRANSACTION 27264001, ACTIVE 5 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 73 lock struct(s), heap size 8400, 16 row lock(s), undo log entries 9
MySQL thread id 3248046, OS thread handle 139658719131392, query id 201165398 172.31.242.1 root updating
delete from device
where uin = 1107000000
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 224 page no 75 n bits 208 index PRIMARY of table `productdb`.`device` trx id 27264001 lock_mode X locks rec but not gap waiting
Record lock, heap no 104 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 11; hex 3134353231393836343539; asc 14521986459;;
1: len 6; hex 0000019fd14c; asc L;;
2: len 7; hex a70000014d0110; asc M ;;
3: SQL NULL;
4: len 19; hex 777869645f7979683937336d7036746c6d3232; asc wxid_yyh973mp6tlm22;;
5: len 8; hex 6161313233313233; asc aa123123;;
6: len 15; hex 363637323838383135323635373838; asc 667288815265788;;
7: len 16; hex 41313561643064316434666661636332; asc A15ad0d1d4ffacc2;;
8: len 8; hex 8000000014f0d0c3; asc ;;
*** (2) TRANSACTION:
TRANSACTION 27264064, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
24 lock struct(s), heap size 3520, 3 row lock(s), undo log entries 1
MySQL thread id 3248058, OS thread handle 139654130030336, query id 201165376 172.31.242.1 root updating
delete from device
where uin = 576000000
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 224 page no 75 n bits 208 index PRIMARY of table `productdb`.`device` trx id 27264064 lock_mode X locks rec but not gap
Record lock, heap no 104 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 11; hex 3134353231393836343539; asc 14521986459;;
1: len 6; hex 0000019fd14c; asc L;;
2: len 7; hex a70000014d0110; asc M ;;
3: SQL NULL;
4: len 19; hex 777869645f7979683937336d7036746c6d3232; asc wxid_yyh973mp6tlm22;;
5: len 8; hex 6161313233313233; asc aa123123;;
6: len 15; hex 363637323838383135323635373838; asc 667288815265788;;
7: len 16; hex 41313561643064316434666661636332; asc A15ad0d1d4ffacc2;;
8: len 8; hex 8000000014f0d0c3; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 224 page no 75 n bits 208 index PRIMARY of table `productdb`.`device` trx id 27264064 lock_mode X locks rec but not gap waiting
spring正确发音Record lock, heap no 23 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
jdbc技术介绍0: len 11; hex 3134353739323835393638; asc 14579285968;;
1: len 6; hex 000001a00401; asc ;;
2: len 7; hex 25000007132c16; asc % , ;;
3: SQL NULL;
4: len 19; hex 777869645f32706b646a62636c6e3432663232; asc wxid_2pkdjbcln42f22;;
5: len 11; hex 6d696e796f6e6763687539; asc minyongchu9;;
6: len 15; hex 363830303638323834323632393936; asc 680068284262996;;
7: len 16; hex 41653031623564613061616366363433; asc Ae01b5da0aacf643;;
8: len 8; hex 8000000030a54cf0; asc 0 L ;;
*** WE ROLL BACK TRANSACTION (2)
锁类型与隔离级别
InnerDB 锁：
共享锁(S Lock)：允许事务读取⼀⾏数据，多个事务可以拿到⼀把S锁(即读读并⾏)；
排他锁(X Lock)：允许事务删除或更新⼀⾏数据，多个事务有且只有⼀个事务可以拿到X锁(即写写/写读互斥)；
法国破魔咒意向共享锁(IS Lock)：事务想要获得⼀张表中某⼏⾏的共享锁；
意向排他锁(IX Lock)：事务想要获得⼀张表中某⼏⾏的排他锁；
插⼊意向锁(Insert Intention Lock)：插⼊意向锁是间隙锁的⼀种，专门针对insert操作的。即多个事务在同⼀个索引、同⼀个范围区间内插⼊记录时，如果插⼊的位置不冲突，则不会阻塞彼此
⾃增锁(Auto-inc Locks)：⾃增锁是⼀种特殊的表级别锁，专门针对事务插⼊AUTO-INCREMENT类型的列。即⼀个事务正在往表中插⼊记录时，其他事务的插⼊必须等待，以便第1个事务插⼊的⾏得到的主键值是连续的。
记录锁(Record Locks)- locks rec but not gap
记录锁是的单个⾏记录上的锁，会阻塞其他事务对其插⼊、更新、删除；
间隙锁(Gap Lock) ：间隙锁锁定记录的⼀个间隔，但不包含记录本⾝。
临键锁(Next-Key Lock)= Gap Lock + Record Lock
临建锁是记录锁与间隙锁的组合，即：既包含索引记录，⼜包含索引区间，主要是为了解决幻读
参考3
mysql事务
事务隔离级别4
脏
不可重复读
幻读
读未提交(read-uncommitted)
是
是
是
不可重复读(read-committed)
否
是
是
可重复读(repeatable-read)
否
否
是
串⾏化(serializable)
否
否
函数公式法否
解决⽅案
1、对于引起的条件语句添加索引：
uin
2、设置数据库的事务隔离级别(并发数据量⼤⼤场景作⽤不明显，需要⼩⼼脏数据)：read commit
> select @@_isolation,@@tx_isolation;
手机mysql安装配置教程> set 作⽤域 transaction isolation level 事务隔离级别;
3、针对业务场景，更新为使⽤redis记录uin与登录账号
mysql锁 ↩
死锁⽇志分析 ↩
记录⼀次MySQL死锁的分析与解决过程 ↩
MySQL的四种事务隔离级别 ↩
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论