我们有一个数据库,其中并发的 kafka 流同时更新多行,我们看到死锁在极少数情况下发生,我不完全确定是什么原因造成的
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-05-24 16:05:22 0x2b84dd626700
*** (1) TRANSACTION:
TRANSACTION 52960655, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 115182, OS thread handle 47848750593792, query id 73757580 10.175.134.185 admin Searching rows for update
UPDATE my_model_v2 SET model = '{"id": {"qid": "14.98.50.42.JMS.h2.UNRESOLVED", "cid": "pi-232-snr", "aid": 2, "etype": 14, "td": 1}, "sliceId": 2000, "metadata": {"67744": {"lastUpdatedTime": 1684944319186, "trainingSamplesProcessed": 1818}, "67743": {"lastUpdatedTime": 1684944319186, "trainingSamplesProcessed": 1814}}, "slices": {"67744": {"avg": 2.136, "variance": 0.117, "count": 31}, "67743": {"avg": 0.295, "variance": 0.207, "count": 31}}}', updated_at = 1684944319187 WHERE q_id = '14.98.50.42.JMS.h2.UNRESOLVED' AND c_id = 'pi-232-snr' AND a_id = 2 AND e_type = 14 AND t_d = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1233 page no 30 n bits 0 index PRIMARY of table `models`.`my_model_v2` /* Partition `p1` */ trx id 52960655 lock_mode X waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len=29; bufptr=0x2b84b10ad041; hex= 31342e39382e35302e34322e4a4d532e68322e554e5245534f4c564544; asc 14.98.50.42.JMS.h2.UNRESOLVED;;
1: len=10; bufptr=0x2b84b10ad05e; hex= 70692d3233322d736e72; asc pi-232-snr;;
2: len=8; bufptr=0x2b84b10ad068; hex= 8000000000000002; asc ;;
3: len=4; bufptr=0x2b84b10ad070; hex= 8000000e; asc ;;
4: len=2; bufptr=0x2b84b10ad074; hex= 8001; asc ;;
5: len=8; bufptr=0x2b84b10ad076; hex= 800001884e4b0440; asc NK @;;
6: len=6; bufptr=0x2b84b10ad07e; hex= 00000327f15b; asc ' [;;
7: len=7; bufptr=0x2b84b10ad084; hex= ec000003bc0110; asc ;;
8: len=30; bufptr=0x2b84b10ad08b; hex= 000400e6012000020022000600280007002f00080000370000ba0005d007; asc " ( / 7 ; (total 487 bytes);
*** (2) TRANSACTION:
TRANSACTION 52960651, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 201 lock struct(s), heap size 1136, 102 row lock(s), undo log entries 1
MySQL thread id 115181, OS thread handle 47848635528960, query id 73757578 10.175.134.185 admin updating
UPDATE my_model_v2 SET model = '{"id": {"qid": "14.97.52.44.CUSTOM.h1.UNRESOLVED", "cid": "pi-232-snr", "aid": 2, "etype": 14, "td": 1}, "sliceId": 2000, "metadata": {"67799": {"lastUpdatedTime": 1684944319187, "trainingSamplesProcessed": 1819}, "67798": {"lastUpdatedTime": 1684944319187, "trainingSamplesProcessed": 1526}}, "slices": {"67799": {"avg": 3.996, "variance": 1.04, "count": 31}, "67798": {"avg": 0.0, "variance": 0.0, "count": 31}}}', updated_at = 1684944319187 WHERE q_id = '14.97.52.44.CUSTOM.h1.UNRESOLVED' AND c_id = 'pi-232-snr' AND a_id = 2 AND e_type = 14 AND t_d = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1233 page no 30 n bits 0 index PRIMARY of table `models`.`my_model_v2` /* Partition `p1` */ trx id 52960651 lock_mode X
Record lock, heap no 8 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
0: len=30; bufptr=0x2b84b10ace01; hex= 31342e39372e35322e34342e435553544f4d2e68312e554e5245534f4c56; asc 14.97.52.44.CUSTOM.h1.UNRESOLV; (total 32 bytes);
1: len=10; bufptr=0x2b84b10ace21; hex= 70692d3233322d736e72; asc pi-232-snr;;
2: len=8; bufptr=0x2b84b10ace2b; hex= 8000000000000002; asc ;;
3: len=4; bufptr=0x2b84b10ace33; hex= 8000000e; asc ;;
4: len=2; bufptr=0x2b84b10ace37; hex= 8001; asc ;;
5: len=8; bufptr=0x2b84b10ace39; hex= 800001884e4b0411; asc NK ;;
6: len=6; bufptr=0x2b84b10ace41; hex= 000003281d8b; asc ( ;;
7: len=7; bufptr=0x2b84b10ace47; hex= 6c00000ca5056b; asc l k;;
8: len=30; bufptr=0x2b84b10ace4e; hex= 000400e9012000020022000600280007002f00080000370000bd0005d007; asc " ( / 7 ; (total 490 bytes);
[bitmap of 256 bytes in hex: 00 03 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ]
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1233 page no 30 n bits 0 index PRIMARY of table `models`.`my_model_v2` /* Partition `p1` */ trx id 52960651 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len=29; bufptr=0x2b84b10ad041; hex= 31342e39382e35302e34322e4a4d532e68322e554e5245534f4c564544; asc 14.98.50.42.JMS.h2.UNRESOLVED;;
1: len=10; bufptr=0x2b84b10ad05e; hex= 70692d3233322d736e72; asc pi-232-snr;;
2: len=8; bufptr=0x2b84b10ad068; hex= 8000000000000002; asc ;;
3: len=4; bufptr=0x2b84b10ad070; hex= 8000000e; asc ;;
4: len=2; bufptr=0x2b84b10ad074; hex= 8001; asc ;;
5: len=8; bufptr=0x2b84b10ad076; hex= 800001884e4b0440; asc NK @;;
6: len=6; bufptr=0x2b84b10ad07e; hex= 00000327f15b; asc ' [;;
7: len=7; bufptr=0x2b84b10ad084; hex= ec000003bc0110; asc ;;
8: len=30; bufptr=0x2b84b10ad08b; hex= 000400e6012000020022000600280007002f00080000370000ba0005d007; asc " ( / 7 ; (total 487 bytes);
*** WE ROLL BACK TRANSACTION (1)
输出Show Create table
:
my_model_v2 | CREATE TABLE `my_model_v2` (
`q_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`c_id` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`a_id` bigint(20) NOT NULL,
`e_type` int(11) NOT NULL,
`t_d` smallint(6) NOT NULL,
`model` json NOT NULL,
`updated_at` bigint(20) NOT NULL,
PRIMARY KEY (`q_id`,`c_id`,`a_id`,`e_type`,`t_d`,`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY HASH (updated_at DIV (1000 * 60 * 60 * 24))
PARTITIONS 100 */ |
我们还有一个存储过程,每 24 小时触发一次以删除包含 15 天未更新数据的分区。
Java 代码工作流程是这样的:
try (Connection connection = writeDatasource.getConnection()) {
try(PreparedStatement preparedStatementUpdate =
connection.prepareStatement(UPDATE_MODEL_QUERY)) {
batchPreparedStatementForModelUpdate(dataModelList, preparedStatementUpdate);
// Execute batch statement to Update existing models.
updatedModelResults = preparedStatementUpdate.executeBatch();
// Filter out the models which are not updated
insertList =
IntStream.range(0, updatedModelResults.length)
.filter(i -> updatedModelResults[i] == 0)
.mapToObj(dataModelList::get)
.collect(Collectors.toList());
}
// Execute batch statement to store new models.
if(!insertList.isEmpty()) {
try (PreparedStatement preparedStatementInsert = connection.prepareStatement(INSERT_MODEL_QUERY)) {
batchPreparedStatementForModelInsert(indestModelObjectList, preparedStatementInsert);
preparedStatementInsert.executeBatch();
}
}
}
我应该在同一个函数中进行更新和插入,但为它们打开两个单独的连接吗?我不能将隔离级别降低到read_commited
,因为它可能会影响其他工作流程。
我注意到死锁只发生在更新行而不是插入时。