Dropwizard+JDBI3操作MySQL批量更新用户设置时的事务死锁问题求助

我现在在用Dropwizard搭配JDBI3来和MySQL数据库交互。为了减少流量、让API更好用，我做了一个接口，它接收一个设置Map，然后在事务里把数据库里的用户设置更新成对应的样子。但问题是，当这个接口被同时调用的时候，有时候第一个请求会先查询并锁定当前设置，然后开始更新，但会被第二个请求持有的等待锁给阻塞，最后就出现死锁了。

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,
    )

}

数据库配置 #

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)

只要3个并发请求就可能触发死锁，如果用更宽松的锁机制，需要更多请求才会出现。我试过用无锁查询，把事务隔离级别改成read-committed，这样死锁概率低很多，但会出现不符合预期的行为。我也试过直接用控制台执行查询来复现，但没成功，估计是需要刚好的时机。还试过不用@Transaction注解，手动调用START TRANSACTION和COMMIT，结果和用注解的情况差不多。

也许这种接口本来就不该被同时调用同一个id，但如果能有办法避免死锁就更好了，求助各位大佬！

备注：内容来源于stack exchange，提问作者Terkel Laursen