1. 事务是什么
是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行。
一条SQL语句的执行过程：
（1）更新语句经过 解析，优化生成执行计划，交由执行器调用存储引擎接口（注：执行器会多次调用存储引擎接口，并不是一次完成）
（2）查询旧值，先去内存缓冲区查看是否有数据，如果没有，从磁盘中加载到内存，并将旧值写入到 Undo log 日志中，用于回滚数据
（3）更新内存中的数据（注：磁盘仍为旧数据）
（4）将操作写入到redolog 中
（5）redolog根据刷盘策略刷到磁盘
（6）准备提交事务，写入binlog 日志（注：binlog也有自己的刷盘策略）
（7）把本次更新对应的binlog文件名称和这次 更新的binlog日志在文件里的位置，都写入到redolog日志文件里去，同时在redolog日志文件里写入一个commit标 记。
（8）事务完成
（9）mysql会有个后台线程将内存数据刷入到磁盘
2. 事务的基本操作
设置提交模式：
MySQL默认是自动提交模式，如果没有显示开启事务START TRANSACTION，每一条SQL语句都会自动提交commit。如果想要控制自动提交，可以通过更改autocommit变量来实现，将其值设为1表示开启自动提交，设为0表示关闭自动提交。
select @@autocommit; # 查看当前autocommit的值set autocommit=0; # 关闭自动提交模式set autocommit=1; # 开启自动提交模式
开启事务：
可以使用一条START TRANSACTION或begin语句来显式地启动一个事务。两者的作用一摸一样，只是在begin可能成为关键字的时候，使用START TRANSACTION 可以避免这种情况，所以建议使用START TRANSACTION。START TRANSACTION;begin;
START TRANSACTION/begin 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用START TRANSACTION with consistent snapshot这个命令。
结束/提交事务
COMMIT语句是提交语句，它使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，也标志一个事务的结束。COMMIT;
撤销事务
ROLLBACK语句是撤销语句，它撤销事务所做的修改，并结束当前这个事务。ROLLBACK;
回滚事务
设置保存点
除了撤销整个事务，用户还可以使用ROLLBACK TO语句使事务回滚到某个点，在这之前需要使用SAVEPOINT语句来设置一个保存点。
SAVEPOINT identifier;其中，identifier为保存点的名称。
回滚至保存点
ROLLBACK TO SAVEPOINT语句会向已命名的保存点回滚一个事务。如果在保存点被设置后，当前事务对数据进行了更改，则这些更改会在回滚中被撤销。
ROLLBACK TO SAVEPOINT identifier;
当事务回滚到某个保存点后，在该保存点之后设置的保存点将被自动删除。
删除保存点
RELEASE SAVEPOINT语句会从当前事务的一组保存点中删除已命名的保存点。不出现提交或回滚。如果保存点不存在，会出现错误。RELEASE SAVEPOINT identifier;
查看隔离级别
对于隔离级别的查看，有几种不同的方式。如下：
select @@global.transaction_ioslation; # 查看全局隔离级select @@session.transaction_ioslation; # 查看当前会话的隔离级select @@transaction_ioslation; # 查看下一个事务的隔离级
修改隔离级别
基于ANSI/ISO SQL规范，MySQL提供了下面4种隔离级：序列化（SERIALIZABLE）、可重复读（REPEATABLE READ）、提交读（READ COMMITTED）、未提交读（READ UNCOMMITTED）。
系统变量TX_ISOLATION中存储了事务的隔离级，可以使用SELECT获得当前会话隔离级的值
SELECT @@TX_ISOLATION;
只有支持事务的存储引擎才可以定义一个隔离级。定义隔离级可以使用SET TRANSACTION语句。
语法格式：
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL 隔离级别（ | SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED） 例如：SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 将当前会话的隔离级别设为读未提交 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 将全局的隔离级别设为读未提交
3. 一致性：
数据库总是从一个一致性的状态转换到另外一个一致性的状态。由原子性/持久性/隔离性,共同实现。
4. 原子性：
事务中的SQL语句要么全部执行成功要么全部失败，不会部分成功部分失败。
1. 实现原理：
主要是使用了undolog来实现原子性，undolog记录了事务的执行过程，当事务未能成功执行或是回滚时，根据undolog中的记录，反向执行SQL。undolog除了回滚外还有另一个作用就是多版本控制（MVCC）。
注意：undolog 是逻辑日志，可以理解为
当delete 一条记录时，undo log 中会记录一条对应的insert 记录
当insert一条记录时，undo log 中会记录一条对应的delete 记录
当update 一条记录时，它记录一条对应相反的update记录。
当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断其他事务是否在使用， 如隔离级别repeatable read时，事务需要历史读，所以读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，undo log就不能删除，在后文的MVCC还会提到。
提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。
2. undolog(回滚日志)与redolog(重做日志)的区别：
1.redolog通常是物理日志，记录的是数据页的物理修改，而不像undolog记录某一行或某几行的修改，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
2.undolog用来回滚行记录到某个版本，undolog是逻辑日志，根据每行记录进行记录。
5. 持久性：
1. 实现原理：
背景：
首先需要知道，数据库中的数据是存在磁盘中的，但是即使SSD磁盘的写入与读写速度都不够快，为了提升读写速度，所以Innodb中使用了一个在内存中的缓存池，Buffer Pool 中包含了磁盘数据页的映射，可以当做缓存来使用。
读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取再放入缓冲池；
写数据：MySQL表数据是持久化到磁盘中的，但如果所有操作都去操作磁盘，等并发上来了，那处理速度谁都吃不消，因此当修改表数据时，把操作记录先写到Buffer Pool的change buffer中，并标记事务已完成，等MySQL空闲时，再把更新操作持久化到磁盘里，从而大大缓解了MySQL并发压力。但是Buffer Pool/change buffer是内存，所以需要redo log进行持久化。
持久性主要是通过redolog来实现，InnoDB引擎会把更新记录写到redolog日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redolog中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术，他的关键点是先写日志，再写磁盘。
这样的目的是因为内存不可靠，万一断电重启，还没来得及落盘的内存数据就会丢失，所以还需要加上写日志这个步骤，万一断电重启，还能通过日志中的记录进行恢复。也就达到了crash-safe，crash-safe指事务执行过程中突然奔溃，重启后能保证事务完整性。
redolog是固定大小的，所以只能循环写，从头开始写，写到末尾就又回到开头，相当于一个环形。当日志写满了，就需要对旧的记录进行擦除，但在擦除之前，需要确保这些要被擦除记录对应在内存中的数据页都已经刷到磁盘中了。在redolog满了到擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求，所以有可能会导致MySQL卡顿。（所以针对并发量大的系统，适当设置redolog的文件大小非常重要）
Tips：如果断电时redolog未记录完毕,那么就看binlog是否完整，如果binlog完整就重做redolog，binlog如果不完整就按照undolog回滚。
2. 关于binlog：
binlog在MySQL的server层产生，不属于任何引擎，主要记录用户对数据库操作的SQL语句（除了查询语句）。
之所以将binlog称为归档日志，是因为binlog不会像redolog一样擦掉之前的记录循环写，而是一直记录（超过有效期才会被清理），如果超过单日志的最大值（默认1G，可以通过变量 max_binlog_size 设置），则会新起一个文件继续记录。
但由于日志可能是基于事务来记录的(如InnoDB表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好binlog日志文件达到了最大值但事务还没有提交则不会切换新的文件记录，而是继续增大日志，所以max_binlog_size 指定的值和实际的binlog日志大小不一定相等。
主从模式下，binlog用来做从库的数据同步。
单机模式下，不考虑数据库基于时间点的还原，binlog就不是必须，因为有redolog就可以保证crash-safe能力了。但如果万一需要回滚到某个时间点的状态，这时候就无能为力，所以建议binlog还是一直开启。
3. reolog(重做日志)与binlog(归档日志)区别：
- 作用不同：
redolog属于innoDB层面，是保证事务的持久性的，作为异常宕机或者介质故障后的数据恢复使用。
binlog是数据库层面的(当然也可以精确到事务层面的)，可以作为恢复数据使用，主从复制搭建，虽然都有还原的意思，但是其保护数据的层次是不一样的。 - 内容不同：
redolog是物理日志，以二进制的形式记录的是这个语句的原始逻辑。
binlog是逻辑日志，可以简单认为记录的就是sql语句。 - 写入方式不同：
redolog是循环写，日志空间大小固定。
binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
6. 隔离性：
MySQL 事务隔离依靠锁与MVCC实现。
1. 异常
脏读
事务A修改了数据，但未提交，而事务B查询了事务A修改过却没有提交的数据，这就是脏读，因为事务A可能会回滚。
不可重复读
事务A第一次查a数据为100，未提交，此时事务b修改了此数据并且提交，事务A第二次查的时候数据变了。针对数据本身。
幻读
幻读是指在同一个事务中，存在前后两次查询同一个范围的数据，但是第二次查询却看到了第一次查询没看到的行，一般情况下指新增数据。也就是A第一次查有1条，B插了一条，A第二次查有2条。针对数据量。
2. 隔离级别
为了解决上述问题，MySQL制定了四种不同的“隔离级别”，包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。 隔离得越严实，效率就会越低。
原理描述：
在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图 consistent read view 。
在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。
这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
1. 读未提交(READ UNCOMMITTED)：
一个事务还没提交时，它做的变更就能被别的事务看到，可能出现的问题有脏读/不可重复读/幻读。
读操作默认不加锁，但是写操作是有锁的，而且是行级排他锁，具体分析/实验可以看这篇文章，详细分析
2. 读已提交(READ COMMITTED)：
一个事务只能读到其他事务已经提交过的更新，看不到未提交的更新，可能出现不可重复读/幻读。
在SQL语句开始执行前以所有已提交的数据创建一个快照，这样看到的数据都是已经提交了的数据。
3. 可重复读(REPEATABLE READ)：
该隔离级别指一个事务中进行两次或多次同样的对于数据内容的查询，得到的结果与数据条数是一样的，是数据库的默认隔离级别。
为了解决不可重复读，MySQL 采用了 MVVC (多版本并发控制) 的方式。 事务开始执行的时候，会创建一个快照，之后他看到的一直就会是这个视图，直到事务提交，但是这种方式并不能解决幻读。
使用间隙锁解决幻读。
4. 串行化(序列化读、SERIALIZABLE )：
意思是说这个事务执行的时候不允许别的事务并发写操作的执行完全串行化的读，只要存在读就禁止写，但可以同时读。这是事务隔离的最高级别，虽然最安全最省心，但是效率太低，一般不会用。
5. MVCC
MVCC
MVCC，全称 Multi-Version Concurrency Control ，即多版本并发控制。它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的，所以我们先来看看这个三个 point 的概念。
隐式字段
每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID 等字段
- 最近修改(修改/插入)事务 ID
DB_TRX_ID，记录创建这条记录/最后一次修改该记录的事务 ID - 回滚指针
DB_ROLL_PTR，指向这条记录的上一个版本（存储于 rollback segment 里） - 隐含的自增 ID（隐藏主键）
DB_ROW_ID，如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引
实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了
如上图，DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID ,而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo日志，指向上一个旧版本
undo日志
undo log 主要分为两种：
- insert undo log
代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃 - update undo log
事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除
purge
从前面的分析可以看出，为了实现 InnoDB 的 MVCC 机制，更新或者删除操作都只是设置一下老记录的 deleted_bit，并不真正将过时的记录删除。
为了节省磁盘空间，InnoDB 有专门的 purge 线程来清理 deleted_bit 为 true 的记录。为了不影响 MVCC
的正常工作，purge 线程自己也维护了一个read view（这个 read view 相当于系统中最老活跃事务的 read view）;如果某个记录的 deleted_bit 为 true ，并且 DB_TRX_ID 相对于 purge 线程的 read view。
可见，那么这条记录一定是可以被安全清除的。
对 MVCC 有帮助的实质是 update undo log ，undo log 实际上就是存在 rollback segment 中旧记录链，它的执行流程如下：
比如一个有个事务插入 persion 表插入了一条新记录，记录如下，name 为 Jerry , age 为 24 岁，隐式主键是 1，事务 ID和回滚指针，假设为 NULL
现在来了一个事务 1对该记录的 name 做出了修改，改为 Tom
- 在事务 1修改该行(记录)数据时，数据库会先对该行加排他锁
- 然后把该行数据拷贝到 undo log 中，作为旧记录，既在 undo log 中有当前行的拷贝副本
- 拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务 ID 为当前事务 1的 ID, 我们默认从 1 开始，之后递增，回滚指针指向拷贝到 undo log 的副本记录，既表示我的上一个版本就是它
- 事务提交后，释放锁
又来了个事务 2修改person 表的同一个记录，将age修改为 30 岁
- 在事务2修改该行数据时，数据库也先为该行加锁
- 然后把该行数据拷贝到 undo log 中，作为旧记录，发现该行记录已经有 undo log 了，那么最新的旧数据作为链表的表头，插在该行记录的 undo log 最前面
- 修改该行 age 为 30 岁，并且修改隐藏字段的事务 ID 为当前事务 2的 ID, 那就是 2 ，回滚指针指向刚刚拷贝到 undo log 的副本记录
- 事务提交，释放锁
从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该 undo log 的节点可能是会 purge 线程清除掉，向图中的第一条 insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）
Read View 读视图
说白了 Read View 就是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大)
所以我们可以理解为 Read View 主要是用来做可见性判断的，即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
6. 间隙锁
危害
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，如果要更新的是记录之间的“间隙”，那么行锁就无法满足了。
数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。InnoDB引入了新的锁，也就是间隙锁(Gap Lock)。顾名思义，间隙锁，锁的就是两个值之间的空隙。
例如表t，初始化插入了6个记录，这就产生了7个间隙。
当执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的6个记录加上了行锁(全表扫描)，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。跟行锁有冲突关系的是“另外一个行锁”，与行锁不同的是，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作，间隙锁之间不存在冲突关系。
这里session B并不会被堵住。因为表t里并没有c=7这个记录，无法加行锁读写锁，因此session A加的是间隙锁(5,10)。而session B也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但它们之间是不冲突的。
**间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。**也就是说，表t初始化以后，如果用select * from t for update 要把整个表所有记录锁起来，就形成了7个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
把间隙锁记为开区间，把next-key lock记为前开后闭区间，这个supremum存在是因为+∞是开区间。实现上，InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合说的“都是前开后闭区间”。
缺点：
1.死锁
例如：任意锁住一行，如果这一行不存在的话就插入，如果存在就更新它的数据，代码如下：
begin;
select * from t where id=9 for update;
/*如果行不存在*/ insert into t values(N,N,N);
/*如果行存在*/ update t set d=N set id=N;
commit;而这个逻辑一旦有并发，就会碰到死锁：
session A 执行select * from t where id=9 for update; 语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10);
session B 执行 select * from t where id=9 for update; 语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
session B 试图插入一行(9,9,9)，被session A的间隙锁挡住了，只好进入等待；
session A 试图插入一行(9,9,9)，被session B的间隙锁挡住了。
至此，两个session进入互相等待状态，形成死锁。当然，InnoDB的死锁检测马上就发现了这对死锁关系，让session A的insert语句报错返回了。
间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。
数据表：注意没有id = 2
id name
1 a
3 a
4 a
5 a执行select id,name where id >= 1 and id <=5; 后执行insert into (id,name) values (2,'a');这一线程会被堵塞，因为2对应的记录行被间隙锁锁住了。
在session1事务没有commit之前，一直被锁住，其它session无法操作。
7. 按照作用划分锁
共享锁（Share locks简记为S锁）：
共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。如果事务A对数据B加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。
SELECT … LOCK IN SHARE MODE; 在查询语句后面增加 LOCK IN SHARE MODE ，MySQL就会对查询结果中的每行都加读锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请读锁，否则会被阻塞。其他线程也可以读取使用了读锁的表，而且这些线程读取的是同一个版本的数据。
排它锁（Exclusivelocks简记为X锁）：
排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。
SELECT … FOR UPDATE; 在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加写锁，当没有其他线程对查询结果集中的任何一行使用锁时，可以成功申请写锁，否则会被阻塞。另外成功申请写锁后，也要先等待该事务前的读锁释放才能操作。
对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；
对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。
共享锁：SELECT … LOCK IN SHARE MODE; 排他锁：SELECT … FOR UPDATE
意向锁（简记为U锁/更新锁）：
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁的作用是用来预定要对此对象施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的对象将要被更新时，则升级为X锁，主要是用来防止死锁的。
因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为排它锁，然后再执行修改操作。这样如果同时有两个或多个事务同时对一个对象申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为排它锁。这些事务都不会释放共享锁而是一直等待对方释放，这样就造成了死锁。如果一个数据在修改前直接申请意向锁，在数据修改的时候再升级为排它锁，就可以避免死锁。
间隙锁（GAP Key）：
见上文。
8. 按照范围划分锁
1.全局锁
全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL) 。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是：做全库逻辑备份。在备份过程中整个库完全处于只读状态。如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。
加全局锁不太好，但是如果不加，可能会引起数据不一致问题，例如两个表，账户和课程表，扣除余额后添加课程，先备份课程，后备份余额，可能会出现扣钱没课的情况。也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。
2.表级锁
MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
元数据锁
MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。可以想象一下，如果一个查询正在遍历一个 表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。所以，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
表锁
使用lock tables tableName read;为表加上读锁，也可以使用lock tables t1 read,t2 write;这种方式一次性为不同的表加上不同的锁。
使用unlock tables;解锁。也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
如果在某个线程A中执行 lock tables t1 read, t2 write ;（t1加读锁，t2加写锁）这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行 unlock tables 之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。
特点：MyISAM与Innodb都有，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
例子：
读锁（共享锁）
下面是一个加锁的例子。
当前session加上表的读锁后：
当前session：
只能读取加了锁的表，不能修改表，也不能读别的表。
其他session：
可以读别的表，可以读加了锁的表，但是不能写，写时会阻塞，直到这个表的读锁被释放。
写锁（排它锁）
session1：
session2：
当前session加上表的读锁后：
当前session：
可以对加了锁的表进行读写，但是不能对其他表做任何操作。
其他session：
可以读取别的表，不能读写加了锁的表。
3. 行锁
行锁只有InnoDB支持，特点是开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB默认对update，delete，insert加排他锁，select语句默认不加锁。
手动加共享锁的方式是 select…lock in share mode.
手动加排它锁的方式是 select…for update
4. 死锁与死锁检测
1. 产生死锁
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。
2. 解决死锁
当出现死锁以后，有两种策略：
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。默认值是50s。
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on，表示开启这个逻辑，innodb_deadlock_detect默认值是on。
如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是，把这个时间设置成一个很小的值，当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？超时时间设置太短的话，会出现很多误伤。
正常情况下还是要采用第二种策略，但他是需要额外资源的。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这个时间复杂度是O(n)，在大量请求的情况下会导致CPU爆满。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。就算最终检测的结果是没有死锁，那也需要消耗大量的CPU资源。怎么解决由这种热点行更新导致的性能问题呢？
- 如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。
- 控制并发度：比如同一行同时最多只有10个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。在客户端做并发控制不太可行，因为无法控制客户端的数量，如果有600个客户端，这样即使每个客户端控制到只有5个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到3000。所以并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现，基本思路就是，对于相同行的更新， 在进入引擎之前排队（削峰）。这样在InnoDB内部就不会有大量的死锁检测工作了。
- 也可以考虑通过将一行改成逻辑上的多行来减少锁冲突。
3. 查看死锁
出现死锁后，执行 show engine isnnodb status 命令得到的信息中有一节LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。
这个结果分成三部分：
(1) TRANSACTION，是第一个事务的信息；
(2) TRANSACTION，是第二个事务的信息；
WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
第一个事务的信息中：
- WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
- index c of table test.t，说明在等的是表t的索引c上面的锁；
- Record lock说明这是一个记录锁；
- lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中；
- n_fields 2表示这个记录是两列，也就是字段c和主键字段id,因为走的是c索引查询
- 0: len 4; hex 0000000a;是第一个字段，也就是c。值是十六进制a，也就是10；
- 1: len 4; hex 0000000a;是第二个字段，也就是主键id，值也是10；
第一个事务信息就只显示出了等锁的状态，在等待(c=10,id=10)这一行的锁。
既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来，可以从第二个事务的信息中推导出来。
第二个事务显示的信息要多一些：
- HOLDS THE LOCK(S) 用来显示这个事务持有哪些锁；
- index c of table test.t 表示锁是在表t的索引c上；
- hex 0000000a和hex 00000014表示这个事务持 有c=10和c=20以及id=10和id=20的录锁；
- WAITING FOR THIS LOCK TO BE GRANTED，表示在等(c=5,id=5)这个记录锁
所以，“lock in share mode”的这条语句，持有c=5的记录锁，在等c=10的锁；“for update”这个语句，持有c=20和c=10的记录锁，在等c=5的记录锁。因此导致了死锁。
由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以InnoDB选择了回滚成本更小的lock in share mode语句，来回滚。
