Hibernate+Mysql的锁死问题

数据库是MYSQL 5
Hibernate 3.2 使用JPA
多个线程并发更新一个表时发生锁死,异常提示是:
[code="java"]java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction[/code]

抛出异常后立即使用show innodb status得到结果:
[code="java"]------------------------

LATEST DETECTED DEADLOCK

080831 13:01:23
*** (1) TRANSACTION:
TRANSACTION 0 942395, ACTIVE 0 sec, OS thread id 9100 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1532, query id 1426491 Bearice-Gateway 192.168.0.2 root Updating
update IPRecord set closeTime='2008-08-31 13:01:23', inBytes=0, inPackets=0, openTime='2008-08-31 13:01:16', outBytes=0, outPackets=0, session_ID='bc6837d1-e308-4744-84b2-fc3be7325412', targetIP='192.168.28.1', targetMAC='00:00:00:00:00:00' where ID='c352891b-2785-4c23-ab75-a338f9506013'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 656 n bits 128 index PRIMARY of table ipc_db/iprecord trx id 0 942395 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 30; hex 63333532383931622d323738352d346332332d616237352d613333386639; asc c352891b-2785-4c23-ab75-a338f9;...(truncated); 1: len 6; hex 0000000e6142; asc aB;; 2: len 7; hex 000000029b2d19; asc - ;; 3: len 8; hex 800012436ece6a0b; asc Cn j ;; 4: len 8; hex 8000000000000000; asc ;; 5: len 8; hex 8000000000000000; asc ;; 6: len 8; hex 800012436ece6a04; asc Cn j ;; 7: len 8; hex 8000000000000000; asc ;; 8: len 8; hex 8000000000000000; asc ;; 9: len 12; hex 3139322e3136382e32382e31; asc 192.168.28.1;; 10: len 17; hex 30303a30303a30303a30303a30303a3030; asc 00:00:00:00:00:00;; 11: len 30; hex 62633638333764312d653330382d343734342d383462322d666333626537; asc bc6837d1-e308-4744-84b2-fc3be7;...(truncated);

*** (2) TRANSACTION:
TRANSACTION 0 942402, ACTIVE 0 sec, OS thread id 9428 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1522, query id 1426492 Bearice-Gateway 192.168.0.2 root Updating
update IPRecord set closeTime='2008-08-31 13:01:23', inBytes=0, inPackets=0, openTime='2008-08-31 13:01:16', outBytes=0, outPackets=0, session_ID='0f8afe72-43bb-4ce5-96d5-2a5ad41a0ee5', targetIP='192.168.5.1', targetMAC='00:00:00:00:00:00' where ID='cb4666bc-d3cc-4c12-9cbb-967e4fadd4fb'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 656 n bits 128 index PRIMARY of table ipc_db/iprecord trx id 0 942402 lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 30; hex 63333532383931622d323738352d346332332d616237352d613333386639; asc c352891b-2785-4c23-ab75-a338f9;...(truncated); 1: len 6; hex 0000000e6142; asc aB;; 2: len 7; hex 000000029b2d19; asc - ;; 3: len 8; hex 800012436ece6a0b; asc Cn j ;; 4: len 8; hex 8000000000000000; asc ;; 5: len 8; hex 8000000000000000; asc ;; 6: len 8; hex 800012436ece6a04; asc Cn j ;; 7: len 8; hex 8000000000000000; asc ;; 8: len 8; hex 8000000000000000; asc ;; 9: len 12; hex 3139322e3136382e32382e31; asc 192.168.28.1;; 10: len 17; hex 30303a30303a30303a30303a30303a3030; asc 00:00:00:00:00:00;; 11: len 30; hex 62633638333764312d653330382d343734342d383462322d666333626537; asc bc6837d1-e308-4744-84b2-fc3be7;...(truncated);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 656 n bits 128 index PRIMARY of table ipc_db/iprecord trx id 0 942402 lock_mode X locks rec but not gap waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 30; hex 63623436363662632d643363632d346331322d396362622d393637653466; asc cb4666bc-d3cc-4c12-9cbb-967e4f;...(truncated); 1: len 6; hex 0000000e613b; asc a;;; 2: len 7; hex 00000002990510; asc ;; 3: len 8; hex 800012436ece6a0b; asc Cn j ;; 4: len 8; hex 8000000000000000; asc ;; 5: len 8; hex 8000000000000000; asc ;; 6: len 8; hex 800012436ece6a04; asc Cn j ;; 7: len 8; hex 8000000000000000; asc ;; 8: len 8; hex 8000000000000000; asc ;; 9: len 11; hex 3139322e3136382e352e31; asc 192.168.5.1;; 10: len 17; hex 30303a30303a30303a30303a30303a3030; asc 00:00:00:00:00:00;; 11: len 30; hex 30663861666537322d343362622d346365352d393664352d326135616434; asc 0f8afe72-43bb-4ce5-96d5-2a5ad4;...(truncated);

*** WE ROLL BACK TRANSACTION (2)
[/code]
相关代码:
[code="java"] dao.beginTransaction();
try {
dao.merge(rec);
dao.commitTransaction();
} catch (RuntimeException e) {
dao.rollbackTransaction();
}[/code]
[b]问题补充:[/b]
问题解决了,打开了Hibernate的hibernate.order_inserts和hibernate.order_updates就很少出现这种问题了。

1个回答

好好优化SQL吧,不是Java代码的问题。

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!