Reputation: 1
Transactions holding a waiting lock resulting in deadlocks
I am using Dropwizard and including JDBI3 to handle communication with a MySQL database.
To minimize traffic and make the API easy to use I have included an endpoint that takes a Map of settings and makes the database settings look like that in a transaction. The problem is that when this endpoint is called simultaneously in some cases the first to reach gets to list and lock the current settings and then moves on to start updating, but gets blocked by the second call that is holding a waiting lock.
DAO
interface MysqlUserSettingsStore {
@Transaction
fun replace(id: Int, settings: Map<String, Any?>) {
val existing = listLocked(id)
// Update all settings
for ((key, value) in settings) {
if (existing[key] == value) {
// Skip unchanged setting
continue
}
replace(id, key, value)
}
// Remove existing settings not in the new settings
for ((key, _) in existing) {
if (settings[key] == null) {
delete(id, key)
}
}
}
@SqlQuery("""
SELECT *
FROM user_settings
WHERE id = :id
FOR UPDATE
""")
@KeyColumn("key")
@ValueColumn("value")
@RegisterColumnMapper(SettingValueMapper::class)
fun listLocked(
@Bind("id")
id: Int,
): Map<String, Any?>
@SqlUpdate("""
INSERT INTO user_settings
VALUES (:id, :key, :value)
ON DUPLICATE KEY UPDATE value = :value
""")
@RegisterArgumentFactory(ReplaceBooleanArgumentHandler::class)
fun replace(
@Bind("id")
id: Int,
@Bind("key")
key: String,
@Bind("value")
value: Any?,
)
@SqlUpdate("""
DELETE FROM user_settings
WHERE id = :id
AND `key` = :key
""")
fun delete(
@Bind("id")
id: Int,
@Bind("key")
key: String,
)
}
Config for the database
database:
driverClass: 'com.mysql.cj.jdbc.Driver'
user: 'root'
password: ''
url: 'jdbc:mysql://localhost:3306/<database>'
maxSize: 80
defaultTransactionIsolation: 'repeatable-read'
SHOW ENGINE INNODB STATUS;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-11-05 15:59:36 140331471333120
*** (1) TRANSACTION:
TRANSACTION 41960, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 2382, OS thread handle 140331686369024, query id 246406 172.17.0.1 root executing
/* MysqlUserSettingsStore.listLocked */
SELECT *
FROM user_settings
WHERE id = 300
FOR UPDATE
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 160 page no 7 n bits 216 index PRIMARY of table `propertree`.`user_settings` trx id 41960 lock_mode X waiting
Record lock, heap no 145 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000012c; asc ,;;
1: len 4; hex 6b657932; asc key2;;
2: len 6; hex 00000000a3e3; asc ;;
3: len 7; hex 82000000c10110; asc ;;
4: len 6; hex 76616c756532; asc value2;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 160 page no 7 n bits 216 index PRIMARY of table `propertree`.`user_settings` trx id 41960 lock_mode X waiting
Record lock, heap no 145 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000012c; asc ,;;
1: len 4; hex 6b657932; asc key2;;
2: len 6; hex 00000000a3e3; asc ;;
3: len 7; hex 82000000c10110; asc ;;
4: len 6; hex 76616c756532; asc value2;;
*** (2) TRANSACTION:
TRANSACTION 41959, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s)
MySQL thread id 2381, OS thread handle 140327490713344, query id 246414 172.17.0.1 root update
/* MysqlUserSettingsStore.replace */
INSERT INTO user_settings
VALUES (300, 'key1', 'value1')
ON DUPLICATE KEY UPDATE value = 'value1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 160 page no 7 n bits 216 index PRIMARY of table `propertree`.`user_settings` trx id 41959 lock_mode X
Record lock, heap no 145 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000012c; asc ,;;
1: len 4; hex 6b657932; asc key2;;
2: len 6; hex 00000000a3e3; asc ;;
3: len 7; hex 82000000c10110; asc ;;
4: len 6; hex 76616c756532; asc value2;;
Record lock, heap no 148 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000012c; asc ,;;
1: len 7; hex 746573744b6579; asc testKey;;
2: len 6; hex 00000000a363; asc c;;
3: len 7; hex 81000001540122; asc T ";;
4: len 9; hex 7465737456616c7565; asc testValue;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 160 page no 7 n bits 216 index PRIMARY of table `propertree`.`user_settings` trx id 41959 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 145 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 8000012c; asc ,;;
1: len 4; hex 6b657932; asc key2;;
2: len 6; hex 00000000a3e3; asc ;;
3: len 7; hex 82000000c10110; asc ;;
4: len 6; hex 76616c756532; asc value2;;
*** WE ROLL BACK TRANSACTION (1)
Deadlocks can happen with as few as 3 simultaneous calls but using less strict locking requires more.
I have tried using a non-locking list and defaultTransactionIsolation: read-committed both making the endpoint much less susceptible to deadlocks but also giving rise to unintended behavior. I have tried replicating it with queries directly with a console but was not able to replicate it. I am guessing the timing has to be fast enough for this behavior. I tried to not use the @Transaction annotation and calling START TRANSACTION and COMMIT instead but got similar behavior to the @Transaction annotation.
Maybe this is just a bug and endpoints like this should never be called simultaneously for the same id. But it would be nice if there was a good way to avoid deadlocks being able to happen.
Upvotes: 0
Views: 35