hibernate mysql 锁表_Hibernate+Mysql的锁死问题

数据库是MYSQL 5

Hibernate 3.2 使用JPA

多个线程并发更新一个表时发生锁死，异常提示是：

java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction

抛出异常后立即使用show innodb status得到结果：

------------------------

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)

相关代码：

dao.beginTransaction();

try {

dao.merge(rec);

dao.commitTransaction();

} catch (RuntimeException e) {

dao.rollbackTransaction();

}

问题补充：

问题解决了，打开了Hibernate的hibernate.order_inserts和hibernate.order_updates就很少出现这种问题了。