Hibernate+Mysql的锁死问题

问题描述

数据库是MYSQL 5Hibernate 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 readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 1MySQL thread id 1532, query id 1426491 Bearice-Gateway 192.168.0.2 root Updatingupdate 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 waitingRecord 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 500mysql tables in use 1, locked 13 lock struct(s), heap size 320, undo log entries 1MySQL thread id 1522, query id 1426492 Bearice-Gateway 192.168.0.2 root Updatingupdate 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 gapRecord 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 waitingRecord 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就很少出现这种问题了。

解决方案

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

时间： 2024-08-01 07:14:37

Hibernate+Mysql的锁死问题的相关文章

图片存储-hibernate mysql blob类型 图文存储怎么解决

问题描述 hibernate mysql blob类型 图文存储怎么解决 我想做一个文章存取得功能,即一片文章的内容有文字和图片,均存在实类Article中的content中,在Java是String类型,在mysql中是blob类型. jsp页面有一个form表(含有编辑器)是用来发表文章的,怎么从form表中,取得content的参数,然后转化成blob类型存进数据库,又怎么从数据库取出在转化成String在页面上显示呢???求大神指教怎么存取成功? 新手一枚,请大家多多包涵! 解决方案 是

框架-hibernate mysql generationtype.auto 没有自增

问题描述 hibernate mysql generationtype.auto 没有自增 数据库是mysql,之前在实体类的主键上使用generationtype.auto主键策略 是可以自增的. 但是今天搭了一个新框架后不知道改了什么配置 文件,结果导致不在使用mysql自己的自增. 策略,而是单独生成了一张 hibernate_sequence 的表,里面只有一个next_val的字段. 但是却没有值,结果每次插入数据都提示错误"you need to populate the table

hibernate+mysql数据刷新的问题？

问题描述 我的hibernate配置如下<propertyname="connection.url">jdbc:mysql://192.168.10.195:3306/radius?useUnicode=true&characterEncoding=UTF-8</property><propertyname="dialect">org.hibernate.dialect.MySQLDialect</property&

hibernate mysql连接不上 java.sql.SQLException: Access denied

问题描述 之前用springdatasource注入连接mysql还能正常操作,用hibernate时驱动名之类配置的全一样,可是就是有问题.问题如下:...382[main]INFOorg.hibernate.connection.DriverManagerConnectionProvider-connectionproperties:{password=****}641[main]WARNorg.hibernate.cfg.SettingsFactory-Couldnotobtainconn

HIbernate + mysql 异常

问题描述 2008-11-07 08:42:56,285 ERROR [org.hibernate.util.JDBCExceptionReporter] - Table 'toempids0_' is marked as crashed and should be repaired2008-11-07 08:42:56,581 ERROR [dao.impl.ConsumerDaoImpl] - ConsumerDaoImpl login(Consumer consum)org.hiberna

解决hibernate+mysql写入数据库乱码_Mysql

hibernate.cfg.xml加上属性. <property name="connection.useUnicode">true</property> <property name="connection.characterEncoding">UTF-8</property> mysql 的驱动用3.0.15以上版本的, 加个Filter, 使用UTF-8字符集就可以了, 若使用Spring则写在spring中的s

解析Hibernate + MySQL中文乱码问题

如果是windows系统,那么系统默认的本地字符集是gb2312,为了让数据表也使用gb2312字符集,我们要这样编写创建数据表的SQL语句: 复制代码 代码如下: CREATE TABLE TEST {  ...  ...  ... }default character set gb2312; 然后在hibernate.cfg.xml中加上: 复制代码 代码如下: <property name="connection.characterEncoding">gb2312&l

hibernate MySQL日期映射问题

问题描述 我在MySQL表中有这样一个字段timestamp,让其在新增记录时自动生成系统当前时间,在model层JAVA中用来映射的类型是Timestamp,最后无法用hibernateTemplate.load方法取出数据表中的timestamp字段,请问大虾们是怎样处理时间类型的,在JAVA中用什么样的类型去映射MySQL中的timestamp类型可将其直接取出来? 解决方案 解决方案二:在数据库中timestamp可以设置成datatime或者varchar类型的,insert的值可以是

解析Hibernate + MySQL中文乱码问题_java

如果是windows系统,那么系统默认的本地字符集是gb2312,为了让数据表也使用gb2312字符集,我们要这样编写创建数据表的SQL语句: 复制代码 代码如下: CREATE TABLE TEST{ ... ... ...}default character set gb2312; 然后在hibernate.cfg.xml中加上: 复制代码 代码如下: <property name="connection.characterEncoding">gb2312</pr