测试版本

MySQL 8.0.31

PostgreSQL 15.1

表结构

MySQL

create table test
(
    id   int auto_increment
        primary key,
    name varchar(50) default '' not null,
    book varchar(50)            null
);

PostgreSQL

create table test
(
    id   bigint generated by default as identity
        primary key,
    name varchar(50) default ''::character varying not null,
    book varchar(50)
);

数据集

执行 SQL

准备阶段

确认 MySQL 和 PostgreSQL 的隔离级别均已设置为 RC

PostgreSQL 查看隔离级别语句 show transaction isolation level;

MySQL 查看隔离级别语句 SELECT @@GLOBAL.transaction_isolation

MySQL 默认隔离级别为 RR 要用 set global transaction isolation level read committed; 改成 RC 隔离级别

执行阶段

MySQL

开启两个 session

可以看到 session 2 进入了锁等待

PostgreSQL

开启两个 session

PG 这边没有进入锁等待

探究

我们知道锁是加在索引上的，在我们的测试场景下 MySQL 和 PostgreSQL 都是只有主键索引，没有其他索引

根据理论分析，这两个删除语句应该不会有锁等待

delete from test where name = 'a' and book = 'a'; 会把锁加在主键索引 id = 1 上

delete from test where name = 'a' and book = 'c'; 会把锁加在主键索引 id = 2 上

实际上 MySQL 出现了锁等待，PostgreSQL 没有锁等待

那为什么两个 DB 表现不同呢？

PostgreSQL 符合我们的预期，我们主要来看看 MySQL

SELECT * FROM performance_schema.data_locks; 这个语句可以在 MySQL 8.0 以上的版本中用来查询锁的情况

在 MySQL 中用之前打开的 session 执行如下命令

图 1

图 2

可以看到确实在主键索引 id = 1 上加了两次锁，所以发生了锁等待

我们通过调换 SQL 语句执行的顺序，进一步探究加锁的情况

图 3

图 4

这次调换语句顺序的目的是为了让 MySQL 先对靠后的数据进行加锁，即锁加在了主键索引 id = 2 上，见图三

继续看图 4，我们执行的语句是 delete from test where name = 'a' and book = 'a'; 按理说锁只加在主键索引 id = 1 上，但对比图 3 我们能看出实际是在主键索引 id = 1 和 id = 2 上都进行了加锁

由此我们可以推断出 MySQL 在事务中会依次对扫描过的行加锁，整个扫描结束后才会释放非目标行的锁，想要避免这种情况，需要增加索引，且这个索引需要包含能区分两行记录的字段即 book 字段

而 PostgreSQL 则是扫描完一行则释放一行

正是锁释放时机的不同造成了这种差异

个人是觉得 PostgreSQL 的处理方式更加合理一些，不知道 MySQL 为什么要等事务结束在释放非目标行的锁，奇奇怪怪的

注意：以上结论是通过实验得出的推论，是否真的如此需要看源码才能真的确定

补充 case

如果将 delete 语句替换为 update 语句会怎样呢？

WOW，居然没有锁等待了

然而 MySQL 官方对 update 和 delete 加锁的描述是一样的，可能加的锁确实一样，只是释放时机不一样吧...

后来找到官方文档有这样的描述

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels

As InnoDB executes each UPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If InnoDB does not modify the row, it releases the lock. Otherwise, InnoDB retains the lock until the end of the transaction. This affects transaction processing as follows.

确实是事务结束时释放锁，但其中有提到如果行没有被修改会释放，根据实验结果来看，应该是这个功能出 bug 了..

更进一步，我们给有重复字段的 name 字段加上索引，再执行一遍上边的 update 测试呢？

create index test_name_index on test (name);

emmmm....加索引反而降低并发度，MySQL 真有你的....