12回复
2年前
【赏金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并发出现死锁的问题
相关的表结构和idea插件打印的sql
具体的死锁日志
=====================================
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
============================
5274 阅读
