使用IDENTITY列替代COALESCE(MAX(Id)+1)插入：性能提升与死锁减少吗？

当前方案的核心问题 #

你用COALESCE(MAX(Id)+1)生成主键的方式，本质上每次插入前都要全表扫描获取最大Id值，这会触发全表级别的锁（高并发下甚至会持有范围锁导致其他插入请求阻塞）。多个并发插入请求会因为争抢获取MAX(Id)的权限而排队，不仅插入性能拉胯，还很容易因为锁等待的顺序冲突引发死锁。

改用IDENTITY列的改善效果 #

完全可以解决你的问题，核心优势有这几点：

• 性能大幅提升：IDENTITY是数据库引擎内部维护的自增计数器，生成新主键值时不需要扫描全表，直接从内部计数器取值，开销极低，并发插入时多个请求可以并行处理。
• 锁范围大幅缩小：插入时只会对新插入的行加行级锁，不会触发全表锁，其他请求不会因为你的插入操作被阻塞。
• 死锁概率显著降低：死锁大多源于多个会话互相等待对方释放锁，IDENTITY的行级锁模式下，会话之间的锁冲突场景几乎消失，自然大幅减少死锁的发生。

补充说明 #

如果你的业务有特殊需求（比如需要自定义主键生成规则、主键值需要可手动修改），IDENTITY可能不完全适配，但单纯解决当前的锁、性能和死锁问题，IDENTITY是非常合适的替代方案。

内容的提问来源于stack exchange，提问作者Amy