事务的隔离级别
隔离级别定义的是并发事物之间的可⻅性和影响程度，为什么要有事物隔离级别?
在理想的情况下，事务之间是完全隔离的，这样就可以避免出现脏读，不可重复读，幻读等问题，且事物隔离级别越⾼，在并发下会产⽣的问题就越少，但同时付出的性能消耗也将越⼤。因此很多时候必须在并发性和性能之间做⼀个权衡，针对这四种隔离级别，应该根据具体的业务来取舍，如果某个系统的业务⾥根本就不会出现重复读RR的场景，完全可以将数据库的隔离级别设置为 RC，这样可以最⼤程度的提⾼数据库的并发性
通过命令查看事务的隔离级别：select @@tx_isolation;
事务的ACID特性：
- 原子性(Atomicity)
- 一致性(Consistency)
- 隔离性(Isolation)
- 持久性(Durability)
事务的隔离级别：
- 读未提交（READ UNCOMMITTED）
- 读已提交（READ COMMITTED）
- 可重复读（REPEATABLE READ）
- 串行化（SERIALIZABLE）
事务并发引起的问题：
- 脏读（DIRTY READ）
- 不可重复读（UNREPEATABLE READ）
- 幻读（PHANTOM READ）
其中：
读未提交：.可以读取未提交的记录，会出现脏读，幻读， 不可重复读，所有并发问题都可能遇到
读已提交：事务中只能看到已提交的修改，不会出现脏读，但是会出现幻读，不可重复读（⼤多数数据库的默认隔离级别都是 RC，如Oracle）
可重复读：解决了不可重复读问题，但是仍然存在幻读问题.（MySQL的InnoDb 默认是 RR，MySQL通过MVCC+GAP间隙锁解决了幻读）
序列化：“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时 候，后访问的事务必须等前⼀个事务执⾏完成,没有并发问题.
SQL 规范中定义的四种隔离级别，分别是为了解决事务并发时可能遇到的问题，⾄于如何解决，实现⽅式是什么，规范中并没有严格定义。锁作为最简单最显⽽易⻅的实现⽅式被应⽤在很多数据库中。除了锁，实现并发问题的⽅式还有时间戳，多版本控制等。这些也可以称为⽆锁的并发控制。
基于锁的隔离级别并发控制（Lock-Based Concurrent Control，简写 LBCC）
锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。InnoDB存储引擎会在行级别上对表数据上锁，也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介入。对于MyISAM引擎，其锁是表锁设计。并发情况下的读没有问题，但是并发插人时的性能就要差一些了，若插入是在“底部”，MyISAM存储引擎还是可以有一定的并发写入操作。
Lock和Latch区别
- latch一般称为闩锁（轻量级的锁），要求锁定的时间必须非常短。在InnoDB存储引擎中，latch又可以分为mutex(互斥量）和rwlock(读写锁）。目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。
- lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务 commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同），lock是有死锁机制的。
基于锁的隔离级别并发控制
InnoDB存储引擎中的行级锁的类型
- 共享锁（Share locks，S锁，⼜叫读锁)：允许事务读一行数据。加了共享锁的记录，其他事务也可以读，但不能写；
- 排他锁（Exclusive locks，X锁，⼜叫写锁)：允许事务删除或更新一行数据。加了排它锁的记录，其他事务既不能读，也不能写。
如果一个事务T1已经获得了行r的共享锁，那么事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容（Lock Compatible)，但若事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁，这种情况称为锁不兼容。表6-3显示了共享锁和排他锁的兼容性。
| 写锁/读锁互斥关系 | X | S |
| X | ❌ | ❌ |
| S | ❌ | ✅ |
可以看出：
- X锁和任何锁（不管是S锁还是X锁）都不兼容
- S锁只和S锁兼容
另外，除了行锁外，InnoDB也支持表锁（数据库级别的），⾏锁只锁⼀行或某⼏行记录，对其它行的操作不受影响，表锁会锁住整张表，所有对这个表的操作都受影响。
通过对锁的类型（读锁还是写锁），锁的粒度（行锁还是表锁），持有锁的时间（临时锁：语句执行完后就释放锁和持续锁：事物结束才释放锁）合理的进行组合，就可以实现四种不同的隔离级别。（理论模型，认识不同的锁和互斥关系，可以实现不同的事物隔离级别)
- 读未提交：通过对写操作加 “持续X锁”，对读操作不加锁实现；(写操作加锁是为了防⽌出现回滚覆盖，也叫做第⼀类更新丢失，数据库任何隔离级别下都不允许出现），事务读不阻塞其他事务读和写，事务写阻塞其他事务写但不阻塞读；
- 读已提交：通过对写操作加 “持续X锁”，对读操作加 “临时S锁” 实现； 不会出现脏读；事务读不会阻塞其他事务读和写，事务写会阻塞其他事务读和写；
- 可重复读：通过对写操作加 “持续X锁”，对读操作加 “持续S锁” 实现；事务读会阻塞其他事务写但不阻塞读，事务写会阻塞其他事务读和写；
- 序列化：为了解决幻读问题，⾏级锁做不到，需使⽤表级锁。
其中：
第一类更新丢失（回滚丢失，Lost update）：如A事务撤销时，把已经提交的B事务的更新数据覆盖了。这种错误可能造成很严重的问题，通过下面的账户取款转账就可以看出来：
第二类丢失更新(覆盖丢失/两次更新问题，Second lost update) ：A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失
基于MVCC的隔离级别并发控制(Multi-Version Concurrent Control，简写MVCC)
LBCC最⼤的问题是它只实现了并发的读读，对于并发的读写还是冲突的，写时不能读，读时不能写。当读写操作都很频繁时，数据库的并发性将大大降低，针对这种场景，MVCC 技术应运而生，MVCC 的全称叫做 Multi-Version Concurrent Control（多版本并发控制）
InnoDB 会为每⼀⾏记录增加几个隐含的“辅助字段”（ROWID（不存在主键或唯一索引）、事物ID、回滚指针）。事务在更新⼀条记录时会将其拷⻉⼀份生成这条记录的⼀个原始拷⻉，写操作同样还是会对原记录加锁，但是读操作会读取未加锁的新记录，即通过维持一个数据的多个版本，使得读写操作没有冲突，保证了读写并行。要注意的是，生成的新版本其实就是 undo log，它也是实现事务回滚的关键技术。
MVCC的实现原理
在数据库中的实现，为每行记录添加 3个隐式字段，字段名：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID
- DB_TRX_ID：6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
- DB_ROLL_PTR：7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
- DB_ROW_ID：6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
- 实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了
快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术，由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control,MVCC)
从上图中可以看出，每次修改都会新增一个undo log，各个undo log之间通过指针连接，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录，这里也侧面说明了长事务的性能影响，不仅占用更多的锁资源，还会产生大量的回滚日志。
快照读和当前读
MySQL通过MVCC来实现RR和RC隔离级别下的读写并行，它们读取的都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read）。也叫一致性的非锁定读（consistent nonlocking read)，除了快照读 ， MySQL 还提供了另⼀种读取⽅式：当前读（Current Read），有时候⼜叫做 锁定读（Locking Read） 或者 阻塞读（Blocking Read），这种读操作读的不再是数据的快照版本，而是数据的最新版本，并会对数据加锁（当前读在 RR 和 RC 两种隔离级别下的实现也是不⼀样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题）
一致性的非锁定读（consistent nonlocking read)
即快照读，是指 InnoDB存储引擎通过行多版本控制（mcvv)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE或UPDATE操作（INSERT / UPDATE / DELETE操作会加 X 锁），这时读取操作不会去等待行锁的释放。而是会去读取行的一个快照数据。快照数据是指该行的之前版本的数据，是通过undo段来实现的，而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。不需要上锁，因为没有事务需要对历史的数据进行修改操作。
不同事务隔离级别下的非锁定读（快照读）
在不同事务隔离级别下，并不是在每个事务隔离级别下都是采用非锁定的一致性读。在 Read Uncommit 隔离级别下，每次都是读取最新版本的数据⾏，所以不能⽤ MVCC 的 多版本，⽽Serializable 隔离级别每次读取操作都会为记录加上读锁，也和 MVCC 不兼 容，所以只有 RC 和 RR 这两个隔离级别才有 MVCC ，此外，即使都是使用非锁定的一致性读，但是对快照数据的定义也各不相同
在事务隔离级别 READ COMMITTED和REPEATABLE READ(InnoDB存储引擎的默认事务隔离级别）下，InnoDB存储引擎使用非锁定的一致性读。但是对于快照数据的定义却不相同
- 在READ COMMITTED事务隔离级别下，非一致性读总是读取被锁定行的最新一份快照数据。
- 在REPEATABLE READ事务隔离级别下，非一致性读总是读取事务开始时的行数据版本
如下：MySQL数据库的会话A执行SQL如下：
Session A
mysq1>BEGIN;
Query OK, O rows affected (0.00 sec)
mysq1> SELECT * FROM parent WHERE id = 1会话A：显式地开启了一个事务，并读取了表parent中id为1的数据，但是事务并没有提交。此时再开启另一个会话B，模拟并发的情况，会话B做如下的操作：
Session B
mysql>BEGIN;
Query OK, O rows affected (0.00 sec)
mysq1> UPDATE parent SET id=3 WHERE id=1
Query OK,1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
会话B中将事务表parent中id为1的记录UPDATE为id=3，事务同样没有提交，这样id=1的行其实加了一个X锁。
此时在会话A中再次读取id为1的记录，根据InnoDB存储引擎的特性，即在READ COMMITTED和REPEATETABLE READ的事务隔离级别下会使用非锁定的一致性读。所以读到的都是快照数据，读到id=1这个记录。
接着会话B提交事务后，这时在会话A中再次读取id为1的记录，在READ COMMITTED和REPEATABLE事务隔离级别下得到结果就不一样了：
- 对于READ COMMITTED的事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照（fresh snapshot)，在上述例子中，因为会话B已经提交了事务，所以READ COMMITTED事务隔离级别下会读到最新的结果为Empty set；
- 而对于REPEATABLE READ的事务隔离级别，总是读取事务开始时的行数据版本，因此读到的还是id=1
在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见；而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View，这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因
总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同，
Read View(读视图)
Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)，所以我们知道 Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本
根据上图可以看出，存在一个数值列表read_view_list，用来维护Read View生成时刻系统正活跃的事务ID，其中：tmin是记录read_view_list列表中事务ID最小的ID，tmax记录的是read_view_list列表中事务ID最大的ID，可见性算法主要如下：
- 首先比较DB_TRX_ID < tmin， 如果小于，则当前事务能看到DB_TRX_ID 所在的记录，
- 如果大于等于进入下一个判断，接下来判断 DB_TRX_ID 大于 tmax , 如果大于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见
- 如果小于则进入下一个判断，判断DB_TRX_ID 是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表Read View生成时刻，当前这个事务还在活跃，还没有Commit，当前事务修改的数据是看不见的；如果不在，则说明，当前这个事务在Read View生成之前就已经Commit了，当前事务修改的数据是是能看见的
undo日志
主要分为两种：
- 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可见，那么这条记录一定是可以被安全清除的。
一致性锁定读（当前读）
在默认配置下，即事务的隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read)操作：
- SELECT···FOR UPDATE：对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。
- SELECT···LOCK IN SHARE MODE：对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。
注意：当事务提交了，锁也就释放了。因此在使用上述两句SELECT锁定语句时，务必加上BEGIN、START TRANSACTION或者 SET AUTOCOMMIT=0
常见锁类型
- 按照锁类型：行锁 、表锁
表锁由 MySQL 服务器实现，行锁由存储引擎实现 。InnoDB支持行锁，而 MyISAM 存储引擎只能使用表锁；表锁指的是对⼀整张表加锁，⼀般是 DDL 处理时使⽤，也可以自己在 SQL 中指定。
表锁：开销小，加锁快，不会出现死锁，锁的粒度大，发生锁冲突的概率高，并发度低
行锁指的是锁定某⼀行数据或某几行，或行和行之间的间隙。行锁的加锁方法比较复杂，但是由于只锁住有限的数据，对于其它数据不加限制，所以并发能力强，通常都是用行锁来处理并发事务。
行锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，并发度高
表锁的使用
表锁使⽤的是⼀次封锁技术，在会话开始的地方使用 lock 命令将后面所有要用到的表加上锁，在锁释放之前，我们只能访问这些加锁的表，不能访问其他的表，最后通过unlock tables 释放所有表锁。表锁也分为读锁和写锁，如下：
mysql root @localhost :study> lock tables vote_record read,t_item write; Query OK, 0 rows affected
Time: 0.006s
mysql root @localhost :study> select id,user_id,vote_id from vote_record limit 1;
+----+----------------------+---------+
| id | user_id | vot e_id |
+----+----------------------+---------+
| 1 | a56351fd5b013bd0bf 3f | 985 |
+----+----------------------+---------+
1 row in set Time: 0.015s
mysql root @localhost :study> update vote_record set vote_id=905 where id=1; (1099, "Table 'vote_record' was locked with a READ lock and can't be updated") mysql root @localhost :study> unlock tables;表的读锁：lock tables table_name read
- 持有读锁的会话可以读表，但不能写表；
- 允许多个会话同时持有读锁；其他会话就算没有给表加读锁，也是可以读表的，但是不能写表；
- 其他会话申请该表写锁时会阻塞，直到读锁释放。
表的写锁：lock tables table_name write
- 持有写锁的会话既可以读表，也可以写表
- 只有持有写锁的会话才可以访问该表，其他会话申请该表的读锁或写锁会被阻塞，直到锁释放；
其他会话⽆论申请该表的读锁或写锁，都会阻塞，直到锁释放
MySQL 表锁的加锁规则如下：
在用 LOCK TABLE对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；COMMIT或ROLLBACK并不能释放用LOCK加的表级锁，必须用UNLOCK TABLES释放表锁。UNLOCK TABLES会隐含地提交事务
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;表锁的释放规则如下：
- 使⽤ UNLOCK TABLES 语句可以显示释放表锁
- 如果会话在持有表锁的情况下执⾏ LOCK TABLES 语句，将会释放该会话之前持有的锁
- 如果会话在持有表锁的情况下执⾏ START TRANSACTION 或 BEGIN 开启⼀个事务，将会释放该会话之前持有的锁
- 如果会话连接断开，将会释放该会话所有的锁
行锁的使用
MySQL 行锁仅适用于InnoDB存储引擎，加锁规则如下：
- SELECT ... LOCK IN SHARE MODE：加 S 锁
- SELECT ... FOR UPDATE：加 X 锁
- 常⻅的增删改（INSERT、DELETE、UPDATE）语句会⾃动对操作的数据行加Ｘ锁，
- 对于普通SELECT语句，InnoDB不会加任何锁
其中：SELECT ... LOCK IN SHARE MODE 语句加的是读锁，SELECT ... FOR UPDATE 语句加的是写锁。在 MySQL 中，行锁是加在索引上的，MySQL 有两种索引类型：主键索引（Primary Index）和非主键索引（Secondary Index，⼜称为⼆级索引、辅助索引，细分又可以分为唯⼀索引、普通索引）
InnoDB行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。注意事项如下：
- 在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。
- 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以即使是访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。
- 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
- 即便在条件中使用了索引字段，但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
测试用表和数据
Table | student
Create Table | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(20) COLLATE utf8_bin NOT NULL COMMENT '学号',
`name` varchar(20) COLLATE utf8_bin NOT NULL COMMENT '姓名',
`age` int(11) NOT NULL COMMENT '年龄',
`score` int(11) NOT NULL DEFAULT '0' COMMENT '学分',
PRIMARY KEY (`id`),
UNIQUE KEY `student_stu_no_uindex` (`stu_no`),
KEY `student_name_index` (`name`),
KEY `student_age_index` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
mysql root@localhost:study> select * from student;
+-----+--------+------+-----+-----+-----+
| id | stu_no | name | age | score |
+-----+--------+------+-----+-----+-----+
| 1 | S0001 | Bob | 25 | 34 |
| 3 | S0002 | Tom | 23 | 50 |
| 5 | S0003 | Eric | 26 | 27 |
| 10 | S0004 | Rain | 21 | 48 |
| 20 | S0005 | Tom | 23 | 8 |
| 30 | S0006 | Kobe | 19 | 28 |
| 40 | S0007 | Rose | 25 | 52 |
| 41 | S0008 | Jim | 26 | 19 |
| 42 | S0009 | Zoom | 22 | 90 |
| 50 | S0010 | Tom | 24 | 81 |
+----+--------+------+-----+------+-----+1、主键加锁
update student set score=35 where id=1InnoDb存储引擎会在id=1这个主键索引上加一把X锁
2、普通索引加锁
update student set score =35 where name ='Rose'
InnoDb存储引擎会在name='Rose'这个索引上加一把X锁，同时会通过name='Rose'这个二级索引定位到id=40这个主键索引，并在id=40这个主键索引上加一把X锁
3、多条记录加锁
update student set score=28 where name ='Tom'当UPDATE语句被发给Mysql之后，Mysql Server会根据Where条件读取第一条满足条件的记录，然后InnoDB引擎将第一条记录返回并加锁（Current read），待Mysql Server收到这条加锁的记录之后们会在发起一个UPDATE请求，更新这条记录，一条操作记录完成在读取下一条记录，直到读完所有满足条件的记录，Mysql在操作多条记录时InnoDB与Mysql Sever的交互式一条一条进行的，加锁也是一条一条进行的，先对一条满足条件的记录加锁，返回给Mysql，做DML操作后，继续下一条，直至读取完毕
可以根据多个事务的读取进行验证
行锁的算法
InnoDB存储引擎有3种行锁的算法，其分别是：
- Record Lock：单个行记录上的锁
- Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
- Next-Key Lock：Gap Lock+Record Lock,锁定一个范围，并且锁定记录本身
Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
Next-Key Lock是结合了Gap Lock和 Record Lock的一种锁定算法，InnoDB对于行的查询都是采用这种锁定算法。例如一个索引(非唯一)有10、13和20这三个值，那么该索引可能被Next-Key Locking的区间为：（－∞ ，10]、（10，13]、（13，20]、（20，+∞)
Next-Key Lock设计的目的是为了解决 Phantom Problem（幻读），锁定的不是单个值，而是一个范围，但是当查询的索引是唯一属性时，InnoDB存储引擎会对Next-Key Lock 进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。但是如果不是唯一索引，而是普通的辅助索引，那么用的是Next-Key Lock，可以看出Gap Lock的作用就是阻止多个事务将记录插入到同一范围内，而这也是导致幻读的产生
如下SQL：a是主键，会话A对a=5进行X锁定，锁定的仅仅是a=5 这个值
CEREATE TABLE t(a int PRIMARY KEY(a));
INSERT INTO t select 1
INSERT INTO t select 2
INSERT INTO t select 5
SESSION A
BEGIN
SELECT * FROM t WHERE a =5 FOR UPDATE
SESSION B
BEGIN
INSERT INTO t SELECT 4
COMMIT
#成功，不需要等待
SESSION A
COMMIT如下SQL：a是主键，b是辅助索引，会话A对b=3进行X锁定时候，由于有两个索引，需要分别锁定，对于聚集索引，仅对于a=5的索引加Record Lock，对于辅助索引，加上Next-key Lock，锁定的范围是（1,3]、（3,6）
CEREATE TABLE t(a int,b int PRIMARY KEY(a), KEY(b));
INSERT INTO t select 1,1
INSERT INTO t select 3,1
INSERT INTO t select 5,3
INSERT INTO t select 7,6
SESSION A
BEGIN
SELECT * FROM t WHERE b =5 FOR UPDATE
SESSION B
BEGIN
SELECT * FROM Z WHERE a=5 LOCK IN SHARE MODE #阻塞
INSERT INTO t SELECT 4,2 #阻塞
INSERT INTO t SELECT 6,5 #阻塞
INSERT INTO t SELECT 8,6 #成功
可以通过以下两种方式来显式地关闭 Gap Lock:
- 将事务的隔离级别设置为READ COMMITTED
- 将参数 innodb locks unsafe for binlog设置为1
在上述的配置下，除了外键约束和唯一性检查依然需要的Gap Lock，其余情况仅使用Record Lock进行锁定，
对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，故InnoDB存储引擎依然使用Next-Key Lock进行锁定。
意向锁
InnoDB存储引擎支持多粒度锁定，允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock)，意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁，比如：如果需要对页上的记录r进行上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。如果任何一个加意向锁操作失败，也会导致最终的记录r加X锁失败。
InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了：在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：
- 意向共享锁（IS Lock)：事务想要获得一张表中某几行的共享锁
- 意向排他锁（IX Lock)：事务想要获得一张表中某几行的排他锁
读写意向锁
表锁锁定了整张表，而行锁是锁定表中的某条记录，他们锁定的范围存在交集，因此表锁和行锁是存在冲突的，如某个表中有10000行记录，其中有一题条记录加了X锁，如果这个时候需要对表添加表锁，为了判断是否可以加这个表锁，系统需要便利表中的10000行记录，看看是否有某些记录被加了行锁，如果有则不允许加表锁，显然这种方式的效率极低，所以引入意向锁
意向锁是表级锁，也可分为读意向锁和写意向锁，当事务试图读或者写一条记录时，会现在表上加上意向锁，然后再要操作的记录上添加读锁或者写锁，这样判断表是否有记录行锁就非常简单，只需要看表中是否存在意向锁即可，意向锁之间是不会产生冲突的，他只会阻塞表级读锁或者表级写锁，另外意向锁也不会和行锁冲突，行锁只会与行锁产生冲突
解决 Phantom Problem
在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking 机制来避免 Phantom Problem(幻像问题）。
幻读 是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。
阻塞
因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其是为了确保事务可以
并发且正常地运行。
在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来控制等待的时间（默认是50秒），innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是OFF,代表不回滚）。参数innodb_lock_wait_timeout是动态的，可以在MySQL数据库运行时进行调整：
mysq1> SET @@innodb_lock_wait_timeout=60;
Query OK,O rows affected (0.00 sec)而innodb_rollback_on_timeout是静态的，不可在启动时进行修改，如：
mysq1> SET @@innodb_rollback_on_timeout=on;
ERROR 1238 (HY000):Variable \'innodb_rollback_on_timeout\' is a read only variable当发生超时，MySQL数据库会抛出一个1205的错误，如：
mysql> BEGIN;
Query OK, O rows affected (0.00 sec)
mysq1> SELECT*FROM t WHERE a =1 FORUPDATE;
ERROR 1205 (HY000):Lock wait timeout exceeded; try restarting transaction需要牢记的是，在默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。其实InnoDB存储引擎在大部分情况下都不会对异常进行回滚。
死锁
死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象，若无外力作用，事务无法推进下去
解决方案：
- 回滚，重新开始事务
- 超时，两个事务相互等待，当一个等待时间超过某个阈值就会回滚，另外一个事务得以进行，通过innodb_lock_wait_timeout设置超时时间
- wait-for graph：等待图检测死锁
死锁案列：
SESSION A
BEGIN
select * from t where a =1 for update
SESSION B
BEGIN
select * from t where a =2 for update
SESSION A
select * from t where a =2 for update
SESSION B
select * from t where a =1 for update
查看表级锁争用情况
执行SQL:mysql> show status like ‘table%’;
mysql> show status like 'table%';
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Table_locks_immediate | 20708 |
| Table_locks_waited | 0 |
+----------------------------+-----------+
Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而发生等待的次数；
如果Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。
优化表级锁定
前面说过MyISAM的插入如果是尾部的话，有一定的并发度，是因为可以设置可并发插入：concurrent_insert=2；MyISAM存储引擎有一个控制是否打开Concurrent Insert（并发插入）功能的参数选项：concurrent_insert，取值范围为0，1，2。
- concurrent_insert=0，不允许并发插入。
- concurrent_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这是MySQL的默认设置；
- concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；
所以，我们可通过设置concurrent_insert=2，同时定期在系统空闲时段执行optimize table tableName语句来整理空间碎片，收回因删除记录而没有真正释放的空间，从而提高并发。optimize参考：mysql中OPTIMIZE TABLE的作用及使用
合理设置读写优先级
MyISAM存储引擎默认是写优先级大于读优先级。即使是写请求后到，写锁也会插到读锁请求之前。
但是，有时像修改文章点击数 操作是不那么重要的，我们希望的是读更快，此时我们可以这样：
UPDATE LOW_PRIORITY article SET click_num=134 WHERE id = 823LOW_PRIORITY使得系统认为update操作优化级比读操作低，如果同时出现读操作和上面的更新操作，则优先执行读操作。
MySQL提供了几个语句调节符，允许你修改它的调度策略：
- LOW_PRIORITY关键字应用于：DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。
- HIGH_PRIORITY关键字应用于：SELECT、INSERT语句。
- DELAYED(延迟)关键字应用于：INSERT、REPLACE语句。
如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，那么可能使用low-priority-updates选项来启动服务器。然后可通过使用insert HIGH_PRIORITY table.....来把个别我们希望的INSERT语句提高到正常的写入优先级。
InnoDB存储引擎锁相关表
InnoDB在INFORMATION_SCHEMA架构下添加了表:
- INNODB_TRX：查看事务运行状态和信息
- INNODB_LOCKS：查看表中锁的一些信息
- INNODB_LOCK_WAITS：查看表中锁引起的等待的信息
通过这三张表，用户可以更简单地监控当前事务并分析可能存在的锁问题。
INNODB_TRX表结构如下：其由8个字段组成。
| 字段名 | 说明 |
| trx_id | InnoDB 存储引擎内部唯一的事务 ID |
| trx_state | 当前事务的状态 |
| trx_started | 事务的开始时间 |
| trx_requested_lock_id | 等待事务的锁ID。如trx_state的状态为LOCK WAIT，那么该值代表当前的事务等待之前事务占用锁资源的ID；若trx state不是LOCK WAIT，则该值为NULL |
| trx_wait_startd | 事务等待开始的时间 |
| trx_weight | 事务的权重，反映了一个事务修改和锁住的行数。在InnoDB存储引擎中，当发生死锁需要回滚时，InnoDB存储引擎会选择该值最小的进行回滚 |
| trx_mysql_thread_id | MySQL 中的线程ID,SHOW PROCESSLIST 显示的结果 |
| trx_query | 事务运行的SQL语句 |
如下SQL：可以看出730FEE的事务正在运行，7311F4的事务处理LOCK WAIT状态
mysq1>SELECT·FROM information_schema,INNODB_TRX;
*****1.row *****
trx_id:7311F4
trx_state: LOCK WAIT
trx_atarted:2010-01-04 10:49:33
trx_requested_lock_id:7311F4:96:3:2
trx_wait_started:2010-01-04 10:49:33
trx_weight:2
trx_mysql_thread_id:471719
trx_query: select * from parent lock in share mode
*****2.row *****
trx_id:730FEE
trx_state:RUNNING
trx_started:2010-01-04 10:18:37
trx_requested_lock_id:NULL
trx_wait_started:NULL
trx_weight:2
trx_mysql_thread_id:471718
trx_query: NULL
2 rows in set (0.00 sec)INNODB_LOCKS字段如下
| 字段名 | 说明 |
| lock_id | 锁的ID |
| lock_trx_id | 事务 ID |
| lock_mode | 锁的模式 |
| lock_type | 锁的类型，表锁还是行锁 |
| lock table | 要加锁的表 |
| lock_index | 锁住的索引 |
| lock space | 锁对象的 space id |
| lock page | 事务锁定页的数量。若是表锁，则该值为NULL |
| lock_rec | 事务锁定行的数量，若是表锁，则该值为NULL |
| lock_data | 事务锁定记录的主键值，若是表镇，则该值为NULL |
mysq1> SELECT·FRON Information_schena.INNODB_LOCKS
******1.rOW ******
1ock_id:7311F4:96:3:2
lock_trx_id:7311F4
lock_mode: s
1ock_type:RECORD
1ock_table:mytest.parent
lock_index:PRIMARY
1oek_epace:96
1ock_page: 3
lock_rec:2
lock_data:1
******2.rOW ******
1ock_id:730FEE19613:2
lock_trx_id:730FEE
1ock_mode:x
lock_type:RECORD
1ock_table:\'myteat.parent
1ock_indexi\'PRIMARY\'
lock_space:96
lock_page:3
lock_rec:2
lock_data:1
2 rows in set (0.00 sec)通过INNODB_LOCKS查看每张表的上锁情况，可以看出trx_id 730EE的事务向表parent加了一个X的行锁，7311F4的事务向表parent申请一个S行锁，lock_data都是1申请相同的资源
在通过表INNODB_LOCKS查看了每张表上锁的情况后，用户就可以来判断由此引发的等待情况了。当事务较小时，用户就可以人为地、直观地进行判断了。但是当事务量非常大，其中锁和等待也时常发生，这个时候就不这么容易判断。但是通过表INNODB_LOCK_WAITS,可以很直观地反映当前事务的等待。表INNODB_LOCK_WAITS由4个字段组成，如下：
| 字段 | 说明 |
| requcsting_trx_id | 申请锁资源的事务 ID |
| blocking_trx_id | 阻塞的事务 ID |
| requesting_lock_id | 申请的锁的 ID |
| blocking_trx_id | 阻塞的锁的ID |
mysq1>SELECT* FROM information_achema.INNODB_LOCK_WAITS\\G;
****1.row ****
requesting_trx_id:7311F4
requested_lock_id:7311F4:96:3:2
blocking_trx_id:730FEE
blocking_lock_id:730FEE:96:3:2
1 row in set (0.00 sec)通过上述的SQL语句，可以看到哪个事务阻塞了另一个事务。当然，这里只给出了事务和锁的ID，如果需要，用可以根据表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK WAITS得到更为直观的详细信息。
SELECT*
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schena.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id-w.requesting_trx_id;
扫一扫
687
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
