SQL死锁

飞花落雨

已于 2023-06-27 15:48:14 修改

阅读量7.8k

点赞数 1

文章标签： sql 数据库 java

于 2023-06-14 19:59:20 首次发布

本文链接：https://blog.csdn.net/u013372493/article/details/131214586

版权

解决办法： (本质：快速筛选或高效处理、以此减少锁冲突)

①大事务拆成小事务，尽可能缩小事务范围

大事务:将多个操作放在一个事务中执行

小事务：将多个操作分成多个小事务，

②业务中存在更新前和更新后一模一样的不再执行更新

前言：

使用脚本刷数据时，开多线程经常遇到死锁现象，面试也经常问到，故开此篇

日志错误示例：

### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/patpat/product/mapper/SaleProductSkuMapper.java (best guess)
### The error may involve com.patpat.product.mapper.SaleProductSkuMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE ps_sale_product_sku_XXXXX_30002  SET sku_id=?, product_id=?,    sku_code=?, image=?  WHERE id=?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(Abs

分析：

死锁产生的原因：

①不可剥夺（只能由占有资源的进程释放）

②循环等待（每个进程在等待其他进程释放，而其他进程也在等待）

③互斥条件 (一个资源只能同时被一个进程使用)

④请求与保持（申请新资源，并还不释放已占有的）

sql死锁

死锁发生的主要原因是这两个事务对表的锁定顺序不一致,先拿到一把锁的事务等待另一把锁,而那一把锁正被另一个事务持有,形成闭环等待。

模拟：

当多个事务同时访问数据库中的数据并且存在竞争条件时，就有可能发生死锁。下面是一个示例，展示了一个可能导致死锁的情况：

假设有两个用户，用户A和用户B，同时执行以下两个事务：

事务1（用户A）：

BEGIN TRANSACTION;
UPDATE orders SET status = 'processing' WHERE order_id = 1;
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
COMMIT;

事务2（用户B）：

BEGIN TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
UPDATE orders SET status = 'processing' WHERE order_id = 1;
COMMIT;

在以上操作中，存在：用户A执行自己的第一条sql，用户B执行自己第一条sql。用户A再准备执行自己的第二条时，却不能执行了，用户B也不能再执行，于是陷入死锁。如下图：

死锁产生！

（注意总结此处死锁的特点：① 开启了事务②交叉修改（如果按顺序操作是不是就不会死锁了呢？应该是的））

那么，死锁是2个以上的不同表才能出现死锁吗？不一定

如下:

事务1：
updae  A  set ... where id =1;
updae  A  set ... where id =2;
..

事务2：
update A  set ... where id =2;
update A  set ... where id =1;

因此可以总结：

①同一个表中：在一个事务中对同一表进行两次或多次操作,并且在多线程的情况下有多个这样的事务,这可能会导致死锁。

②不同表中：两个事务操作表的顺序不同，出现锁表交叉循环，导致死锁。

解决办法： (本质：快速筛选或高效处理、以此减少锁冲突)

①大事务拆成小事务，尽可能缩小事务范围

大事务:将多个操作放在一个事务中执行

优点：这样可以减少事务的提交和回滚次数，提高性能。

缺点：如果事务过大，涉及到的数据量多，会增加事务持有锁的时间，增加死锁的风险。

小事务：将多个操作分成多个小事务，

优点：每个小事务只涉及少量的数据，尽快释放锁资源。这样可以减少事务持有锁的时间，降低死锁的风险

②业务中存在更新前和更新后一模一样的不再执行更新

场景：通过运维拉去mysql日志，查询发现，下面2事务，对表ps_option_value666进行更新操作，查询数据库发现，要更新的数据和想更新的数据，一模一样！

解决方案：在代码层面检查对象内容如果一样，就不更新了！

数据库死锁日志中部分日志：

数据库原有数据：

数据库死锁日志：

2023-06-15 01:35:18 0x2b170f8877002023-06-15 01:35:18 0x2b170f887700
*** (1) TRANSACTION:
TRANSACTION 15159595386, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 506546, OS thread handle 47376231835392, query id 4253348818 172.31.19.109 app_product_0 updating
UPDATE ps_option_value666  SET option_id=6,
value='18-24 Months'  WHERE id=1118
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1236 page no 10 n bits 0 index PRIMARY of table `product_service`.`ps_option_value666` trx id 15159595386 lock_mode X locks rec but not gap waiting
Record lock, heap no 224 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len=4; bufptr=0x2b231cfcf469; hex= 0000045e; asc    ^;;
 1: len=6; bufptr=0x2b231cfcf46d; hex= 00000027a645; asc    ' E;;
 2: len=7; bufptr=0x2b231cfcf473; hex= 940000030237da; asc      7 ;;
 3: len=4; bufptr=0x2b231cfcf47a; hex= 00000000; asc     ;;
 4: len=4; bufptr=0x2b231cfcf47e; hex= 00000006; asc     ;;
 5: len=12; bufptr=0x2b231cfcf482; hex= 31382d3234204d6f6e746873; asc 18-24 Months;;
 6: SQL NULL;
 7: len=8; bufptr=0x2b231cfcf48e; hex= 0000000000001840; asc        @;;
 8: len=4; bufptr=0x2b231cfcf496; hex= 5b061db0; asc [   ;;
 9: len=4; bufptr=0x2b231cfcf49a; hex= 5f6df00d; asc _m  ;;
 10: len=0; bufptr=0x2b231cfcf49e; hex= ; asc ;;

*** (2) TRANSACTION:
TRANSACTION 15159595381, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 506549, OS thread handle 47373393610496, query id 4253348825 172.31.45.125 app_product_0 updating
UPDATE ps_option_value666  SET option_id=6,
value='4-5 Years'  WHERE id=1446
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1236 page no 10 n bits 0 index PRIMARY of table `product_service`.`ps_option_value666` trx id 15159595381 lock_mode X locks rec but not gap
Record lock, heap no 226 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len=4; bufptr=0x2b231cfcf4e0; hex= 00000460; asc    `;;
 1: len=6; bufptr=0x2b231cfcf4e4; hex= 00000027a645; asc    ' E;;
 2: len=7; bufptr=0x2b231cfcf4ea; hex= 940000030237f6; asc      7 ;;
 3: len=4; bufptr=0x2b231cfcf4f1; hex= 00000000; asc     ;;
 4: len=4; bufptr=0x2b231cfcf4f5; hex= 00000006; asc     ;;
 5: len=9; bufptr=0x2b231cfcf4f9; hex= 352d36205965617273; asc 5-6 Years;;
 6: SQL NULL;
 7: len=8; bufptr=0x2b231cfcf502; hex= 0000000000002440; asc       $@;;
 8: len=4; bufptr=0x2b231cfcf50a; hex= 5b061dbf; asc [   ;;
 9: len=4; bufptr=0x2b231cfcf50e; hex= 5f6df00d; asc _m  ;;
 10: len=0; bufptr=0x2b231cfcf512; hex= ; asc ;;

[bitmap of 256 bytes in hex: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 05 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ]
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1236 page no 12 n bits 0 index PRIMARY of table `product_service`.`ps_option_value666` trx id 15159595381 lock_mode X locks rec but not gap waiting
Record lock, heap no 66 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len=4; bufptr=0x2b231cf95154; hex= 000005a6; asc     ;;
 1: len=6; bufptr=0x2b231cf95158; hex= 00000027a645; asc    ' E;;
 2: len=7; bufptr=0x2b231cf9515e; hex= 940000030a0a02; asc        ;;
 3: len=4; bufptr=0x2b231cf95165; hex= 00000000; asc     ;;
 4: len=4; bufptr=0x2b231cf95169; hex= 00000006; asc     ;;
 5: len=9; bufptr=0x2b231cf9516d; hex= 342d35205965617273; asc 4-5 Years;;
 6: SQL NULL;
 7: len=8; bufptr=0x2b231cf95176; hex= 0000000000002240; asc       "@;;
 8: len=4; bufptr=0x2b231cf9517e; hex= 5b061fd6; asc [   ;;
 9: len=4; bufptr=0x2b231cf95182; hex= 5f6df00d; asc _m  ;;
 10: len=0; bufptr=0x2b231cf95186; hex= ; asc ;;

*** WE ROLL BACK TRANSACTION (2)