性能问答>【赏金10元】Mysql在RC隔离级别下高并发/情况下的DEADLOCK问题>
12回复

【赏金10元】Mysql在RC隔离级别下高并发/情况下的DEADLOCK问题



环境:mysql5.7 READ-COMMITTED
操作:使用jemter批量压测接口
相关代码:
controller:

@RestController
@Slf4j
@RequestMapping("test")
public class TestController {

    @Resource
    private IStudentService studentService;

    @RequestMapping("test")
    public String test(){
        studentService.updateStudentBatch(null);
        return null;
    }
}
service:
public interface IStudentService {

    public void updateStudentBatch(List<Student> studentList);
}



@Service
public class StudentServiceImpl implements IStudentService {

    @Resource
    private StudentMapper studentMapper;

    @Override
    @Transactional(isolation = Isolation.READ_COMMITTED,rollbackFor = Exception.class)
    public void updateStudentBatch(List<Student> studentList) {
        System.out.println("测试");
        testUpdate();
    }

    private void testUpdate() {
        List<Student> studentList = new ArrayList<>();
        for (int i = 20; i < 28; i++) {
            Student student = new Student();
            student.setStudentName("test" + i);
            student.setStudentAge(i);
            studentList.add(student);
        }
        studentMapper.updateStudentBatch(studentList);
    }
}

mapper:

public interface StudentMapper {

    public int insertStudentHistory(@Param("ids") List<Long> ids);


    public List<Student> getStudentList(@Param("queryStudent") QueryStudent queryStudent);


    public void updateStudentBatch(@Param("studentList") List studentList);
}

mapper.xml

update id="updateStudentBatch" parameterType="list"
        foreach collection="studentList" index="index" open="" close="" item="item" separator=";
            update student
            set
                student_name = #{item.studentName}
            set
            where student_age = #{item.studentAge}
        foreach
/update

压测100并发出现死锁的问题
1655122874909.jpg
相关的表结构和idea插件打印的sql
1655122233671.jpg

具体的死锁日志

=====================================
2022-06-11 17:10:11 0x9c8 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 59 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 120 srv_active, 0 srv_shutdown, 457123 srv_idle
srv_master_thread log flush and writes: 457240
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 94
OS WAIT ARRAY INFO: signal count 92
RW-shared spins 0, rounds 160, OS waits 80
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 160.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-06-11 17:09:56 0x2b5c
*** (1) TRANSACTION:
TRANSACTION 1507872, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 65, OS thread handle 10100, query id 32546 localhost 127.0.0.1 root updating
update student
             SET student_name = 'test21' 
            where student_age = 21
         
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 3 n bits 176 index PRIMARY of table `mytest`.`student` trx id 1507872 lock_mode X locks rec but not gap waiting
Record lock, heap no 100 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 8; hex 8000000000000015; asc         ;;
 1: len 6; hex 00000016c766; asc      f;;
 2: len 7; hex 3a000001a20436; asc :     6;;
 3: len 6; hex 746573743231; asc test21;;
 4: len 4; hex 80000015; asc     ;;
 5: SQL NULL;
 6: len 5; hex 99abe6b335; asc     5;;
 7: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 1507871, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 66, OS thread handle 11100, query id 32547 localhost 127.0.0.1 root updating
update student
             SET student_name = 'test22' 
            where student_age = 22
         
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 114 page no 3 n bits 176 index PRIMARY of table `mytest`.`student` trx id 1507871 lock_mode X locks rec but not gap
Record lock, heap no 100 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 8; hex 8000000000000015; asc         ;;
 1: len 6; hex 00000016c766; asc      f;;
 2: len 7; hex 3a000001a20436; asc :     6;;
 3: len 6; hex 746573743231; asc test21;;
 4: len 4; hex 80000015; asc     ;;
 5: SQL NULL;
 6: len 5; hex 99abe6b335; asc     5;;
 7: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 3 n bits 176 index PRIMARY of table `mytest`.`student` trx id 1507871 lock_mode X locks rec but not gap waiting
Record lock, heap no 15 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 8; hex 800000000000000b; asc         ;;
 1: len 6; hex 00000016fff6; asc       ;;
 2: len 7; hex 60000001c40e5e; asc `     ^;;
 3: len 6; hex 746573743131; asc test11;;
 4: len 4; hex 8000004d; asc    M;;
 5: len 4; hex 31e78fad; asc 1   ;;
 6: len 5; hex 99916cf702; asc   l  ;;
 7: len 9; hex 800000000000640000; asc       d  ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 1507934
Purge done for trx's n:o < 1507334 undo n:o < 0 state: running but idle
History list length 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283317920201024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920200152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920199280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920198408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920196664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920192304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920197536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920193176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920195792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920194048, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283317920194920, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
866 OS file reads, 789 OS file writes, 378 OS fsyncs
0.05 reads/s, 16384 avg bytes/read, 0.90 writes/s, 0.76 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
0.00 hash searches/s, 0.10 non-hash searches/s
---
LOG
---
Log sequence number 223762258
Log flushed up to   223762258
Pages flushed up to 223762258
Last checkpoint at  223762249
0 pending log flushes, 0 pending chkp writes
247 log i/o's done, 0.49 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Dictionary memory allocated 214438
Buffer pool size   512
Free buffers       254
Database pages     256
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 550, created 60, written 481
0.05 reads/s, 0.00 creates/s, 0.31 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: 256, unzip_LRU len: 0
I/O sum[16]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=4480, Main thread ID=8644, state: sleeping
Number of rows inserted 3084, updated 26, deleted 0, read 226480
0.00 inserts/s, 0.05 updates/s, 0.00 deletes/s, 975.29 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

【问题赏金领取方式】

4220 阅读
请先登录,查看12条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步