Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | int(5) | NO | PRI | NULL | auto_increment |
| week | varchar(20) | YES | | NULL | |
| starttime | datetime | YES | | NULL | |
| endtime | datetime | YES | | NULL | |
| teacher_code | varchar(50) | YES | | NULL | |
| teacher_name | varchar(50) | YES | | NULL | |
| class_code | varchar(50) | YES | | NULL | |
| student_code | varchar(50) | YES | | NULL | |
| student_name | varchar(50) | YES | | NULL | |
| submit_count | int(4) | YES | | NULL | |
| must_submit_week | int(4) | YES | | NULL | |
| submit_week | int(4) | YES | | NULL | |
| correct_count | int(4) | YES | | NULL | |
| must_correct_week | int(4) | YES | | NULL | |
| correct_week | int(4) | YES | | NULL | |
| course_type | varchar(20) | YES | | NULL | |
| product_type | varchar(20) | YES | | NULL | |
| del_flag | char(1) | YES | | 0 | |
| submit_ids | varchar(255) | YES | | NULL | |
| correct_ids | varchar(255) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
[root@localhost][dbxxx]> show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-05-10 14:11:40 0x7fa3fc225700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD // 线程
-----------------
srv_master_thread loops: 17165142 srv_active, 0 srv_shutdown, 22607227 srv_idle
srv_master_thread log flush and writes: 39771441
----------
SEMAPHORES
----------------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-07 00:09:55 0x7fa418034700
*** (1) TRANSACTION:
TRANSACTION 3345595400, ACTIVE 611 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 495 lock struct(s), heap size 73936, 210 row lock(s)
MySQL thread id 7469389, OS thread handle 140339391547136, query id 11284706720 172.20.xx.xx xxxx updating
UPDATE tablexxx
set submit_count = 0,
must_submit_week = 1,
submit_week = 0,
correct_count = 0,
must_correct_week = 1,
correct_week = 0
WHERE
class_code = 'xxxx'
AND teacher_code = 'xxxx'
AND course_type = 'xxxx'
AND student_code = 'xxxxx'
AND WEEK = 'xxx'
AND del_flag = 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 999 page no 526 n bits 192 index PRIMARY of table `dbxxx`.`tablexxx` trx id 3345595400 lock_mode X locks rec but not gap waiting
Record lock, heap no 111 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 8000cdc4; asc ;;
1: len 6; hex 0000c6e0a065; asc e;;
2: len 7; hex cf000000c10110; asc ;;
3: len 7; hex 323032322d3139; asc xxx;;
4: len 5; hex 99acc40000; asc ;;
5: len 5; hex 99acd17efb; asc ~ ;;
6: len 6; hex 544337353132; asc xxx;;
7: len 9; hex e983ade5ae9de5a9b7; asc ;;
8: len 11; hex 5456473231303239305a42; asc xxx;;
9: len 12; hex 424a30373231393833353835; asc xxx;;
10: len 6; hex e9988ee6b69b; asc ;;
11: len 4; hex 80000000; asc ;;
12: len 4; hex 80000001; asc ;;
13: len 4; hex 80000000; asc ;;
14: len 4; hex 80000000; asc ;;
15: len 4; hex 80000001; asc ;;
16: len 4; hex 80000000; asc ;;
17: len 6; hex e58699e4bd9c; asc ;;
18: len 1; hex 37; asc 7;;
19: len 1; hex 30; asc 0;;
20: SQL NULL;
21: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 3345595384, ACTIVE 431 sec fetching rows, thread declared inside InnoDB 4580
mysql tables in use 1, locked 1
509 lock struct(s), heap size 73936, 223 row lock(s)
MySQL thread id 7469757, OS thread handle 140342754232064, query id 11284746544 172.20.xx.xx xxxx updating
UPDATE tablexxx
set submit_count = 0,
must_submit_week = 1,
submit_week = 0,
correct_count = 0,
must_correct_week = 1,
correct_week = 0
WHERE
class_code = 'xxx'
AND teacher_code = 'xxx'
AND course_type = 'xxx'
AND student_code = 'xxx'
AND WEEK = 'xxx'
AND del_flag = 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 999 page no 526 n bits 192 index PRIMARY of table `dbxxx`.`tablexxx` trx id 3345595384 lock_mode X locks rec but not gap
Record lock, heap no 111 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 8000cdc4; asc ;;
1: len 6; hex 0000c6e0a065; asc e;;
2: len 7; hex cf000000c10110; asc ;;
3: len 7; hex 323032322d3139; asc xxx;;
4: len 5; hex 99acc40000; asc ;;
5: len 5; hex 99acd17efb; asc ~ ;;
6: len 6; hex 544337353132; asc xxx;;
7: len 9; hex e983ade5ae9de5a9b7; asc ;;
8: len 11; hex 5456473231303239305a42; asc xxx;;
9: len 12; hex 424a30373231393833353835; asc xxx;;
10: len 6; hex e9988ee6b69b; asc ;;
11: len 4; hex 80000000; asc ;;
12: len 4; hex 80000001; asc ;;
13: len 4; hex 80000000; asc ;;
14: len 4; hex 80000000; asc ;;
15: len 4; hex 80000001; asc ;;
16: len 4; hex 80000000; asc ;;
17: len 6; hex e58699e4bd9c; asc ;;
18: len 1; hex 37; asc 7;;
19: len 1; hex 30; asc 0;;
20: SQL NULL;
21: SQL NULL;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 999 page no 8 n bits 192 index PRIMARY of table `dbxxx`.`tablexxx` trx id 3345595384 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
0: len 4; hex 800001a5; asc ;;
1: len 6; hex 0000b31c4ed9; asc N ;;
2: len 7; hex f80000002b1788; asc + ;;
3: len 7; hex 323032312d3439; asc xxx;;
4: len 5; hex 99ab3a0000; asc : ;;
5: len 5; hex 99ab4b7efb; asc K~ ;;
6: len 6; hex 544337353132; asc xxx;;
7: len 9; hex e983ade5ae9de5a9b7; asc ;;
8: len 9; hex 545647313930383938; asc xxx;;
9: len 9; hex 424a32363438373238; asc xxx;;
10: len 9; hex e78e8be790aee79086; asc ;;
11: len 4; hex 80000000; asc ;;
12: len 4; hex 80000001; asc ;;
13: len 4; hex 80000000; asc ;;
14: len 4; hex 80000000; asc ;;
15: len 4; hex 80000001; asc ;;
16: len 4; hex 80000000; asc ;;
17: len 6; hex e58699e4bd9c; asc ;;
18: len 1; hex 36; asc 6;;
19: len 1; hex 30; asc 0;;
20: SQL NULL;
21: SQL NULL;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 3352085862
Purge done for trx's n:o < 3352085850 undo n:o < 0 state: running but idle
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421822933605552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933600080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933601904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933603728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933610112, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933600992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933606464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933602816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933612848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933615584, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933613760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933611024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933607376, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933609200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933608288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933604640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933624704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933623792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933621056, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933619232, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933618320, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933620144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933616496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421822933611936, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
4903747157 OS file reads, 474193111 OS file writes, 114973914 OS fsyncs
1.75 reads/s, 16384 avg bytes/read, 11.00 writes/s, 4.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 22214, seg size 22216, 22404084 merges
merged operations:
insert 23502702, delete mark 440515459, delete 8750852
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1182691, node heap has 5844 buffer(s)
Hash table size 1182691, node heap has 12123 buffer(s)
Hash table size 1182691, node heap has 974 buffer(s)
Hash table size 1182691, node heap has 7720 buffer(s)
Hash table size 1182691, node heap has 378 buffer(s)
Hash table size 1182691, node heap has 5112 buffer(s)
Hash table size 1182691, node heap has 581 buffer(s)
Hash table size 1182691, node heap has 647 buffer(s)
88752.06 hash searches/s, 24036.99 non-hash searches/s
---
LOG
---
Log sequence number 3671867146213
Log flushed up to 3671867146213
Pages flushed up to 3671867146213
Last checkpoint at 3671867146204
0 pending log flushes, 0 pending chkp writes
192625852 log i/o's done, 1.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744
Dictionary memory allocated 11514553
Buffer pool size 262112
Free buffers 8187
Database pages 220546
Old database pages 81252
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11524517817, not young 340158055943
3.25 youngs/s, 58.99 non-youngs/s
Pages read 4903764741, created 34663489, written 248270738
1.75 reads/s, 0.00 creates/s, 8.75 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 220546, unzip_LRU len: 0
I/O sum[7648]:cur[8], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 32764
Free buffers 1025
Database pages 27565
Old database pages 10155
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1425158825, not young 43137986481
0.25 youngs/s, 0.25 non-youngs/s
Pages read 641605377, created 4196010, written 28486598
0.25 reads/s, 0.00 creates/s, 0.25 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27565, unzip_LRU len: 0
I/O sum[956]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 32764
Free buffers 1023
Database pages 27598
Old database pages 10168
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1552370855, not young 44655684048
0.00 youngs/s, 0.25 non-youngs/s
Pages read 628275569, created 4323036, written 38427682
0.25 reads/s, 0.00 creates/s, 0.25 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27598, unzip_LRU len: 0
I/O sum[956]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 32764
Free buffers 1023
Database pages 27552
Old database pages 10151
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1452243781, not young 42628722057
0.25 youngs/s, 42.24 non-youngs/s
Pages read 621913386, created 4215051, written 33149846
0.25 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27552, unzip_LRU len: 0
I/O sum[956]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 32764
Free buffers 1024
Database pages 27571
Old database pages 10157
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1651174925, not young 45067265288
0.25 youngs/s, 0.00 non-youngs/s
Pages read 666954528, created 4224720, written 32544080
0.00 reads/s, 0.00 creates/s, 0.75 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27571, unzip_LRU len: 0
I/O sum[956]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 32764
Free buffers 1024
Database pages 27583
Old database pages 10162
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1263426020, not young 40077761530
0.75 youngs/s, 0.00 non-youngs/s
Pages read 603265612, created 4210869, written 23149366
0.00 reads/s, 0.00 creates/s, 0.50 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27583, unzip_LRU len: 0
I/O sum[956]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 32764
Free buffers 1024
Database pages 27593
Old database pages 10165
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1325959984, not young 37692529905
0.25 youngs/s, 0.00 non-youngs/s
Pages read 571222514, created 4226953, written 23782294
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27593, unzip_LRU len: 0
I/O sum[956]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 32764
Free buffers 1022
Database pages 27544
Old database pages 10148
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1483825350, not young 47643435900
0.50 youngs/s, 14.00 non-youngs/s
Pages read 609534010, created 4996069, written 46716730
0.50 reads/s, 0.00 creates/s, 6.75 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27544, unzip_LRU len: 0
I/O sum[956]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 32764
Free buffers 1022
Database pages 27540
Old database pages 10146
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1370358077, not young 39254670734
1.00 youngs/s, 2.25 non-youngs/s
Pages read 560993745, created 4270781, written 22014142
0.50 reads/s, 0.00 creates/s, 0.25 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27540, unzip_LRU len: 0
I/O sum[956]:cur[1], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
2 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Process ID=36991, Main thread ID=140343178970880, state: sleeping
Number of rows inserted 5332900270, updated 184966046, deleted 172974588, read 20144466876288
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1071910.27 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
1
jiobanma OP - mapper.xml
```xml <update id="updateTablexxx"> <foreach collection="aas" item="aa" index="index" open="" close="" separator=";"> UPDATE tablexxx <trim prefix="set" suffixOverrides=","> <if test="aa.submitCount != null"> submit_count = #{aa.submitCount}, </if> <if test="aa.mustSubmitWeek != null"> must_submit_week = #{aa.mustSubmitWeek}, </if> <if test="aa.submitWeek != null"> submit_week = #{aa.submitWeek}, </if> <if test="aa.correctCount != null"> correct_count = #{aa.correctCount}, </if> <if test="aa.mustCorrectWeek != null"> must_correct_week = #{aa.mustCorrectWeek}, </if> <if test="aa.correctWeek != null"> correct_week = #{aa.correctWeek} </if> </trim> WHERE class_code = #{aa.classCode} AND teacher_code = #{aa.teacherCode} AND course_type = #{aa.courseType} AND student_code = #{aa.studentCode} AND WEEK = #{aa.week} AND del_flag = 0 </foreach> </update> ``` |
2
ration 2022-05-10 16:18:05 +08:00 via Android
where 条件怎么那么多,能不能 update by id
|
4
zmal 2022-05-10 16:41:03 +08:00
你这样 update...where 不加索引的字段会锁表的,让老鸟看到该挨揍了。
|
5
zydxn 2022-05-10 16:41:36 +08:00
给你 where 条件中的字段建索引
|
7
jiobanma OP |
8
jiobanma OP 忽略第三点
|
9
zmal 2022-05-10 17:17:16 +08:00
你对这个场景的解决方案,问题太多了。
首先,RC 的隔离级别,即使加了索引和事务,也只有行锁没有间隙锁,你在 update...where ( A && B && C )还是会受到另一个事务的影响,极有可能导致更新错误。 其次,从根源上来说上层代码逻辑本身就有问题,不太合理。 如果你只是想解决死锁问题,加索引即可。索引怎么设计取决于你 where 条件的字段的区分度和使用概率。但这样做不解决 RC 隔离级别下的不可重复读问题。但也有可能你的业务场景和并发度不会遇到这个问题。 最好不要 update...where id 或唯一索引以外的字段,可以先 select id where..,再 update...where id 。 |
10
zzzzzzzzzy 2022-05-10 17:21:08 +08:00 1
InnoDB 的锁是加到索引上的,如果没命中索引,就是表锁
|
11
billlee 2022-05-10 17:24:33 +08:00 via Android
Mysql 查询引擎和存储引擎分层,如果过滤条件没有索引无法下推到存储引擎执行,存储引擎就会把全部数据锁上返回给查询引擎
|
12
jiobanma OP |
13
Granado 2022-05-10 22:46:50 +08:00
啊,这难道不是 mysql 的问题吗,关 Mybatis 啥事
|
14
chengyiqun 2022-05-11 09:06:46 +08:00
能不能先查出主键?
我们公司都强制要求, 更新必须有主键和分片键. |