mysql 诡异录

删不掉的记录

10.1.15-MariaDB
create table test (id int primary key, name varchar(8));
insert into test values (1,‘a’),(2,‘b’);

tx-1	tx-2
begin;
	begin;
select * from test; '1,2
	select * from test; '1,2
delete from test where id=1; 'OK, 1 rows
	delete from test where id=1; '锁等待…
commit; 'OK, 0 rows
	OK, 0 rows
	select * from test; '1,2 '1这条看得到删不掉

有1,2两条记录时再试一次:

tx-1	tx-2
begin;
	begin;
delete from test where id=1; 'OK, 1 rows
	delete from test where id=1; '锁等待…
commit; 'OK, 0 rows
	OK, 0 rows
	select * from test; '仅剩2

结论:
并发删除的记录, 被阻塞的事务如果之前查过, 则仍能看到. 被阻塞的事务如果之前没查过, 则从来查不到. (可能是查询时加了共享锁)