MS-SQL与PostgreSQL在READ-COMMITTED隔离级别下的行为差异及阻塞排查

当逐行交替执行CASE:A与CASE:B（即执行一行CASE:A代码后执行一行CASE:B代码）时，MS-SQL的SELECT语句会挂起，但PostgreSQL可正常执行。

CASE:A #

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin transaction

    insert into mytable (id, name) values (98,'person-1')
    select * from mytable order by id

CASE:B #

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin transaction

    insert into mytable (id, name) values (99,'person-2')
    select * from mytable order by id

核心原因：两种数据库READ COMMITTED隔离级别的实现差异 #

MS-SQL（SQL Server）的锁机制导致阻塞

• SQL Server在READ COMMITTED默认模式下，读取数据时会申请共享锁（S锁），且锁会持有到当前语句执行结束。
• 当CASE:A执行INSERT后，会在id=98的行上持有排他锁（X锁）；CASE:B执行INSERT后，在id=99的行上持有X锁。
• 当CASE:A执行SELECT * FROM mytable ORDER BY id时，需要对全表所有行申请S锁，但CASE:B持有的X锁会阻止这个请求，导致SELECT挂起；同理CASE:B的SELECT也会被CASE:A的X锁阻塞，最终形成互相等待的局面，严重时触发死锁。
• 如果mytable没有针对id的合适索引，SELECT会触发全表扫描，SQL Server可能会将行锁升级为页锁或表锁，进一步加剧阻塞范围。

PostgreSQL的快照隔离避免阻塞

• PostgreSQL的READ COMMITTED基于快照隔离实现：每个SELECT语句执行时，会生成一个数据库快照，读取的是快照生成时刻已提交的数据，完全不依赖锁。
• CASE:A的INSERT操作只会在自己的行上持有X锁，CASE:B的SELECT看不到CASE:A未提交的id=98行，也不会去申请锁；反之CASE:A的SELECT也看不到CASE:B未提交的id=99行，因此不会出现互相阻塞的情况。

项目中MS-SQL出现死锁、卡顿的原因 #

项目中类似的并发读写场景下，SQL Server的锁竞争容易形成循环等待（死锁），尤其是无索引的全表查询会扩大锁范围，延长阻塞时间；而PostgreSQL的快照读取模式从根源上避免了读与写的锁冲突，因此运行更稳定。

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