一个MySQL死锁问题的解决最近在项目开发过程中,碰到了数据库的死锁问题,在解决问题的过程中,加深了对MySQL InnoDB引擎锁机制的理解。
我们使用Show innodb status检查引擎状态时,发现了死锁问题:
*** (1) TRANSACTION:
TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320
MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME<date_sub(now(), INTERVAL 30 minute)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec but not gap waiting
Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
*** (2) TRANSACTION:
TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating
update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap
Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap waiting
Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;
*** WE ROLL BACK TRANSACTION (1)
该死锁问题涉及TSK_TASK表,该表用于保存系统监测任务,相关字段及索引如 下:
ID:主键;
MON_TIME:监测时间;
STATUS_ID:任务状态;
索 引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。
经分析,涉及的两条语句应该不会 涉及相同的TSK_TASK记录,那为什么会造成死锁呢?
查询MySQL官网文档,发现这跟MySQL的索引机制有关。MySQL的 InnoDB引擎是行级锁,我原来的理解是直接对记录进行锁定,实际上不是这样的,其要点如下:
不是对记录进行锁定,而是对索引 进行锁定;
在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓 的next-key locking;
如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000会锁定所有主键大于等于1000的所有记录,在该语句完成之前,你就不能对主键等于10000的记录进行操作;
当 非簇索引(non-cluster index)记录被锁定时,相关的簇索引(cluster index)记录也需要被锁定才能完成相应的操作。
再 分析一下发生问题的两条SQL语句,就不难找到问题所在了:
当“update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME<date_sub(now(), INTERVAL 30 minute)”执行时,MySQL会使用KEY_TSKTASK_MONTIME2索引,因此首先锁定相关的索引记录,因为 KEY_TSKTASK_MONTIME2是非簇索引,为执行该语句,MySQL还会锁定簇索引(主键索引)。
假设“update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)”几乎同时执行时,本语句首先锁定簇索引(主键),由于需要更新STATUS_ID的值,所以还需要锁定 KEY_TSKTASK_MONTIME2的某些索引记录。
这样第一条语句锁定了KEY_TSKTASK_MONTIME2的记录,等待 主键索引,而第二条语句则锁定了主键索引记录,而等待KEY_TSKTASK_MONTIME2的记录,这样死锁就产生了。
我们通过拆分 第一条语句解决了死锁问题:即先查出符合条件的ID:select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME < date_sub(now(), INTERVAL 30 minute);然后再更新状态:update TSK_TASK set STATUS_ID=1064 where ID in (….)
这样就不会产生索引的竞争问题,死锁问题就 解决了。
- 浏览: 1745709 次
最新评论
-
xinglianxlxl:
有空看看,谢谢
eclipse经常未响应优化 -
code_xiaoke:
我觉得Lucene版本变化太大!! 对于版本的变动 太巨大了 ...
Lucene使用IKAnalyzer分词实例 及 IKAnalyzer扩展词库 -
jayant-xu:
...
BigDecimal加减乘除运算 -
jayant-xu:
[img][/img]大大
BigDecimal加减乘除运算 -
jayant-xu:
[*]案说法是否
BigDecimal加减乘除运算
相关推荐
} while (需要继续检测死锁); 五、死锁检测机制的优点和缺点 MySQL 的死锁检测机制有以下优点: * 能够检测和解决死锁问题,确保事务的可靠执行。 * 能够提高系统的性能和可扩展性。 然而,MySQL 的死锁检测机制...
- 事务等待超时:MySQL的InnoDB存储引擎有死锁检测机制,当事务等待超过预设的超时时,会被自动回滚。 - 锁粒度不一致:行级锁与表级锁的混合使用可能导致死锁。 2. **死锁的检测与解决**: - InnoDB存储引擎...
MySQL InnoDB引擎检测到死锁后,会执行以下操作: - 死锁检测:InnoDB有内置的死锁检测机制,会在事务等待一段时间(可通过`innodb_lock_wait_timeout`参数设置)后进行检查。 - 死锁解决:一旦检测到死锁,InnoDB会...
MySQL通过内部机制自动检测死锁。当检测到死锁时,通常会选择回滚其中一个事务来解除死锁。可以通过查询`INFORMATION_SCHEMA.INNODB_TRX`表来查看当前活跃的事务信息,包括事务的状态、开始时间、等待锁的信息等。 ...
1. **自动检测和解除死锁**:InnoDB会定期检查是否存在死锁,并自动解除死锁,牺牲其中一个事务来恢复系统的正常运行。 2. **锁等待超时**:设置合理的锁等待超时时间,避免长时间等待而导致死锁。 3. **优化事务...
此外,可以使用死锁检测功能,一旦检测到死锁,MySQL会自动选择并回滚一个事务,以打破死锁循环。 在深入剖析死锁的成因时,我们可能需要考虑的因素包括事务的执行顺序、锁的粒度、索引的使用,以及并发控制的细节...
2. 使用死锁检测机制,当检测到死锁时,MySQL会回滚其中一个事务以打破死锁循环。 3. 优化SQL查询,避免全表扫描,减少间隙锁的使用。 4. 考虑使用更宽松的事务隔离级别,如可重复读(Repeatable Read)降级为读已...
对于REPLACE,MySQL会在检测到唯一键冲突时先放置一个排他锁(X Lock),然后删除原有行,最后插入新行。这一过程可能引发死锁,尤其是在高并发环境下。 死锁的出现通常涉及多个并发事务对资源的竞争。在这个例子中...
在MySQL的InnoDB存储引擎中,死锁检测机制能自动识别并解决这种情况,通常通过回滚其中一个事务来打破循环。 当发生死锁时,MySQL服务器会返回一个错误码ERROR 1213,提示用户有死锁情况。InnoDB存储引擎的特性之一...
MySQL的InnoDB引擎通过死锁检测算法来识别死锁,并选择将持有最少行级排他锁的事务进行回滚,以打破循环并释放资源,从而解决死锁。 在实际应用中,如果遇到类似的问题,如旅游电商平台接口出现请求超时异常,可以...
MySQL检测到死锁后,会通过InnoDB存储引擎的死锁检测机制来解决。它会选择一个事务进行回滚,释放其持有的锁,让其他事务可以继续执行。这个过程可以通过`SHOW ENGINE INNODB STATUS;`命令查看详细信息。 为了预防...
当MySQL检测到死锁时,可以通过查看日志或者在MySQL客户端中执行`SHOW INNODB STATUS\G`命令来获取死锁的具体信息。在这个案例中,通过查看死锁记录可以发现,冲突确实发生在主键索引上。 #### 锁冲突分析 ##### ...
7. **死锁检测与回滚**:MySQL数据库自身具备死锁检测机制,当检测到死锁时,会自动回滚其中一个事务,释放资源以解除死锁。 8. **资源超时与重试策略**:设置事务超时时间,当事务执行超过预设时间仍未完成,可...
3. **死锁检测与恢复**:数据库管理系统定期检查是否存在死锁,一旦发现,选择一个死锁进程进行回滚,使其释放资源,从而打破死锁状态。 为了减少死锁,可以采取以下策略: 1. **统一访问顺序**:设定所有事务访问...
5. **死锁检测**: 使用MySQL的死锁检测功能,并在应用程序中处理死锁异常。 通过深入理解这些死锁的特征、原因和解决方案,可以有效地预防和处理MySQL中的死锁问题,确保数据库系统的稳定性和高可用性。在实际应用...
例如,MySQL的InnoDB存储引擎会自动检测死锁,并选择一个事务进行回滚。同时,DBA还可以通过调整事务隔离级别、合理设计锁粒度、避免长时间持有锁等方式预防死锁。 总的来说,"数据库死锁检测工具"对于数据库管理至...
检测死锁通常涉及解析事务的状态和锁定信息。项目可能自定义了算法或者使用MySQL提供的INFORMATION_SCHEMA.INNODB_LOCK_WAITS视图来识别死锁。 4. **系统调用**:当检测到死锁或其他异常时,Java程序可以通过执行...