mysql deadlock when update target table and many errorlog in edge and n9e log

[jesusys]

Relevant server.conf | webapi.conf

edge配置
[CenterApi]
Addrs = ["http://n9e.xxx.com"]
# unit: ms
Timeout = 15000

Relevant logs

n9e ERROR.log
2024-01-15 11:56:52.594526 ERROR ormx/ormx.go:66 ^[[31;1m/home/runner/work/nightingale/nightingale/pushgw/idents/idents.go:106 ^[[35;1mError 1213: Deadlock found when trying to get lock; try restarting transaction

System info

n9e 6.7.2,categraf .0.3.45,centos 7.9

Steps to reproduce

1.n9e-edge ERROR.log
2024-01-15 04:30:54.511912 ERROR idents/idents.go:74 failed to update targets: failed to fetch from url: Post "http:/n9e.xxx.com/v1/n9e/target-update": context deadline exceeded (Client.Timeout exceeded while awaiting headers)
2024-01-15 05:46:56.575646 ERROR idents/idents.go:74 failed to update targets: error from server: Error 1213: Deadlock found when trying to get lock; try restarting transaction
2024-01-15 11:20:25.582749 ERROR idents/idents.go:74 failed to update targets: unexpected status code: 502

n9e ERROR.log
2024-01-15 12:20:48.076133 ERROR ormx/ormx.go:66 ^[[31;1m/home/runner/work/nightingale/nightingale/pushgw/idents/idents.go:106 ^[[35;1mError 1213: Deadlock found when trying to get lock; try restarting transaction

mysql

*** (1) TRANSACTION:
TRANSACTION 6335184349, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1136, 34 row lock(s), undo log entries 16
MySQL thread id 387243063, OS thread handle 139780792985344, query id 1185588454150 xxx.xxx.xxx n9e updating
UPDATE target SET update_at=1705286361 WHERE ident in ('xxx.xxx.xxx','xxx.xxx.xxx',')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3701 page no 8 n bits 200 index PRIMARY of table n9e_v6.target trx id 6335184349 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
...

Expected behavior

不应该有死锁

Actual behavior

更新ident状态不应该有死锁

Additional info

No response

[UlricQin]

没遇到过，如何复现？

另外检查一下你的 mysql 的资源是否不够用了，比如 cpu、内存、磁盘、io、网络等，如果数据库资源不够用，可能会导致一个更新太慢，其他的就都等待

[nichtsen]

与该issue相同 1742
官方尚未解决
正在考虑提一个PR