有关 mysql 主键自增锁的问题应该如何解决？

[LeeReamond]

如题，最近发现好像 mysql，innodb 主键自增表当中，如果并发插入超过一定数量就会死锁，有什么办法可以避免这点吗？

[jj783850915]

锁交叉才会死锁 看你的描述应当不会出现这种情况

[3dwelcome]

我以前遇到过 Java 莫名锁死的问题，百思不得其解。

最后查下来是 OutOfMemory, 服务器上把几个数据库内存预分配调小后，就莫名消失了。

[zhangysh1995]

应该不会死锁，文档写的锁了要等待：

AUTO-INC Locks
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
The innodb_autoinc_lock_mode variable controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

可以发一下详细的死锁信息大家看看。

ps: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

[zhangysh1995]

另外，有个 mysql 专区