MySQL锁

MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

表级锁

一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

给一个小表加个字段，导致整个库挂了。

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。我们来看一下下面的操作序列，假设表 t 是一个小表。

1. 我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。
2. 之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。
3. 如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。
4. 如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

如何安全地给小表加字段？

ALTER TABLE tbl_name NOWAIT add column ...

ALTER TABLE tbl_name WAIT N add column ...

共享锁

行级别的锁:共享锁

我们获取了 一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁，注意不要在加上了读锁 以后去写数据，不然的话可能会出现死锁的情况。

SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

排它锁

行级别的锁：Exclusive Locks(排它锁)。

它是用来操作数据的，所以又 叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数 据的共享锁和排它锁。

第一种是自动加排他锁。我们在操作数据的时候，包括 增删改，都会默认加上一个排它锁。

还有一种是手工加锁，我们用一个 FOR UPDATE 给一行数据加上一个排它锁，这个 无论是在我们的代码里面还是操作数据的工具里面，都比较常用。

SELECT * FROM student where id=1 FOR UPDATE;

意向锁

当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个 意向共享锁。

当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。

行锁原理

没有索引的表

首先我们有三张表，一张没有索引的 t1，一张有主键索引的 t2，一张有唯一索引的 t3。

我们先假设 InnoDB 的锁锁住了是一行数据或者一条记录。

我们先来看一下 t1 的表结构，它有两个字段，int 类型的 id 和 varchar 类型的 name。 里面有 4 条数据，1、2、3、4。

Transaction 1	Transaction 2
begin
SELECT * FROM t1 WHERE id =1 FOR UPDATE;
	select * from t1 where id=3 for update;//blocked
	INSERT INTO t1 (id, name) VALUES (5, ‘5’);//blocked

没有索引或者没有用到索引的情况下，会锁住整张表。

有主键索引的表

Transaction 1	Transaction 2
begin
SELECT * FROM t1 WHERE id =3 FOR UPDATE;
	select * from t1 where id=3 for update;//blocked
	select * from t2 where id=4 for update; // OK

使用相同的 id 值去加锁，冲突;使用不同的 id 加锁，可以加锁成功。

唯一索引

Transaction 1	Transaction 2
begin
SELECT * FROM t1 WHERE id =4 FOR UPDATE;
	select * from t1 where id=4 for update;//blocked
	select * from t2 where id=4 for update; // blocked

为什么表里面没有索引的时候，锁住一行数据会导致锁表?

1. 如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。
2. 如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
3. 如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐
藏的聚集索引都锁住了。

为什么通过唯一索引给数据行加锁，主键索引也会被锁住?

我们通过辅助索引锁定 一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。