56,678
社区成员
发帖
与我相关
我的任务
分享MySQL死锁：Lock wait timeout exceeded; try restarting transaction
使用框架：Spring3.0 + Hibernate3.5 + MySQL
使用Spring事务管理器对事务管理，Service服务中使用@Transactional注解声明要使用的事务管理器
1、下位机每隔100毫秒向上位机发送心跳，会根据主键对itemgroup表先Select查询，再执行update更新操作（更新last_online_time时间戳字段），update语句如下：
UPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=?
2、上位机每隔10秒会对itemgroup表进行Select查询，并执行update操作（更新last_check_time时间戳字段），update语句如下：
UPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=?
经过四五十分钟，出现
“org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [update itemgroup set alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? where id=?]; SQL state [41000]; error code [1205]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction”异常
执行MySQL show full processlist命令，发现存在死锁。请问该问题如何解决？谢谢！！！！
使用Spring事务管理器对事务管理，Service服务中使用@Transactional注解声明要使用的事务管理器
1、下位机每隔100毫秒向上位机发送心跳，会根据主键对itemgroup表先Select查询，再执行update更新操作（更新last_online_time时间戳字段），update语句如下：
UPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=?
2、上位机每隔10秒会对itemgroup表进行Select查询，并执行update操作（更新last_check_time时间戳字段），update语句如下：
UPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=?
经过四五十分钟，出现
“org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [update itemgroup set alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? where id=?]; SQL state [41000]; error code [1205]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction”异常
执行MySQL show full processlist命令，发现存在死锁。请问该问题如何解决？谢谢！！！！
...全文
请发表友善的回复…
发表回复
shaoabramdy 2012-06-07
- 打赏
- 举报
贴上SHOW PROCESSLIST的日志
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28, signal count 28
Mutex spin waits 0, rounds 352, OS waits 11
RW-shared spins 30, OS waits 15; RW-excl spins 4, OS waits 2
------------
TRANSACTIONS
------------
Trx id counter 0 1436831
Purge done for trx's n:o < 0 1436818 undo n:o < 0 0
History list length 26
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 4048
MySQL thread id 4, query id 8851 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 0, not started, OS thread id 2512
MySQL thread id 3, query id 8839 localhost 127.0.0.1 root
---TRANSACTION 0 1436830, ACTIVE 95 sec, OS thread id 2860
mysql tables in use 1, locked 1
MySQL thread id 16, query id 8849 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:58:07', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436829, ACTIVE 104 sec, OS thread id 1244
mysql tables in use 1, locked 1
MySQL thread id 9, query id 8823 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:56:57', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:58', online_duration=18642 where id=1
---TRANSACTION 0 1436828, ACTIVE 105 sec, OS thread id 4200
mysql tables in use 1, locked 1
MySQL thread id 18, query id 8810 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:57', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436827, ACTIVE 115 sec, OS thread id 4196
mysql tables in use 1, locked 1
MySQL thread id 17, query id 8793 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:47', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436826, ACTIVE 125 sec, OS thread id 1572 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 7, query id 8753 localhost 127.0.0.1 root Updating
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:37', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1235 n bits 88 index `PRIMARY` of table `emview3_core_db`.`itemgroup` trx id 0 1436826 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000015ec96; asc ;; 2: len 7; hex 00000005421964; asc B d;; 3: len 7; hex 454d5332363030; asc EMS2600;; 4: len 13; hex 3139322e3136382e312e323030; asc 192.168.1.200;; 5: len 8; hex 8000124a5d8878e7; asc J] x ;; 6: len 12; hex c8f0beb0bce0bfd8d5beb5e3; asc ;; 7: len 8; hex 8000124a5d89b1f5; asc J] ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 80000000; asc ;; 10: len 1; hex 31; asc 1;; 11: len 4; hex 80000000; asc ;; 12: len 4; hex 80000000; asc ;; 13: len 4; hex 80000000; asc ;; 14: len 8; hex 8000124a5d97497b; asc J] I{;; 15: SQL NULL; 16: SQL NULL; 17: len 8; hex 8000124a5d97497b; asc J] I{;; 18: len 8; hex 80000000000048d2; asc H ;; 19: len 4; hex 80000000; asc ;; 20: len 4; hex 80000001; asc ;; 21: len 4; hex 80000001; asc ;; 22: len 4; hex 80000001; asc ;; 23: len 4; hex 80000001; asc ;;
------------------
---TRANSACTION 0 1436822, ACTIVE 155 sec, OS thread id 3604
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, 2 row lock(s), undo log entries 2
MySQL thread id 5, query id 8813 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=1, fault_num=0, last_check_time='2011-06-07 07:57:07', last_downline_time='2011-06-07 07:55:28', last_notice_time=null, last_online_time='2011-06-06 18:48:21', online_duration=0 where id=3
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28, signal count 28
Mutex spin waits 0, rounds 352, OS waits 11
RW-shared spins 30, OS waits 15; RW-excl spins 4, OS waits 2
------------
TRANSACTIONS
------------
Trx id counter 0 1436831
Purge done for trx's n:o < 0 1436818 undo n:o < 0 0
History list length 26
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 4048
MySQL thread id 4, query id 8851 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 0, not started, OS thread id 2512
MySQL thread id 3, query id 8839 localhost 127.0.0.1 root
---TRANSACTION 0 1436830, ACTIVE 95 sec, OS thread id 2860
mysql tables in use 1, locked 1
MySQL thread id 16, query id 8849 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:58:07', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436829, ACTIVE 104 sec, OS thread id 1244
mysql tables in use 1, locked 1
MySQL thread id 9, query id 8823 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:56:57', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:58', online_duration=18642 where id=1
---TRANSACTION 0 1436828, ACTIVE 105 sec, OS thread id 4200
mysql tables in use 1, locked 1
MySQL thread id 18, query id 8810 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:57', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436827, ACTIVE 115 sec, OS thread id 4196
mysql tables in use 1, locked 1
MySQL thread id 17, query id 8793 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:47', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436826, ACTIVE 125 sec, OS thread id 1572 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 7, query id 8753 localhost 127.0.0.1 root Updating
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:37', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1235 n bits 88 index `PRIMARY` of table `emview3_core_db`.`itemgroup` trx id 0 1436826 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000015ec96; asc ;; 2: len 7; hex 00000005421964; asc B d;; 3: len 7; hex 454d5332363030; asc EMS2600;; 4: len 13; hex 3139322e3136382e312e323030; asc 192.168.1.200;; 5: len 8; hex 8000124a5d8878e7; asc J] x ;; 6: len 12; hex c8f0beb0bce0bfd8d5beb5e3; asc ;; 7: len 8; hex 8000124a5d89b1f5; asc J] ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 80000000; asc ;; 10: len 1; hex 31; asc 1;; 11: len 4; hex 80000000; asc ;; 12: len 4; hex 80000000; asc ;; 13: len 4; hex 80000000; asc ;; 14: len 8; hex 8000124a5d97497b; asc J] I{;; 15: SQL NULL; 16: SQL NULL; 17: len 8; hex 8000124a5d97497b; asc J] I{;; 18: len 8; hex 80000000000048d2; asc H ;; 19: len 4; hex 80000000; asc ;; 20: len 4; hex 80000001; asc ;; 21: len 4; hex 80000001; asc ;; 22: len 4; hex 80000001; asc ;; 23: len 4; hex 80000001; asc ;;
------------------
---TRANSACTION 0 1436822, ACTIVE 155 sec, OS thread id 3604
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, 2 row lock(s), undo log entries 2
MySQL thread id 5, query id 8813 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=1, fault_num=0, last_check_time='2011-06-07 07:57:07', last_downline_time='2011-06-07 07:55:28', last_notice_time=null, last_online_time='2011-06-06 18:48:21', online_duration=0 where id=3
shaoabramdy 2012-06-07
- 打赏
- 举报
[Quote=引用 4 楼 的回复:]
你这是锁等待超时，是不是每次一个事务中批量更新一组ID？
[/Quote]
使用的Innodb引擎，缺省的锁超时时间是50秒
你这是锁等待超时，是不是每次一个事务中批量更新一组ID？
[/Quote]
使用的Innodb引擎，缺省的锁超时时间是50秒
shaoabramdy 2012-06-07
- 打赏
- 举报
[Quote=引用 4 楼 的回复:]
你这是锁等待超时，是不是每次一个事务中批量更新一组ID？
[/Quote]
一个事务只根据ID更新一个，下位机心跳更新和上位机监测更新的Update语句相同，只是更新内容不一样。
你这是锁等待超时，是不是每次一个事务中批量更新一组ID？
[/Quote]
一个事务只根据ID更新一个，下位机心跳更新和上位机监测更新的Update语句相同，只是更新内容不一样。
yumenfeiyu945 2012-06-06
- 打赏
- 举报
你这是锁等待超时，是不是每次一个事务中批量更新一组ID？
shaoabramdy 2012-06-06
- 打赏
- 举报
[Quote=引用 1 楼 的回复:]
show innodb status看下 是哪两条sql死锁了
[/Quote]
两条update语句都有可能出现死锁现象，下位机的心跳update语句死锁更频繁些
show innodb status看下 是哪两条sql死锁了
[/Quote]
两条update语句都有可能出现死锁现象，下位机的心跳update语句死锁更频繁些
shaoabramdy 2012-06-06
- 打赏
- 举报
[Quote=引用 1 楼 的回复:]
show innodb status看下 是哪两条sql死锁了
[/Quote]
UPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=1
对id为1的itemgroup更新死锁
show innodb status看下 是哪两条sql死锁了
[/Quote]
UPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=1
对id为1的itemgroup更新死锁
rucypli 2012-06-06
- 打赏
- 举报
show innodb status看下 是哪两条sql死锁了
