关于数据库锁，事务，隔离级别

一，数据库中的锁

MySQL采用的是C/S结构的数据库系统，这就意味着同一时间可能会出现多个客户端访问数据库，进行读写，修改等操作。这样一来就会出现一些列的问题。

比如，买车票，同一趟车次只有一张票了，A和B同时读取了，这个数据。此时如果没有约束，双方都以为自己有票，下单后就会出现A和B坐在了同一趟车次的同一个座位上。

对此问题的解决方案有多种，对于MyISAM 存储引擎来说，只能使用锁定（Locking）机制来实现，简单的来说就是一张或者多张表在同一时间只能有一个客户端来使用，在解除锁定之前，其他客户端无法对表数据进行修改甚至读取，具体要看锁的类型。

锁的使用语法：

LOCK TABLE table1 locktype ,table2 locktype ...

locktype的取值：

READ:被锁定的表对全体客户端只读，不允许修改，包括加锁的客户端，READ LOCK只在当前表上没有任何WRITE LOCK的时候才生效。

READ LOCAL :类似于READ锁定类型，但是允许不影响现有数据的任何INSERT 命令执行。

WRITE:被锁定只允许当前客户端读取和修改。其他客户端完全被排外，不能进行任何读写操作，WRITE LOCK只在当前表上没有任何READ LOCK的时候才生效。

LOW PRIORITY WRITE:类似于WRITE锁定类型，允许其他客户端在等待WRITE LOCK，READ LOCK期间加一个READ LOCK。

使用UNLOCK TABLE 解除锁定。

提示：

Mysql总是以这样的方式执行单条命令：不让它收到其他任何命令的影响，因此在执行单条命令（UPDATE或者DELETE）的时候不需要使用锁定机制。只有当执行连续的命令且不希望其他客户端在命令执行期间修改正在使用的数据时，才需要使用锁定机制。如果先读取紧接着修改此数据。不要对InnoDB类型的数据表操作时使用lock机制，因为在mysql早期版本中使用lock机制会和innoDB本身的锁定机制发生冲突。如果要对InnoDB表进行整个表的锁定，在mysql5.3之后提供了LOCK TABLE TRANSACTIONAL命令。

对于InnoDB存储引擎来说，它支持事务来解决上述问题。

 

二，什么是事务：

事务就是能保证一组操作要么执行，要么不执行。

比如A给B转账操作，需要两步首先把A的账户减去数额，然后把B的账户加上相应数额。事务就保证了这两个操作要么成功要么失败，不会出现其中一个成功的情况，即使是断电等异常情况下。

为啥要用事务：

在回答为什么要用事务的时候，数据库的理论家往往将其总结为一个词：ACID，原子性（Atomicity）,一致性后者稳定性（Consistency）,隔离性（Isolation）和可靠性（Durability）.

原子性（Atomicity）:保证了事务像原子一样不可分割，数据库系统必须保证事务内的所有操作命令，要么全部执行成功，要么不会真正执行，即使执行了也会撤销，即使在事务执行过程中，发现了计算机奔溃等极端事件。

一致性后者稳定性（Consistency）：保证事务执行完毕后，数据库必须处于一个稳定的状态，如果发现执行了某个事务让数据违反了有关数据合法性规定（也就是一些类似外键约束，出现了非法数据）。就会撤销事务。回滚到事务执行之前的状态。

隔离性（Isolation）：这意味着多个事务可以独立执行，在执行时不会相互影响。每一个事务看到的数据库在这个事务开始之前和结束之后，除了事务本身做出的修改外不会发生任何变化。换句话说即使一个事务插入或者修改删除了某个数据，只要事务没被提交，与他同期执行的事务就不会被影响，在这个事务提交了之后，所受其结果影响的其他事务会被自动回滚。并返回错误信息给客户端。让事务百分之百的隔离是要付出巨大代价的，这个代价表现在速度方面。为此，定义了四种事务隔离级别供程序员在安全和速度两方面根据具体需要选择。不通的数据库系统有不通的默认隔离级别。InnoDB采用的默认隔离级别是 REPEATABLE READ.

可靠性（Durability）:这意味着，事务必须能够经受住，软件和硬件崩溃或者其他意外故障，在故障解决后任然能够继续执行。InnoDB的做法是将所有的修改都记到一个日志文件中，如果发生意外，在重启之后，InnoDB数据表驱动程序会读取这个日志文件，根据文件构造所有修改再传输给数据库。可靠性和速度不可兼得，InnoDB提供了参数来选择什么时候记录日志。

 

三，事务的隔离级别：

READ_UNCOMMITED (读未提交)：SELECT 命令在读取记录时会把尚未完成的其他事务对数据的修改也会考虑在内。换句话说SELECT 命令和其他事务没有隔离。注意：READ_UNCOMMITED不隔离SELECT命令但却隔离UPDATE.

注：这种隔离级别会出现脏读，所谓脏读就是读取其他未提交的事务修改的数据后，其他事务出现回滚后，读取的数据就是错误的现象。

READ_COMMITED（读已提交）：SELECT 命令在读取记录时会把已经完成的其他事务对数据的修改也会考虑在内。这意味着同一个事务里的同样的两条SELECT 命令可以有不同的结果。

注：这种隔离级别解决了脏读问题，但是这种隔离级别会出现不可重复读情况，所谓不可重复读就是，同样的两条SELECT命令，命令1 读取的时候其他事务对读取的数据修改操作未提交，但是在命令2执行的时候其他的事务的修改操作提交了，这就导致同样的查询命令在同一个事务中查到的结果不同。

REPEATABLE READ(可重读)：SELECT 命令在读取记录时会把不会把其他事务对数据的修改也会考虑在内，不管其他事务有没有提交。这种隔离级别完全符合ACID对SELECT 命令的隔离要求。同一个事务里同样的读命令当然要返回同样的数据。

注：这种隔离级别解决了不可重复读问题和幻读问题，所谓幻读同样的两条SELECT命令，命令1 读取的时候其他事务对读取的数据修改操作未提交，但是在命令2执行的时候其他的事务的修改操作提交了，这就导致同样的查询命令在同一个事务中查到的数量不同。

SERIALIZABLE(串行化)，这种模式和REPEATABLE READ很相似，唯一的区别就是这种模式会把普通的SELECT也当做是SELECT ...LOCK IN SHARE MODE形势来执行，并给受其影响的其他数据记录统一加上一把共享锁。脏读，不可重读和幻读：

【1】脏读（读取未提交数据）

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。

这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读。

【2】不可重复读（前后多次读取，数据内容不一致）

事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，

将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

【3】幻读（前后多次读取，数据总量不一致）

事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

 

隔离级别是如何实现的：MVCC

MVCC(Multi-Version Concurrency Control多版本并发控制)：MVCC每次更新操作都会复制一条新的记录，新纪录的创建时间为当前事务id优势为读不加锁，读写不冲突InnoDb存储引擎中，每行数据包含了一些隐藏字段 DATA_TRX_ID，DATA_ROLL_PTR，DB_ROW_ID，DELETE BIT

DATA_TRX_ID 字段记录了数据的创建和删除时间，这个时间指的是对数据进行操作的事务的id

DATA_ROLL_PTR 指向当前数据的undo log记录，回滚数据就是通过这个指针

DELETE BIT位用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在mysql进行数据的GC，清理历史版本数据的时候。

具体的DML：

INSERT：创建一条新数据，DB_TRX_ID中的创建时间为当前事务id，DB_ROLL_PT为NULL

UPDATE：将当前行的DB_TRX_ID中的删除时间设置为当前事务id，DELETE BIT设置为1

DELETE：复制了一行，新行的DB_TRX_ID中的创建时间为当前事务id，删除时间为空， DB_ROLL_PT指向了上一个版本的记录，事务提交后DB_ROLL_PT置为NULL

可知，为了提高并发度，InnoDb提供了这个「非锁定读」，即不需要等待访问行上的锁释放，读取行的一个快照即可。 既然是多版本读，那么肯定读不到隔壁事务的新插入数据了，所以解决了幻读。

 

MVCC与隔离级别

Read Uncommitted每次都读取记录的最新版本，会出现脏读，未实现MVCC

Serializable对所有读操作都加锁，读写发生冲突，不会使用MVCC

SELECT (REPEATABLE READ级别)InnoDb检查每行数据，确保它们符合两个标准：

只查找创建时间早于当前事务id的记录，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行行的DELETE BIT为1时，查找删除时间晚于当前事务id的记录，确定了当前事务开始之前，行没有被删(READ_COMMITED级别)每次重新计算read view，read view的范围为InnoDb中最大的事务id，为避免脏读读取的是DB_ROLL_PT指向的记录

就这么简单吗？ 其实幻读有很多种出现形式，简单的SELECT不加条件的查询在RR下肯定是读不到隔壁事务提交的数据的。但是仍然可能在执行INSERT/UPDATE时遇到幻读现象。因为SELECT 不加锁的快照读行为是无法限制其他事务对新增重合范围的数据的插入的。

所以还要引入第二个机制。

Next-Key Lock

其实更多的幻读现象是通过写操作来发现的，如SELECT了3条数据，UPDATE的时候可能返回了4个成功结果，或者INSERT某条不在的数据时忽然报错说唯一索引冲突等。

首先来了解一下InnoDb的锁机制，InnoDB有三种行锁：

Record Lock：单个行记录上的锁

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

Next-Key Lock：前两个锁的加和，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题

如果是带排他锁操作（除了INSERT/UPDATE/DELETE这种，还包括SELECT FOR UPDATE/LOCK IN SHARE MODE等），它们默认都在操作的记录上加了Next-Key Lock。

只有使用了这里的操作后才会在相应的记录周围和记录本身加锁，即Record Lock + Gap Lock，所以会导致有冲突操作的事务阻塞进而超时失败。性能隔离级别越高并发度越差，性能越差，虽然MySQL默认的是RR，但是如果业务不需要严格的没有幻读现象，是可以降低为RC的或修改配置innodb_locks_unsafe_for_binlog为1 来避免Gap Lock的。

注意有的时候MySQL会自动对Next-Key Lock进行优化，退化为只加Record Lock，不加Gap Lock，如相关条件字段为主键时直接加Record Lock。

四，事务和锁

在绝大多数的时候一旦开始一个事务，InnoDB就会把锁定操作安排好，但是在某些情况下，默认的安排并不是最优解，因此InnoDB提供了几种方式来选择。

1，SELECT ..... LOCK IN SHARE MODE

按照InnoDB的默认隔离级别，SELECT命令在锁定的的记录上也能返回结果，并且其结果不收未提交事务的影响，这样的好处是效率高，坏处是查询的结果可能已经过时了。如果加上LOCK IN SHARE MODE 它会等到那些事务处理全部结束后才进行查询，如果LOCK IN SHARE MODE 本身也在一个事务中，在它开始执行之后和事务结束之前，数据表里与其结果记录相关的所有数据都会被锁定，其他客户只能读取不能修改，或者删除。因为里面有个关键字SHART人们称之为共享锁，共享锁可以确保在事务执行过程中读取的数据记录不会是其他事务修改或者删除的。

共享锁不会阻断其他客户来读取这些数据记录，就是其他客户在读取这些数据时也使用了 LOCK IN SHARE MODE 。共享锁只会阻断其他客户对被锁定的数据进行修改和删除操作。其他客户只能等到事务结束后才能对这些记录进行删除和修改。

2，SELECT ......FOR UPDATE

关键字FOR UPDATE 代表这对普通的SELECT 命令的另一种功能扩展。这个关键字将会给数据表里面与这条SELECT 命令相关的所有数据加一个排他锁（exclusive lock）排他锁不禁止其他客户使用SELECT 来读取被锁定的记录，但是其他客户对那些数据进行修改和删除，以及使用 LOCK IN SHARE MODE命令进行读取操作都将被阻断。共享锁和排他锁的唯一区别在于是否阻断其他客户发出的LOCK IN SHARE MODE命令。

INSERT ,UPDATE ,DELETE三个命令在开始执行后都会给将要修改或者删除的数据加上排他锁，直到事务结束，如果修改或者删除的数据有外键关系，则关联的记录也会被加排他锁。

3，放插入锁

InnoDB默认会将带范围条件查询的（where id >100 ）SELECT ...LOCK IN SHARE MODE ，SELECT ......FOR UPDATE 或者DELETE 命令加一把防插入锁，效果是不仅将符合查询条件的当期记录锁定，连符合条件的并不存在的也会被锁定，也就是说不能再插入符合条件的数据。