首页 注册 登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
• 请不要在回答技术问题时复制粘贴 AI 生成的内容
jiobanma
V2EX  ›  程序员

[求助大佬] mybatis 批量更新产生死锁的问题

  •   
  •   jiobanma ·
    banmajio · 2022-05-10 16:10:58 +08:00 · 2099 次点击
    这是一个创建于 946 天前的主题，其中的信息可能已经有所发展或是发生改变。

    一个接口进行数据批量更新，报了私锁，请大佬帮忙分析一下原因 [相关数据进行的脱敏，希望不影响大佬阅读] 。

    报错信息如下

    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

    描述一下情况：

    • 数据库: mysql 5.7.18-log 数据库隔离级别：READ-COMMITTED 数据库引擎：InnoDB
    • 业务场景： 一个接口进行数据处理涉及到多个查询，然后将数据处理封装到两个 List 中，判断第一个 list 不为空，则将 listA 批量插入数据库中，接着判断 listB 不为空，批量更新数据库中。 接口上加了 mybatisplus 的事务注解 @DSTransactional 。偶发现象会出现上述死锁报错。

    表中没有其他索引只有主键，表结构如下：

    +-------------------+--------------+------+-----+---------+----------------+
| 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    |                |
+-------------------+--------------+------+-----+---------+----------------+

    show engine innodb status 日志如下

    [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)
  • len
  • asc
  • HEX
  • lock
    14 条回复  •  2022-05-11 09:06:46 +08:00
    jiobanma
        1
    jiobanma  
    OP
       2022-05-10 16:11:24 +08:00
    - 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>
    ```
    ration
        2
    ration  
       2022-05-10 16:18:05 +08:00 via Android
    where 条件怎么那么多，能不能 update by id
    jiobanma
        3
    jiobanma  
    OP
       2022-05-10 16:20:34 +08:00
    @ration 业务问题导致 update 的数据不是查询出来的 而是代码组装出来的 dto 所以没法用 id
    zmal
        4
    zmal  
       2022-05-10 16:41:03 +08:00
    你这样 update...where 不加索引的字段会锁表的，让老鸟看到该挨揍了。
    zydxn
        5
    zydxn  
       2022-05-10 16:41:36 +08:00
    给你 where 条件中的字段建索引
    jiobanma
        6
    jiobanma  
    OP
       2022-05-10 16:42:55 +08:00
    @zydxn
    @zmal
    啊 这样吗（ dog ） 我试试 谢谢大佬们
    jiobanma
        7
    jiobanma  
    OP
       2022-05-10 16:45:31 +08:00
    @zydxn
    @zmal
    [菜鸟提问]
    1. 如果我给 where 条件的这些字段加一个联合索引是不是就可以避免该问题的产生？
    2. 一般建议是怎么加索引会好点？
    3. 是 update 和 delete 会发生这种现象 还是 insert 也会发生这个现象？
    谢大佬赐教！
    jiobanma
        8
    jiobanma  
    OP
       2022-05-10 17:11:12 +08:00
    忽略第三点
    zmal
        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 。
    zzzzzzzzzy
        10
    zzzzzzzzzy  
       2022-05-10 17:21:08 +08:00   ❤️ 1
    InnoDB 的锁是加到索引上的，如果没命中索引，就是表锁
    billlee
        11
    billlee  
       2022-05-10 17:24:33 +08:00 via Android
    Mysql 查询引擎和存储引擎分层，如果过滤条件没有索引无法下推到存储引擎执行，存储引擎就会把全部数据锁上返回给查询引擎
    jiobanma
        12
    jiobanma  
    OP
       2022-05-10 17:25:24 +08:00
    @zmal 谢谢大佬 我多去了解一下 万分感谢
    @zzzzzzzzzy 谢谢大佬
    Granado
        13
    Granado  
       2022-05-10 22:46:50 +08:00
    啊，这难道不是 mysql 的问题吗，关 Mybatis 啥事
    chengyiqun
        14
    chengyiqun  
       2022-05-11 09:06:46 +08:00
    能不能先查出主键?
    我们公司都强制要求, 更新必须有主键和分片键.
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4465 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 09:46 · PVG 17:46 · LAX 01:46 · JFK 04:46
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.