今日内容概述

1.InnoDB存储引擎的锁机制
2.多版本并发控制MVCC
3.MySQL事务隔离机制

今日内容详细

1.InnoDB存储引擎的锁机制

MyISAM和MEMORY采用表级锁(table-level locking)。

BDB采用页级锁(page-level locking)或表级锁，默认为页级锁。

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁（偏向于写）。

InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：

如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

行级锁以表级锁的使用区别

MyISAM 操作数据都是使用表级锁，MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。所以不会产生死锁，但是由于每操作一条记录就要锁定整个表，导致性能较低，并发不高。

InnoDB 与 MyISAM 的最大不同有两点：一是 InnoDB 支持事务；二是 InnoDB 采用了行级锁。也就是你需要修改哪行，就可以只锁定哪行。

在Mysql中，行级锁并不是直接锁记录，而是锁索引。InnoDB 行锁是通过给索引项加锁实现的，而索引分为主键索引和非主键索引两种

1、如果一条sql 语句操作了主键索引，Mysql 就会锁定这条语句命中的主键索引（或称聚簇索引）；

2、如果一条语句操作了非主键索引（或称辅助索引），MySQL会先锁定该非主键索引，再锁定相关的主键索引。

3、如果没有索引，InnoDB会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表级锁一样。

# 在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
1、在不通过索引条件查询的时候,InnoDB 的效果就相当于表锁
2、当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
3、由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以即便你的sql语句访问的是不同的记录行,但如果命中的是相同的被锁住的索引键，也还是会出现锁冲突的。
4、即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将锁住所有行，相当于表锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。

三种行锁的算法

InnoDB有三种行锁的算法，都属于排他锁：
1、Record Lock：单个行记录上的锁。
2、Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
3、Next-Key Lock：等于Record Lock结合Gap Lock，也就说Next-Key Lock既锁定记录本身也锁定一个范围，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock。

什么时候使用表锁

绝大部分情况下使用表锁，但在个别特殊事务中，也可以考虑使用表锁
1、事务需要更新大部分数据，表又较大
若使用默认的行锁,不仅该事务执行效率低(因为需要对较多行加锁,加锁是需要耗时的); 而且可能造成其他事务长时间锁等待和锁冲突; 这种情况下可以考虑使用表锁来提高该事务的执行速度
2、事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚
这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ。

行锁优化建议

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况，在着手根据状态量来分析改善；
show status like 'innodb_row_lock%';//查看行锁的状态

尽可能让所有数据检索都通过索引来完成， 从而避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

2.多版本并发控制MVCC

MySQL存储引擎实现的是基于多版本的并发控制协议---MVVCC
# 与MVCC相对的，是基于锁的并发控制

MVCC最大的好处是：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段几乎所有的RDBMS，都支持MVCC。

在MVCC并发控制中，读操作可以分为两类：快照读与当前读
快照读读取的是记录的可见版本（有可能是历史版本），不用加锁。
当前读，读取的是记录的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发修改这条记录

# 哪些操作属于快照读，哪些属于当前读？
快照读：简单的select操作，属于快照读，不加锁（也有例外）

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁
当前读读取记录的最新版本，并且读取之后还需要保证其他并发事务不能修改当前记录。
对读取的记录加锁，其中除了第一条语句，对读取记录加S锁（共享锁）外，其他的操作，都是加X锁（排他锁）

# MVCC原理
MVCC可以提供基于某个时间点的快照，使得对于事务来看，总是可以提供与事务开始时刻相一致的数据，而不管这个事务执行的时间有多长。所以在不同的事务看来，同一时刻看到的相同行的数据可能是不一样的，即一个行可能有多个版本

3.MySQL事务隔离机制

事务具有原子性、一致性、隔离性、持久性四大特性，而隔离性顾名思义指的就是事务彼此之间隔离开，多个事务在同时处理一个数据时彼此之间互相不影响，如果隔离的不够好就有可能会产生脏读、不可重复度、幻读等读现象，为此，隔离性总共分为四种级别：

由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读 、不可重复读 、幻读 这几类问题.

需要强调的是：我们确实可以采用提高事务的隔离级别的方式来解决脏读、不可重复读、幻读等问题，但与此同时，事务的隔离级别越高，并发能力也就越低。所以，还需要读者根据业务需要进行权衡。

未提交读(Read uncommitted)

未提交读(READ UNCOMMITTED)是最低的隔离级别。通过名字我们就可以知道，在这种事务隔离级别下，一个事务隔离级别下，一个事务可以读到另一个事务未提交的数据

# 原理：
未提交读的数据库锁情况（实现原理）
事务在读数据的时候并未对数据加锁。
事务在修改数据的时候只对数据增加行级共享锁。

# 案例：
第一步：设置事务的隔离级别
mysql> set tx_isolation='READ-UNCOMMITTED';

第二步：开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

第三步：修改数据
mysql> update student set age = 17 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

第四步：重新开启一个事务
mysql> set tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)
				
mysql> show variables like '%tx_isolation%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

第五步：在新的事务中查询刚刚修改过的数据
mysql> select * from student;
+----+------------+-----+
| id | name       | age |
+----+------------+-----+
|  1 | 大D妹妹    |  17 |

# 结论：
由此可以得出，隔离机制为未提交读时，一个事务修改的数据，另一个事务可以查看到上一个事务未提交的数据。

提交读(Read committed)

提交读(READ COMMITTED)也可以翻译成读已提交，通过名字也可以分析出，在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据。

提交读的数据库锁情况
事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。但是事务一旦提交，其他事务可以立即查看到已经提交了的数据。

案例：
第一步：设置事务的隔离级别
mysql> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%tx_isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

第二步：开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
			
第三步：修改数据并提交		
mysql> update student set age = 17 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
			
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
			
第四步：重新开启一个事务	
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
	
第五步：在新的事务中查询刚刚修改过的数据		
mysql> select * from student where id = 1;
+----+------------+-----+
| id | name       | age |
+----+------------+-----+
|  1 | 大D妹妹    |  17 |
+----+------------+-----+
1 row in set (0.00 sec)

# 结论：
由此可知，提交读事务隔离机制是当事务修改数据之后，并且提交，其他事务可以立即查看到已经提交了的数据。

可序列化(Serializable)

可序列化(Serializable)是最高的隔离级别，前面提到的所有的隔离级别都无法解决的幻读，在可序列化的隔离级别中可以解决。

我们说过，产生幻读的原因是事务一在进行范围查询的时候没有增加范围锁(range-locks：给SELECT 的查询中使用一个“WHERE”子句描述范围加锁），所以导致幻读。
                                  
可序列化的数据库锁情况  
事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放；事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。

第一步：设置事务的隔离级别
mysql> set tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%tx_isolation%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

第二步：开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

第三步：修改数据
mysql> update student set age = 18 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

第四步：查看该数据
mysql> select * from student where id = 2;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

第五步：修改改数据
mysql> update student set age = 17 where id = 2;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted  

# 结论：
当事务的隔离机制为：可序列化机制时，一旦加上排它锁，那么其他事务将无法查看也无法修改该数据。

修改事务隔离级别

在 MySQL 中，可以通过show variables like '%tx_isolation%'或select @@tx_isolation;语句来查看当前事务隔离级别。

mysql> show variables like '%tx_isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别。语法格式如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：
● SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务
● GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响
● 如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

任何用户都能改变会话的事务隔离级别，但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。

如果使用普通用户修改全局事务隔离级别，就会提示需要超级权限才能执行此操作的错误信息，SQL 语句和运行结果如下：

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

# 永久修改：
vim /etc/my.cnf
[mysqld]
transaction_isolation=read-uncommit

退出MySQL并重启MySQL服务即可