数据库锁

锁简介

开始之前，先说一下DML、DDL区别
DML（data manipulation language）：
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
DDL（data definition language）：
DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DCL（Data Control Language）：
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

数据库锁设计的初衷是处理并发问题，合理控制资源访问规则。

根据加锁范围：数据库里面的锁可以分为：全局锁、表级锁、行级锁

全局锁

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

全库只读，有两种方式：
1、set global readonly=true方式，让全库进入只读方式，该方法不建议。
原因一：有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。修改 global 变量的方式影响面很大。
原因二：将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
原因三：readyonly对super用户权限无效。换句话说，设置只读后，super用户依旧是可以修改表结构等操作。

2、加全局锁的方式，Flush tables with read lock(FTWRL)。
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
加锁跟上述设置只读相比，在异常处理机制上会好些，如果执行FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。

全局锁的使用场景：全库逻辑备份。不加锁可能会导致备份过程中不同表数据不一致。

数据库只读状态的风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟

官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。但是需要引擎支持这个隔离级别，而MyISAM不支持事务引擎，所以在备份时就得需要用FTWRL命令了。

表级锁

MySQL里面表级锁有两种：
1、表锁
（1）表锁的语法是:lock tables … read/write
（2）表锁的释放：可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。l
（3）表锁语法不仅会限制别的线程读写，也会限定本线程接下来的操作对象。比如线程 A 中执行 lock tables t1 read, t2 write;那线程A在解锁前，只能对t1读，t2读写，其他表不能访问；其他线程对t1的读，t2的读写都会被阻塞。
（4）InnoDB因为支持行锁，一般不使用表锁。
2、元数据锁(meta data lock,MDL)server层面实现的。
（1）不需要显式使用，在访问一个表的时候会被自动加上。

在对一个表做增删改查操作的时候，加MDL读锁；
当要对表做结构变更操作的时候，加MDL写锁。

（2）MDL释放的情况：事务提交。
（3）MDL的作用：保证读写的正确性。在表上有事务的时候,不可以对元数据经行写入操作。

读锁之间不互斥。
读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

当进行DML操作时，增加的 MDL 读锁, update table set id=Y where id=X; 并且由于隔离级别的原因 读锁之间不冲突

当进行DDL操作是，增加对表的写锁, 同时操作两个alter table 操作 这个要出现等待情况。

但是DML 与DDL 之间的交互就更容易出现不可读写情况，这个情况容易session 爆满,session是占用内存的,也会导致内存升高：

表结构变更导致的锁住线上查询更新的例子：

以上session A 先启动，这时候会对表 t 加一个 MDL 读锁。
session B 需要的也是 MDL 读锁，因此可以正常执行。
session C 会被 blocked，因为 session A 的 MDL 读锁还没有释放。
之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。
然后这个表就完全不可读写了。如果这个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

如何安全的给小表加字段？
1、解决长事务。
事务不提交，就会一直占着 MDL 锁。
在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
2、alter table 语句里面设定等待时间
在指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后再通过重试命令重复这个过程。

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

问题：如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列，而此时备库正在使用–single-transaction 方法做逻辑备份的过程中，这时候，从备库上会看到什么现象呢？

回答：
主库上做了DDL会同步给slave ，由于这个时候有了先前的 single-transaction，所以slave 就会出现 该表的锁等待, 并且slave 出现延迟。
如下备份中的几个关键语句：
(Q1)：在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 ;
(Q2)：启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图；
(Q3)：设置一个保存点，这个很重要；
(Q4)：show create 是为了拿到表结构
(Q5)：正式导数据
(Q6）回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 。

DDL 从主库传过来的时间按照效果不同，我打了四个时刻。假设是小表，同步很快可以完成。
1、如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
2、如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
3、如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
4、从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

注意：一致性视图是不包含表结构的。

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

行锁

行锁概念

1、行锁是在引擎层由各个引擎自己实现。
MyISAM引擎不支持行锁，并发控制只能使用表锁。
InnoDB支持行锁。
2、行锁是针对数据表中行记录的锁。如果两个事务都要更新一行，的需要一个事务操作完后另外一个事务才能更新。
3、两阶段锁。行锁在需要的时候才加上，但并不是不需要了就立刻释放，而是要等到事务结束时才能释放。

注意：
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。这样最大程度地减少了事务之间的锁等待，提升并发度。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

如上，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

出现死锁后的处理策略：
1、直接进入等待，直到超时。该方法不太建议使用。
这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

2、发起死锁检测。
发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。即每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

问题：假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。

解决由这种热点行更新导致的性能问题的方法：
1、如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。该方法存在风险，可能会出现大量的超时，这是业务有损的。

2、控制并发度。
如果在客户端做并发控制，不太可行，如果客户端过多，整体并发度依旧很高。
因此并发控制要做在数据库服务端。如果有中间件，可以考虑在中间件实现；或者修改 MySQL 源码。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

问题：如果要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：
第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。
应该选择哪一种方法呢？

答案：
第一种方式里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
第二种方式较好。
第三种方式，会人为造成锁冲突。