MySQL 表锁和行锁

关于Mysql的锁机制 详细可以了解一张图彻底搞懂 MySQL 的锁机制 | MySQL 技术论坛 (learnku.com)

本文主要记录一些实践验证。

首先创建test数据库做测试：

drop table if exists test;

CREATE TABLE test (

    a INT (11),

    b VARCHAR (20)

) ENGINE INNODB DEFAULT charset = utf8;

insert into test values (1,'a');

insert into test values (2,'b');

insert into test values (3,'c');

insert into test values (4,'d');

insert into test values (5,'e');
create index idx_lock_a on test(a);

create index idx_lock_b on test(b);

行锁(innodb 默认)

行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强
加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：
共享锁：select * from tableName where ... + lock in share more
排他锁：select * from tableName where ... + for update
InnoDB和MyISAM的最大不同点有两个：一，InnoDB支持事务(transaction)；二，默认采用行级锁。加锁可以保证事务的一致性，可谓是有人(锁)的地方，就有江湖(事务)；

准备两个窗口分两个事务执行：

行锁（写&读）

• A 窗口执行

update test set b='a1' where a=1;

SELECT * from test;

我们可以看到 A 窗口可以看到更新后的结果

• B 窗口执行

SELECT * from test;

我们可以看到 B 窗口不能看到更新后的结果，看到的还是老数据，这是因为 a = 1 的这行记录被 A 窗口执行的 SQL 语句抢到了锁，并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的"读已提交"。为什么上了写锁，别的事务还可以读操作？ 因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。

• 窗口 A 执行 commit 操作

COMMIT;

• 窗口 B 查询

SELECT * from test;

这个时候我们发现窗口 B 已经读取到最新数据了

行锁（写&写）

• 窗口 A 执行更新 a = 1 的记录

update test set b='a2' where a=1;

这时候并没有 commit 提交，锁是窗口 A 持有。

• 窗口 B 也执行更新 a = 1 的记录

update test set b='a3' where a=1;

可以看到，窗口 B 一直处于阻塞状态，因为窗口 A 还没有执行 commit，还持有锁。窗口 B 抢不到 a = 1 这行记录的锁，所以一直阻塞等待。

• 窗口 A 执行 commit 操作

COMMIT;

• 窗口 B 的变化

可以看到这个时候窗口 B 已经执行成功了

表锁

表锁的优势：开销小；加锁快；无死锁
表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低
加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁：
共享读锁：lock table tableName read;
独占写锁：lock table tableName write;
批量解锁：unlock tables;

当索引失效的时候，行锁会升级成表锁，索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer，我们加上引号，就变成了 String，这个时候索引就会失效了。

• 窗口 A 更新 a = 1 的记录

update test set b='a4' where a=1 or a=2;//索引失效

• 窗口 B 更新 a = 2 的记录

update test set b='b1' where a=3;

这个时候发现，虽然窗口 A 和 B 更新的行不一样，但是窗口 B 还是被阻塞住了，就是因为窗口 A 的索引失效，导致行锁升级成了表锁，把整个表锁住了，索引窗口 B 被阻塞了。

• 窗口 A 执行 commit 操作

COMMIT;

• 窗口 B 的变化

可以看到这个时候窗口 B 已经执行成功了

间隙锁

• 什么是间隙锁

当我们采用范围条件查询数据时，InnoDB 会对这个范围内的数据进行加锁。比如有 id 为：1、3、5、7 的 4 条数据，我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中，但是不存在这些数据记录，这些 2、4、6 就被称为间隙。

• 间隙锁的危害

范围查找时，会把整个范围的数据全部锁定住，即便这个范围内不存在的一些数据，也会被无辜的锁定住，比如我要在 1、3、5、7 中插入 2，这个时候 1-7 都被锁定住了，根本无法插入 2。在某些场景下会对性能产生很大的影响

• 间隙锁演示

我们先把字段 a 的值修改成 1、3、5、7、9

• 窗口 A 更新 a = 1~7 范围的数据

update test set b='b5' where a>1 and a<7;

• 窗口 B 在 a = 2 的位置插入数据

insert into test values(2, "b6");

这个时候发现窗口 B 更新 a = 2 的操作一直在等待，因为 1~7 范围的数据被间隙锁，锁住了。只有等窗口 A 执行 commit，窗口 B 的 a = 2 才能更新成功。间隙锁解决事务幻读问题。

什么场景下用表锁

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。
即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

第二种情况：多表级联。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

总结

1 InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。
2 InnoDB 自动给修改操作加锁，给查询操作不自动加锁
3 行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过explain执行计划查询索引是否被实际使用。
4 行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。
5 当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。
6 为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。