简单更新已锁定

9rbhqvlz  于 2021-06-30  发布在  Java
关注(0)|答案(1)|浏览(314)

我们有一个非常简单的innodb数据库。只是一堆带外键的表，没有触发器之类的。
我们正在使用tomcat、spring和hibernate编写java应用程序。
当我们在这个表中执行非常简单的操作导致简单的更新时，更新会被卡住（锁定），所有其他请求都会因为这些锁定而超时。mysql的确切错误是： Lock wait timeout exceeded; try restarting transaction .
在mysql中，每个事务中总是有两个进程与这个锁相关：一个是 Updating ，另一个是 Sleeping . 我们觉得这有点奇怪，也许他们陷入僵局了？为什么？
当我们停止我们的应用程序，手动运行更新，它总是工作。所以这张table可能还可以。
hibernate是为autocommit事务配置的，因此在我们的应用程序中没有事务处理，所有事务都由hibernate处理。
我们尝试过：
更新hibernate
更新mysql（5.1到5.5）
正在将mysql配置更改为此。 SHOW INNODB STAUS :

=====================================
150113 16:01:57 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 42 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 439, signal count 426
Mutex spin waits 0, rounds 5145, OS waits 236
RW-shared spins 368, OS waits 184; RW-excl spins 18, OS waits 16
------------
TRANSACTIONS
------------
Trx id counter 0 15578322
Purge done for trx's n:o < 0 15577923 undo n:o < 0 0
History list length 9
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 1599, OS thread id 140351479547648
MySQL thread id 1150, query id 15431 localhost 127.0.0.1 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 15578321, not started, process no 1599, OS thread id 140351478748928
MySQL thread id 1149, query id 15343 localhost redmine_admin
---TRANSACTION 0 15578063, not started, process no 1599, OS thread id 140349944846080
MySQL thread id 879, query id 15207 localhost 127.0.0.1 root
---TRANSACTION 0 0, not started, process no 1599, OS thread id 140349945112320
MySQL thread id 878, query id 15207 localhost 127.0.0.1 root
---TRANSACTION 0 15578218, ACTIVE 318 sec, process no 1599, OS thread id 140351479281408 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s)
MySQL thread id 1121, query id 15351 localhost 127.0.0.1 root Updating
update anna.device set form_factor_id=null, device_level_id=1, connectivity_type_id=1, locale_id=211, os_id=21, app_version_id=1, family_account_id=68, device_role_id=3, device_type_id=14, platform_id=2, language_id=26, unit_system_id=1, timezone_id=7, screen_resolution_id=6, country_id=193, name='Martin\'s iPhone', active=1, running=0, time_created='2015-01-12 17:33:35', time_deactivated=null, device_uuid='4D420BD6-478F-47D0-A652-E746A912AE6F', ip4_wan=_binary'Q\'�', ip4_lan=_binary'\n\0�', ip6_wan=null, ip6_lan=null, has_camera=1, on_battery=null, battery_level=null, light_level=null, temp
Trx read view will not see trx with id >= 0 15578219, sees < 0 15577724
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 32770 n bits 224 index `PRIMARY` of table `anna`.`device` trx id 0 15578218 lock_mode X locks rec but not gap waiting
Record lock, heap no 156 PHYSICAL RECORD: n_fields 41; compact format; info bits 0
 0: len 4; hex 000001cb; asc     ;; 1: len 6; hex 000000edb09b; asc       ;; 2: len 7; hex 00000080191c23; asc       #;; 3: len 15; hex 4d617274696e2773206950686f6e65; asc Martin's iPhone;; 4: len 1; hex 81; asc  ;; 5: len 1; hex 80; asc  ;; 6: len 4; hex 54b3f75f; asc T  _;; 7: SQL NULL; 8: len 30; hex 34443432304244362d343738462d343744302d413635322d453734364139; asc 4D420BD6-478F-47D0-A652-E746A9;...(truncated); 9: len 4; hex 511f27b9; asc Q ' ;; 10: len 4; hex 0a0200b1; asc     ;; 11: SQL NULL; 12: SQL NULL; 13: len 4; hex 00000044; asc    D;; 14: len 1; hex 02; asc  ;; 15: len 2; hex 0001; asc   ;; 16: len 1; hex 01; asc  ;; 17: len 2; hex 0015; asc   ;; 18: SQL NULL; 19: len 2; hex 000e; asc   ;; 20: len 4; hex 00000003; asc     ;; 21: len 2; hex 001a; asc   ;; 22: len 1; hex 07; asc  ;; 23: len 2; hex 00c1; asc   ;; 24: len 2; hex 00d3; asc   ;; 25: len 1; hex 01; asc  ;; 26: len 2; hex 0006; asc   ;; 27: len 1; hex 81; asc  ;; 28: SQL NULL; 29: SQL NULL; 30: len 2; hex 0001; asc   ;; 31: SQL NULL; 32: SQL NULL; 33: SQL NULL; 34: SQL NULL; 35: SQL NULL; 36: SQL NULL; 37: len 1; hex 80; asc  ;; 38: len 4; hex 54b52644; asc T &D;; 39: len 4; hex 0000e306; asc     ;; 40: len 1; hex 81; asc  ;;

------------------
---TRANSACTION 0 15578064, ACTIVE 566 sec, process no 1599, OS thread id 140351558203136
2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 46
MySQL thread id 1091, query id 15391 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 15578065, sees < 0 15577724
---TRANSACTION 0 15577880, ACTIVE 1057 sec, process no 1599, OS thread id 140351558469376 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 3024, 7 row lock(s), undo log entries 6
MySQL thread id 927, query id 15347 localhost 127.0.0.1 root Updating
update anna.server set server_type_id=2, region_id=2, timezone_id=1, country_id=56, name='rdev', cpu_load=4, ip4=_binary'M]�', ip6=null, relay_countries='SK,CZ', cpu_score=862, tcp_port=0, rest_port=8443, udp_port=10002, config=null, tokens_active=0, memory=1822208, memory_free=464904, stack_trace_enabled=0, tokens_free=null, tokens_limit=500 where server_id=9
Trx read view will not see trx with id >= 0 15577881, sees < 0 15577724
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 29430 n bits 168 index `PRIMARY` of table `anna`.`server` trx id 0 15577880 lock_mode X locks rec but not gap waiting
Record lock, heap no 101 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 2; hex 0009; asc   ;; 1: len 6; hex 000000edb317; asc       ;; 2: len 7; hex 00000080192bd3; asc      + ;; 3: len 1; hex 82; asc  ;; 4: len 1; hex 81; asc  ;; 5: len 4; hex 72646576; asc rdev;; 6: len 4; hex 80000000; asc     ;; 7: len 4; hex 4d5dc81f; asc M]  ;; 8: SQL NULL; 9: len 1; hex 82; asc  ;; 10: len 5; hex 534b2c435a; asc SK,CZ;; 11: len 4; hex 8000035e; asc    ^;; 12: len 8; hex 8000000000000000; asc         ;; 13: len 8; hex 80000000000020fb; asc         ;; 14: len 8; hex 8000000000002712; asc       ' ;; 15: SQL NULL; 16: len 4; hex 80000000; asc     ;; 17: len 8; hex 80000000001bce00; asc         ;; 18: len 8; hex 800000000002783e; asc       x>;; 19: len 2; hex 0038; asc  8;; 20: len 1; hex 80; asc  ;; 21: SQL NULL; 22: len 4; hex 800001f4; asc     ;;

------------------
---TRANSACTION 0 15577879, ACTIVE 1058 sec, process no 1599, OS thread id 140351480346368
40 lock struct(s), heap size 6752, 19 row lock(s), undo log entries 26
MySQL thread id 926, query id 15217 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 15577880, sees < 0 15577724
---TRANSACTION 0 15577750, ACTIVE 1374 sec, process no 1599, OS thread id 140351482476288 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 15 lock struct(s), heap size 3024, 9 row lock(s), undo log entries 6
MySQL thread id 915, query id 15345 localhost 127.0.0.1 root Updating
update anna.server set server_type_id=2, region_id=2, timezone_id=1, country_id=56, name='rdev', cpu_load=2, ip4=_binary'M]�', ip6=null, relay_countries='SK,CZ', cpu_score=862, tcp_port=0, rest_port=8443, udp_port=10002, config=null, tokens_active=0, memory=1822208, memory_free=601266, stack_trace_enabled=0, tokens_free=null, tokens_limit=500 where server_id=9
Trx read view will not see trx with id >= 0 15577751, sees < 0 15577415
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 29430 n bits 168 index `PRIMARY` of table `anna`.`server` trx id 0 15577750 lock_mode X locks rec but not gap waiting
Record lock, heap no 101 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 2; hex 0009; asc   ;; 1: len 6; hex 000000edb317; asc       ;; 2: len 7; hex 00000080192bd3; asc      + ;; 3: len 1; hex 82; asc  ;; 4: len 1; hex 81; asc  ;; 5: len 4; hex 72646576; asc rdev;; 6: len 4; hex 80000000; asc     ;; 7: len 4; hex 4d5dc81f; asc M]  ;; 8: SQL NULL; 9: len 1; hex 82; asc  ;; 10: len 5; hex 534b2c435a; asc SK,CZ;; 11: len 4; hex 8000035e; asc    ^;; 12: len 8; hex 8000000000000000; asc         ;; 13: len 8; hex 80000000000020fb; asc         ;; 14: len 8; hex 8000000000002712; asc       ' ;; 15: SQL NULL; 16: len 4; hex 80000000; asc     ;; 17: len 8; hex 80000000001bce00; asc         ;; 18: len 8; hex 800000000002783e; asc       x>;; 19: len 2; hex 0038; asc  8;; 20: len 1; hex 80; asc  ;; 21: SQL NULL; 22: len 4; hex 800001f4; asc     ;;

------------------
---TRANSACTION 0 15577748, ACTIVE 1383 sec, process no 1599, OS thread id 140351479015168 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 35 lock struct(s), heap size 6752, 21 row lock(s), undo log entries 24
MySQL thread id 913, query id 15320 localhost 127.0.0.1 root Updating
update anna.server set server_type_id=2, region_id=2, timezone_id=1, country_id=56, name='rdev', cpu_load=2, ip4=_binary'M]�', ip6=null, relay_countries='SK,CZ', cpu_score=862, tcp_port=0, rest_port=8443, udp_port=10002, config=null, tokens_active=0, memory=1822208, memory_free=164184, stack_trace_enabled=0, tokens_free=null, tokens_limit=500 where server_id=9
Trx read view will not see trx with id >= 0 15577749, sees < 0 15577415
------- TRX HAS BEEN WAITING 27 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 29430 n bits 168 index `PRIMARY` of table `anna`.`server` trx id 0 15577748 lock_mode X locks rec but not gap waiting
Record lock, heap no 101 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 2; hex 0009; asc   ;; 1: len 6; hex 000000edb317; asc       ;; 2: len 7; hex 00000080192bd3; asc      + ;; 3: len 1; hex 82; asc  ;; 4: len 1; hex 81; asc  ;; 5: len 4; hex 72646576; asc rdev;; 6: len 4; hex 80000000; asc     ;; 7: len 4; hex 4d5dc81f; asc M]  ;; 8: SQL NULL; 9: len 1; hex 82; asc  ;; 10: len 5; hex 534b2c435a; asc SK,CZ;; 11: len 4; hex 8000035e; asc    ^;; 12: len 8; hex 8000000000000000; asc         ;; 13: len 8; hex 80000000000020fb; asc         ;; 14: len 8; hex 8000000000002712; asc       ' ;; 15: SQL NULL; 16: len 4; hex 80000000; asc     ;; 17: len 8; hex 80000000001bce00; asc         ;; 18: len 8; hex 800000000002783e; asc       x>;; 19: len 2; hex 0038; asc  8;; 20: len 1; hex 80; asc  ;; 21: SQL NULL; 22: len 4; hex 800001f4; asc     ;;

------------------
---TRANSACTION 0 15577746, ACTIVE 1437 sec, process no 1599, OS thread id 140351482210048
10 lock struct(s), heap size 3024, 13 row lock(s), undo log entries 16
MySQL thread id 911, query id 15219 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 15577747, sees < 0 15577415
---TRANSACTION 0 15577744, ACTIVE 1547 sec, process no 1599, OS thread id 140349945378560 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 15 lock struct(s), heap size 6752, 5 row lock(s), undo log entries 2
MySQL thread id 909, query id 15226 localhost 127.0.0.1 root Updating
update anna.server set server_type_id=2, region_id=2, timezone_id=1, country_id=56, name='rdev', cpu_load=1, ip4=_binary'M]�', ip6=null, relay_countries='SK,CZ', cpu_score=862, tcp_port=0, rest_port=8443, udp_port=10002, config=null, tokens_active=0, memory=1822208, memory_free=481920, stack_trace_enabled=0, tokens_free=null, tokens_limit=500 where server_id=9
Trx read view will not see trx with id >= 0 15577745, sees < 0 15577415
------- TRX HAS BEEN WAITING 37 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 29430 n bits 168 index `PRIMARY` of table `anna`.`server` trx id 0 15577744 lock_mode X locks rec but not gap waiting
Record lock, heap no 101 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 2; hex 0009; asc   ;; 1: len 6; hex 000000edb317; asc       ;; 2: len 7; hex 00000080192bd3; asc      + ;; 3: len 1; hex 82; asc  ;; 4: len 1; hex 81; asc  ;; 5: len 4; hex 72646576; asc rdev;; 6: len 4; hex 80000000; asc     ;; 7: len 4; hex 4d5dc81f; asc M]  ;; 8: SQL NULL; 9: len 1; hex 82; asc  ;; 10: len 5; hex 534b2c435a; asc SK,CZ;; 11: len 4; hex 8000035e; asc    ^;; 12: len 8; hex 8000000000000000; asc         ;; 13: len 8; hex 80000000000020fb; asc         ;; 14: len 8; hex 8000000000002712; asc       ' ;; 15: SQL NULL; 16: len 4; hex 80000000; asc     ;; 17: len 8; hex 80000000001bce00; asc         ;; 18: len 8; hex 800000000002783e; asc       x>;; 19: len 2; hex 0038; asc  8;; 20: len 1; hex 80; asc  ;; 21: SQL NULL; 22: len 4; hex 800001f4; asc     ;;

------------------
---TRANSACTION 0 15577730, ACTIVE 1557 sec, process no 1599, OS thread id 140351483541248
10 lock struct(s), heap size 3024, 10 row lock(s), undo log entries 13
MySQL thread id 895, query id 15184 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 15577731, sees < 0 15577415
---TRANSACTION 0 15577729, ACTIVE 1567 sec, process no 1599, OS thread id 140351559001856 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 3024, 7 row lock(s), undo log entries 3
MySQL thread id 894, query id 15224 localhost 127.0.0.1 root Updating
update anna.server set server_type_id=2, region_id=2, timezone_id=1, country_id=56, name='rdev', cpu_load=1, ip4=_binary'M]�', ip6=null, relay_countries='SK,CZ', cpu_score=862, tcp_port=0, rest_port=8443, udp_port=10002, config=null, tokens_active=0, memory=1822208, memory_free=492131, stack_trace_enabled=0, tokens_free=null, tokens_limit=500 where server_id=9
Trx read view will not see trx with id >= 0 15577730, sees < 0 15577415
------- TRX HAS BEEN WAITING 47 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 29430 n bits 168 index `PRIMARY` of table `anna`.`server` trx id 0 15577729 lock_mode X locks rec but not gap waiting
Record lock, heap no 101 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 2; hex 0009; asc   ;; 1: len 6; hex 000000edb317; asc       ;; 2: len 7; hex 00000080192bd3; asc      + ;; 3: len 1; hex 82; asc  ;; 4: len 1; hex 81; asc  ;; 5: len 4; hex 72646576; asc rdev;; 6: len 4; hex 80000000; asc     ;; 7: len 4; hex 4d5dc81f; asc M]  ;; 8: SQL NULL; 9: len 1; hex 82; asc  ;; 10: len 5; hex 534b2c435a; asc SK,CZ;; 11: len 4; hex 8000035e; asc    ^;; 12: len 8; hex 8000000000000000; asc         ;; 13: len 8; hex 80000000000020fb; asc         ;; 14: len 8; hex 8000000000002712; asc       ' ;; 15: SQL NULL; 16: len 4; hex 80000000; asc     ;; 17: len 8; hex 80000000001bce00; asc         ;; 18: len 8; hex 800000000002783e; asc       x>;; 19: len 2; hex 0038; asc  8;; 20: len 1; hex 80; asc  ;; 21: SQL NULL; 22: len 4; hex 800001f4; asc     ;;

------------------
---TRANSACTION 0 15577727, ACTIVE 1617 sec, process no 1599, OS thread id 140349944579840
10 lock struct(s), heap size 3024, 4 row lock(s), undo log entries 2
MySQL thread id 892, query id 15221 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 15577728, sees < 0 15577415
---TRANSACTION 0 15577724, ACTIVE 1648 sec, process no 1599, OS thread id 140351483275008
14 lock struct(s), heap size 6752, 11 row lock(s), undo log entries 34
MySQL thread id 889, query id 15430 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 0 15577725, sees < 0 15577415
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
786 OS file reads, 2712 OS file writes, 1137 OS fsyncs
0.07 reads/s, 16384 avg bytes/read, 0.05 writes/s, 0.05 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9, seg size 11,
428 inserts, 428 merged recs, 69 merges
Hash table size 2212699, node heap has 9 buffer(s)
5.00 hash searches/s, 15.43 non-hash searches/s
---
LOG
---
Log sequence number 0 1679880718
Log flushed up to   0 1679878430
Last checkpoint at  0 1679871205
0 pending log writes, 0 pending chkp writes
758 log i/o's done, 0.05 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1216262384; in additional pool allocated 1048576
Dictionary memory allocated 786512
Buffer pool size   65536
Free buffers       64458
Database pages     1069
Modified db pages  25
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1062, created 7, written 2508
0.07 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
13 read views open inside InnoDB
Main thread process no. 1599, id 140349909063424, state: sleeping
Number of rows inserted 438, updated 282, deleted 0, read 335130
0.48 inserts/s, 0.50 updates/s, 0.00 deletes/s, 337.68 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

保存对象（导致这些更新）的方法只是下面的代码，其余的是hibernate magic。

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

@Service
@Repository("dbHome")
public class DbHome {
    @Autowired(required=true)
    private SessionFactory sessionFactory;

    // Other CRUD methods are here

    public void saveObject(final Object object) {
        final Session session = sessionFactory.getCurrentSession();
        session.saveOrUpdate(object);
    }
}

休眠配置：

<?xml version="1.0"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory name="">
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/anna?zeroDateTimeBehavior=convertToNull&amp;useUnicode=true&amp;characterEncoding=UTF-8</property>
        <property name="hibernate.connection.username">xxxxx</property>
        <property name="hibernate.connection.password">xxxxx</property>
        <property name="hibernate.connection.schema">anna</property>
        <property name="hibernate.connection.autocommit">true</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
        <property name="hibernate.show_sql">false</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="hibernate.connection.CharSet">utf8</property>
        <property name="hibernate.connection.characterEncoding">utf8</property>
        <property name="hibernate.connection.useUnicode">true</property>
        <!-- Mappings -->
    </session-factory>
</hibernate-configuration>
JavamysqlspringinnodbHibernate

1条答案

按热度按时间
wwodge7n

wwodge7n1#

好的，多亏@m.deinum，我找到了解决办法。
首先，从hibernate配置中删除/注解以下行。

<property name="hibernate.current_session_context_class">thread</property>

然后，将以下行添加到spring上下文配置中。别忘了添加合适的名称空间，更多信息/例子在这里。

<tx:annotation-driven/>

还要确保你有 transactionManager 附近有豆子。
最后一步是添加 @Transactional 注解在后面（我阅读了它的导入以将其放置在后面） @Repository 注解。

赞(0）分享  回复(0）举报  2021-06-30
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com