mysql分析表锁_mysql dml和表锁情况分析

测试环境：

mysql 8.0

测试在不同隔离级别下，对一张表进行dm操作，在全表扫描情况下，是否会出现锁表的问题。

因为在有索引的情况下，mysql 使用记录锁 实现行意向锁。并且使用间隙锁在REPEATABLE READ 隔离级别防止幻影读。

实验：

id2 是非索引的列，所以下面DML 都是全表扫描。

start transaction;

--delete from t1 where id2='bb';

update t1 set id2='cc' where id2='bb';

rollback;

--删除和update 是一样的

这个dml 会在每个读取行上获取一个x锁，并且不会释放其中任意一行。

此时在另外的session中执行：

insert into t1(id,id2) values(666,'bb');

update t1 set id2='cc' where id=353

会被阻塞

如果使用READ COMMITTED，则第一个UPDATE会为其读取的每一行获取一个x锁，并释放那些未修改的行：

set session transaction isolation level read committed;

start transaction;

--delete from t1 where id2='bb';

update t1 set id2='cc' where id2='bb';

rollback;

select * from t1;

此时在另外session中执行：

insert into t1(id,id2) values(666,'bb');

update t1 set id2='cc' where id=353

并不会被阻塞，顺利执行。

还有一种情况，如果使用索引，where条件包含索引列，在多个where条件更新的过程中，会保留索引列上的记录锁。

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE =InnoDB;

INSERT INTO t VALUES (1,2,3),(2,2,4);

COMMIT;

# Session A

START TRANSACTION;

UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B

UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

虽然看起来第二个会话更新的是不同行，但是还是会被会话A阻塞。

也就是会把索引列b=2的所有行加上记录锁。这种情况和隔离级别无关。