全局锁+表锁+行锁

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表锁、行锁三类。
全局锁和表锁都是在Server层（基本是在执行器阶段）实现的，与及具体的存储引擎无关。
行锁是在引擎层由各个引擎自己实现的，并不是所有的引擎都支持行锁。

全局锁

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

1. Flush tables with read lock;(FTWRL)

加全局读锁，让整个库处于只读状态，其他线程的：数据更新语句（数据的增删改）、数据定义语句（建表、修改表结构）、更新类事务的提交语句，会被阻塞。(FTWRL前有读写的话，FTWRL会等待读写执行完毕后才执行。)

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

在主库上备份，在整个备份期间都不能执行更新，业务基本上就得停摆。
在从库上备份，备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

不加锁的话，备份系统备份得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。（在可重复读隔离级别下开启一个事务，可以拿到一致性视图。）

2. mysqldump –single-transaction;

官方逻辑备份工具mysqldump。导数据之前会启动一个事务，来确保拿到一致性视图。基于MVCC(多版本并发控制)，保证备份过程中数据可以正常更新。

single-transaction只适用于所有的表使用事务引擎的库(InnoDB)。如果有的表使用了不支持事务的引擎(MyISAM)，那么备份就只能通过FTWRL方法。

表结构不在一致性视图的保护里面，表结构数据不支持一致性读。
备份时启动了可持续读的事务，在整个备份过程中都是在这个视图下执行的，其他事务的变更不会影响它的备份。但是master传过来的DDL影响，会得到DDL后的表结构。

备库用–single-transaction 做逻辑备份

从主库的binlog传来一个DDL语句。

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 确保RR（可重复读）隔离级别
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;-- 回滚到sp---释放t1的MDL锁
/* 时刻 4 */
/* other tables */

如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

3.set global readonly=true;

让全库进入只读状态。

有些系统中，readonly的值会被用来做其他逻辑：判断一个库是主库还是备库。修改global变量的方式影响面太大。
在异常处理机制上。执行FTWRL命令后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。将整个库设置成readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，会导致整个库长时间处于不可写状态，风险较高。
如果用户有超级权限，readonly是失效的。执行binlog的线程（主从复制）是super权限，不影响主从复制。

业务的更新不仅有增删改数据DML(Data Manipulation Language)（select … lock in share mode;select … for update;行锁），还可能有加字段等修改表结构的操作DDL(Data Definition Language)。都会被全局锁锁住。

表级锁

MySQL表级别的锁有两种不同维度：表锁、元数据锁(meta data lock,MDL)。

1. 表锁lock tables … read/write;

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

如果在某个线程A中执行了lock tabes t1 read, t2 write;则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作，不允许写t1，更不能访问其他表。

InnoDB引擎支持行锁，一般不使用lock tables命令来控制并发，锁住整个表的影响太大。(lock tables&unlock tables–>begin commit)

2. 元数据锁MDL(metadata lock);

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL保证读写的正确性。MDL防止DDL(表结构)和DML(增删改)并发的冲突。

MySQL5.5引入MDL锁，当对一个表做增删改查操作的时候(DML)，加MDL读锁；当要对表做数据结构变更操作的时候(DDL)，加MDL写锁。

读锁之间不互斥，可以有多个线程同时对一张表增删改查。(对同一行数据更新导致的互斥是行锁发挥作用)。（InnoDB做数据的增删改查不需要加表锁）
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完成才能开始执行。

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交之后再释放。

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据，加MDL写锁。（加普通字段不用重建全表，加主键要重建）

一个连接就是一个session，一个连接里可以先后创建多个事务（必须前一个结束才能启动下一个）。事务是无处不在的，大事务会造成额外的处理开销。

dml与ddl之间交互容易出现不可读写的情况

session A:select * from t;  
session B:select * from t;  
session C:alter table t add f int;  
session D:select * from t;

dml与ddl之间交互容易出现不可读写的情况：

session A先启动begin，会对表t加MDL读锁。session B需要的也是MDL读锁，可以正常执行。(不显式begin,不用显式commit,查询完自动释放)
session C需要的是MDL写锁，session A的读锁还未释放，session C只能被阻塞。
之后所有要在表t上新申请MDL读锁的请求(session D)会被session C阻塞(锁队列，先来先得，一进去等待时就开始影响后面，申请不到也占着)。所有对表的增删改查操作都需要先申请MDL读锁，都被锁住，整个表t完全不可读写了。
若表t上的查询语句频繁+客户端重试机制(超时后再起一个新的session再请求)，整个库的线程会很快爆满。

MySQL5.6支持online ddl(不堵塞增删改查mdl读锁，ddl执行的大部分时间，占着的也是mdl读锁)：

1.拿MDL写锁
2.降级成MDL读锁
3.真正做DDL
4.升级成MDL写锁
5.释放MDL锁

安全的给小表加字段：

非热点表：首先解决长事务，事务不提交，就会一直占用MDL锁。在MySQL的information_schema库的innodb_trx表中，可以查到当前执行中的事务。如果要做DDL变更的表有长事务执行，需要先暂停DDL，或者kill掉这个长事务。
热点表：在alter table语句中设定等待时间。如果在指定的等待时间里面能够拿到MDL写锁就执行，拿不到就不要阻塞后面的业务语句，先放弃。之后再通过重试命令重复这个过程。

MariaDB和AliSQL这两个开源分支都支持DDL NOWWAIT/WAIT N语法（语句级别。对应session级别的lock_wait_timeout）；

ALTER TABLE tbl_name NOWWAIT add column ...;
ALTER TABLE tbl_name WAIT N add column ...;

大表加字段：
pt-online-schema-change
gh-ost

行锁

行锁是针对数据表中行记录的锁。事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁。不支持行锁意味着并发控制只能使用表锁，对于(MyISAM)这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。

dml语句会产生MDL读锁，MDL锁和表锁是两个不同的结构。
在MyISAM表上更新一行，那么会加MDL读锁和表的写锁；同时另外一个线程要更新这个表上另外一行，也要加MDL读锁和表的写锁。第二个线程的MDL读锁能成功加上，但是表写锁堵住。第二个线程要等第一个线程执行完成。

InnoDB行级锁是通过锁索引记录来实现的，如果update的列没建索引，即使只update一条记录也会锁定整张表。InnoDB内部是全表根据主键索引逐行扫描、逐行加锁，事务提交时统一释放。

InnoDB中没有不开启事务的更新update，没写默认就是单语句事务，语句结束的地方释放锁。insert、delete都是行锁。

两阶段锁

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这就是两阶段锁协议。

如果事务中需要锁多行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。（多个事务更新同一行，只是锁等待；如果还要更新别的行，可能造成循环依赖，导致死锁。一个事务自己跟自己不会死锁。）
一个表有多个索引的话，并发插入，也可能会导致死锁。

show engine innodb status;– 查看死锁信息。记录“之后一个死锁”的地方

直接进入等待，直到超时。

超时时间可以通过参数innodb_lock_wait_timeout来设置。

innoDB中，innodb_lock_wait_timeout的默认值是50s。当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。超时时间设置太短的话，会出现很多误伤。

发起死锁检测。

发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on（默认值on），表示开启这个逻辑。

在发生死锁的时候，是能够快速发现并进行处理的，但是有额外负担–耗费大量的CPU资源。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。死锁检测的时间复杂度是O(n²)，单个事务为O(n)。

如果新的事务要加锁访问的行上有锁（一个事务更新一条记录时发现被阻塞了），才会进行死锁检测。1一致性读不会加锁，不需要做死锁检测。2并不是每次死锁检测都要扫描所有事务。

如果能确保业务一定不会出现死锁，可以临时把死锁检测关掉。
业务设计的时候一般不会把死锁当做一个严重错误，出现死锁了就回滚，然后通过业务重试一般就没问题了，业务无损。关掉死锁检测意味着可能会出现大量的超时，业务有损。
控制并发度。
在客户端做并发控制。同一行同时最多只有10个线程在更新。若客户端很多，即使每个客户端控制到只有个位数并发线程，汇总到数据库服务端以后，峰值并发数也很高。
在服务端做并发控制。如果有中间件，可以考虑在中间件中实现；也可以在MySQL源码里。对于相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作。
通过将一行改成逻辑上的多行来减少锁冲突。
将总账户放在多条记录上，账户总额等于这多条记录的值的总和，每次给账户加金额的时候，随机选其中一条记录来加。冲突概率变成原来的1/n，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。业务复杂度可能大大提高()。

直接执行delete fron T limit 10000;
单个语句占用时间长，事务相对较长，占用锁的时间较长，会导致其他客户端等待资源时间较长。大事务还会导致主从延迟。
在一个连接中循环执行20次delete from T limit 500;（默认每次独立提交）
串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作意味着将资源分片使用(每次使用不同片段的资源)，可以提高并发性。
在20个连接中同时执行delete from T limit 500;
人为制造锁竞争，造成锁冲突，加剧并发量。
添加主键，根据主键删除。