【mysql45讲】全局锁、表级锁(表锁、MDL)、行锁的作用与区别

1.全局锁

定义：

全局锁就是对整个数据库加锁。

加全局读锁的命令：

Flash tables with read lock(FTWRL)。
这个命令执行后，会阻塞 数据更新语句、数据定义语句、更新事务的提交。

为什么用全局读锁而不是readonly：

当客户端发生异常时，FTWRL会自动释放全局锁，库回到正常状态；readonly不会，数据库就一直保持着不可写状态。

为什么不用single-transaction：

在支持事务的引擎中（如InnoDB),建议使用single-transaction。
将启动参数TRANSACTION ISOLATION设置为REPEATABLE READ（可重复读）;

show variables like ‘transaction_isolation’;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

2.表级锁

2.1表锁

语法：

加锁：lock tables… read/write
释放：可以用unlock tables主动释放，也可以等断开连接时自动释放

要点：

lock tables不仅限制了其他线程的读写，也限制了本线程接下来的操作对象
举个例子：lock tables t1 read,t2 write，使本线程只能读t1,写t2,不能写t1也不能读写其他表

2.2元数据锁（mete data lock MDL)

使用方式：

不需要显式使用，在访问表的时候会自动加上。

什么时候用：

当对一个表增删改查时，加MDL读锁；当对一个表做结构变更操作时，加MDL写锁。
读锁不互斥，可以有多个线程对同一张表增删改查；写锁互斥，比如两个线程要对一张表修改表结构，第二个线程要等第一个线程结束后再修改。

什么时候释放

语句的MDL锁，在语句执行完后不会立即释放，而是等事务提交后才会释放。

使用例子：给访问频繁、数据量不大的热点表增加字段

解决方法：在alter table语句中设置等待时间，如果不能在等待时间内拿到MDL写锁，就放弃，这样不会影响后面的业务语句。之后开发人员或DBA再通过重试命令重复这个过程。

ALTER TABLE tbl_name NOWAIT add column …
ALTER TABLE tbl_name WAIT N add column …

3.行锁

不是所有的存储引擎都支持行锁，InnoDB是支持行锁的。

加在索引上

INNODB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁。也就是说，没有索引的时候，使用表锁。

什么时候：

在InnoDB中，行锁是需要时才加上，但不是执行完那条语句就释放，要等事务提交后释放。
所以，在应用中，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
举例：

死锁：

什么时候会出现死锁

并发系统中不同线程间循环资源等待

出现死锁怎么办

1.直接进入等待直到超时

设置超时时间

set global innodb_lock_wait_timeout=xx;

超时时间太长，系统不能忍受；超时时间太短，可能只是普通的锁等待而非死锁，容易误伤。
所以我们推荐第二种办法。

2.死锁检测

发起死锁检测，检测到死锁时，回滚某个事务，让其他事务得以执行。

将参数 innodb_deadlock_detect 设置为 on

但死锁检测也会消耗大量CPU资源