在高负载服务上，我定期看到数据库出现死锁

LATEST DETECTED DEADLOCK

2023-03-06 22:48:18 0x7fa6a24af700
*** (1) TRANSACTION:
TRANSACTION 209985062, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1134110, OS thread handle 140353648719616, query id 13926656547 localhost 127.0.0.1 userdb updating
update element set tags_names_string=‘XXXXX’, genres_names_string=‘XXXX’, description=‘YYYY’ where id=370443
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1313 page no 9937 n bits 120 index PRIMARY of table user.element trx id 209985062 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 209985053, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
551 lock struct(s), heap size 90320, 985 row lock(s), undo log entries 545
MySQL thread id 1134109, OS thread handle 140353664120576, query id 13926657948 localhost 127.0.0.1 userdb updating
update element set last_update=‘2023-03-06 22:48:15.912’ where id=370443
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1313 page no 9937 n bits 120 index PRIMARY of table user.element trx id 209985053 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1313 page no 9937 n bits 120 index PRIMARY of table user.element trx id 209985053 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
赏金将在 6 天后到期。此问题的答案有资格获得+200声誉奖励。 demo101想让更多人关注这个问题。
在高负载服务上，我定期看到数据库出现死锁

LATEST DETECTED DEADLOCK

2023-03-06 22:48:18 0x7fa6a24af700
*** (1) TRANSACTION:
TRANSACTION 209985062, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1134110, OS thread handle 140353648719616, query id 13926656547 localhost 127.0.0.1 userdb updating
update element set tags_names_string=‘XXXXX’, genres_names_string=‘XXXX’, description=‘YYYY’ where id=370443
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1313 page no 9937 n bits 120 index PRIMARY of table user.element trx id 209985062 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 209985053, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
551 lock struct(s), heap size 90320, 985 row lock(s), undo log entries 545
MySQL thread id 1134109, OS thread handle 140353664120576, query id 13926657948 localhost 127.0.0.1 userdb updating
update element set last_update=‘2023-03-06 22:48:15.912’ where id=370443
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1313 page no 9937 n bits 120 index PRIMARY of table user.element trx id 209985053 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1313 page no 9937 n bits 120 index PRIMARY of table user.element trx id 209985053 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
如果两个连续更新修改同一行，就会发生这种情况。每次更新都会通过一个查询触及一条记录，并按 pk 进行过滤

神奇的是我在 java 代码中同步了表更新。

我长期以来一直试图解决一个问题。因为重试没有丢失数据的情况，但好奇心不允许我离开这个问题
SpringSefviceClass (singleton):

def synchronized updateElement(ElementMessage msg) {
Element.withNewTransaction {
def e = Element.get(e.id)
e.xxx= msg.xxx
…
e.save(flush: true)
}
sleep(400)
}
添加 sleep 以确保所有数据库事务活动完成。这减少了错误的数量，但无论如何它都会发生。使用 sleep(10000) 日志中没有错误，但这对于服务来说太慢了。

根据我的理解，数据库事务必须在java代码中结束事务之前完成。在我的代码中，在睡眠行执行之前。但根据我看到的错误，即使在睡眠后它也没有完成！我确定，一次只有一个线程执行方法（已通过日志记录检查）
有什么优化方法吗？