mysql事务隔离级别 以及 悲观锁-乐观锁

以上不是重点，重点是 对事务控制语句不熟悉。

SAVEPOINT identifier : 在事务中 创建保存点。一个事务中 允许有多个保存点。

RELEASE SAVEPOINT identifier : 删除保存点。当事务中 没有指定的 保存点，执行该语句 会抛异常。

ROLLBACK TO identifier : 把事务回滚到 保存点。

Say you have a table T with a column C with one row in it, say it has the value '1'. And consider you have a simple task like following:

That is a simple task that issue two reads from table T, with a delay of 1 minute between them.

If you follow the logic above you can quickly realize that SERIALIZABLE transactions, while they may make life easy for you, are always completely blocking every possible concurrent operation, since they require that nobody can modify, delete nor insert any row. The default transaction isolation level of the .Net System.Transactions scope is serializable, and this usually explains the abysmal performance that results.

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

我们仍然先准备好students表的数据：

然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

事务B在第3步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时，读到的记录仍然为空，但是，事务B在第7步试图更新这条不存在的记录时，竟然成功了，并且，事务B在第8步再次读取id=99的记录时，记录出现了。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

在冲突较少的情况下，使用乐观锁。乐观锁 因为没有 加锁 释放锁，也减少了 加锁 释放锁的开销。

冲突较多时，如果使用乐观锁 需要不停地尝试，所以 使用悲观锁。

如果乐观锁 进行尝试时 的花销较大，也是使用悲观锁。

分别是原子性、一致性、隔离性、持久性。

原子性是指事务包含的所有 *** 作要么全部成功，要么全部失败回滚，因此事务的 *** 作如果成功就必须要完全应用到数据库，如果 *** 作失败则不能对数据库有任何影响。

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

隔离性是当多个用户并发访问数据库时，比如同时 *** 作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的 *** 作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的 *** 作。例如我们在使用JDBC *** 作数据库时，在提交事务方法后，提示用户事务 *** 作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。

在数据库 *** 作中，在并发的情况下可能出现如下问题：

正是为了解决以上情况，数据库提供了几种隔离级别。

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(未授权读取、读未提交)、Read committed（授权读取、读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

虽然数据库的隔离级别可以解决大多数问题，但是灵活度较差，为此又提出了悲观锁和乐观锁的概念。

悲观锁，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统的数据访问层中实现了加锁机制，也无法保证外部系统不会修改数据。

商品t_items表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单（此时该商品无法再次下单），那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1。

如果不采用锁，那么 *** 作方法如下：

但是上面这种场景在高并发访问的情况下很可能会出现问题。例如当第一步 *** 作中，查询出来的商品status为1。但是当我们执行第三步Update *** 作的时候，有可能出现其他人先一步对商品下单把t_items中的status修改为2了，但是我们并不知道数据已经被修改了，这样就可能造成同一个商品被下单2次，使得数据不一致。所以说这种方式是不安全的。

在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程，使用悲观锁的原理就是，当我们在查询出t_items信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为t_items被锁定了，就不会出现有第三者来对其进行修改了。需要注意的是，要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新 *** 作后，MySQL会立刻将结果进行提交。我们可以使用命令设置MySQL为非autocommit模式： set autocommit=0

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

上面的begin/commit为事务的开始和结束，因为在前一步我们关闭了mysql的autocommit，所以需要手动控制事务的提交。

上面的第一步我们执行了一次查询 *** 作： select status from t_items where id=1 for update 与普通查询不一样的是，我们使用了 select…for update 的方式，这样就通过数据库实现了悲观锁。此时在t_items表中，id为1的那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。需要注意的是，在事务中，只有 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE *** 作同一个数据时才会等待其它事务结束后才执行，一般 SELECT ... 则不受此影响。拿上面的实例来说，当我执行 select status from t_items where id=1 for update 后。我在另外的事务中如果再次执行 select status from t_items where id=1 for update 则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行 select status from t_items where id=1 则能正常查询出数据，不会受第一个事务的影响。

使用 select…for update 会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键或者索引，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。举例如下：

1、 select * from t_items where id=1 for update

这条语句明确指定主键（id=1），并且有此数据（id=1的数据存在），则采用row lock。只锁定当前这条数据。

2、 select * from t_items where id=3 for update

这条语句明确指定主键，但是却查无此数据，此时不会产生lock（没有元数据，又去lock谁呢？）。

3、 select * from t_items where name='手机' for update

这条语句没有指定数据的主键，那么此时产生table lock，即在当前事务提交前整张数据表的所有字段将无法被查询。

4、 select * from t_items where id>0 for update 或者 select * from t_items where id>1 for update （注：>在SQL中表示不等于）

上述两条语句的主键都不明确，也会产生table lock。

5、 select * from t_items where status=1 for update （假设为status字段添加了索引）

这条语句明确指定了索引，并且有此数据，则产生row lock。

6、 select * from t_items where status=3 for update （假设为status字段添加了索引）

这条语句明确指定索引，但是根据索引查无此数据，也就不会产生lock。

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以只会在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做。实现乐观锁一般来说有以下2种方式：

当多个用户访问同一份数据时，一个用户在更改数据的过程中，可能有其他用户同时发起更改请求，为保证数据库记录的更新从一个一致性状态变为另外一个一致性状态，使用事务处理是非常必要的，事务具有以下四个特性：

MySQL 提供了多种事务型存储引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事务。为了支持事务，InnoDB 存储引擎引入了与事务处理相关的 REDO 日志和 UNDO 日志，同时事务依赖于 MySQL 提供的锁机制

事务执行时需要将执行的事务日志写入日志文件，对应的文件为 REDO 日志。当每条 SQL 进行数据更新 *** 作时，首先将 REDO 日志写进日志缓冲区。当客户端执行 COMMIT 命令提交时，日志缓冲区的内容将被刷新到磁盘，日志缓冲区的刷新方式或者时间间隔可以通过参数 innodb_flush_log_at_trx_commit 控制

REDO 日志对应磁盘上的 ib_logifleN 文件，该文件默认为 5MB，建议设置为 512MB，以便容纳较大的事务。MySQL 崩溃恢复时会重新执行 REDO 日志的记录，恢复最新数据，保证已提交事务的持久性

与 REDO 日志相反，UNDO 日志主要用于事务异常时的数据回滚，具体内容就是记录数据被修改前的信息到 UNDO 缓冲区，然后在合适的时间将内容刷新到磁盘

假如由于系统错误或者 rollback *** 作而导致事务回滚，可以根据 undo 日志回滚到没修改前的状态，保证未提交事务的原子性

与 REDO 日志不同的是，磁盘上不存在单独的 UNDO 日志文件，所有的 UNDO 日志均存在表空间对应的 .ibd 数据文件中，即使 MySQL 服务启动了独立表空间

在 MySQL 中，可以使用 BEGIN 开始事务，使用 COMMIT 结束事务，中间可以使用 ROLLBACK 回滚事务。MySQL 通过 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务

MySQL 定义了四种隔离级别，指定事务中哪些数据改变其他事务可见、哪些数据该表其他事务不可见。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少

InnoDB 系统级事务隔离级别可以使用以下语句设置：

查看系统级事务隔离级别：

InnoDB 会话级事务隔离级别可以使用以下语句设置：

查看会话级事务隔离级别：

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据称为脏读（Dirty Read），即是：首先开启 A 和 B 两个事务，在 B 事务更新但未提交之前，A 事务读取到了更新后的数据，但由于 B 事务回滚，导致 A 事务出现了脏读现象

所有事务只能看见已经提交事务所做的改变，此级别可以解决脏读，但也会导致不可重复读（Nonrepeatable Read）：首先开启 A 和 B 两个事务，A事务读取了 B 事务的数据，在 B 事务更新并提交后，A 事务又读取到了更新后的数据，此时就出现了同一 A 事务中的查询出现了不同的查询结果

MySQL 默认的事务隔离级别，能确保同一事务的多个实例在并发读取数据时看到同样的数据行，理论上会导致一个问题，幻读（Phontom Read）。例如，第一个事务对一个表中的数据做了修改，这种修改会涉及表中的全部数据行，同时第二个事务也修改这个表中的数据，这次的修改是向表中插入一行新数据，此时就会发生 *** 作第一个事务的用户发现表中还有没有修改的数据行

InnoDB 通过多版本并发控制机制（MVCC）解决了该问题：InnoDB 通过为每个数据行增加两个隐含值的方式来实现，这两个隐含值记录了行的创建时间、过期时间以及每一行存储时间发生时的系统版本号，每个查询根据事务的版本号来查询结果

通过强制事务排序，使其不可能相互冲突，从而解决幻读问题。简而言之，就是在每个读的数据行上加上共享锁实现，这个级别会导致大量的超时现象和锁竞争，一般不推荐使用

为了解决数据库并发控制问题，如走到同一时刻客户端对同一张表做更新或者查询 *** 作，需要对并发 *** 作进行控制，因此产生了锁

共享锁的粒度是行或者元组（多个行），一个事务获取了共享锁以后，可以对锁定范围内的数据执行读 *** 作

排他锁的粒度与共享锁相同，一个事务获取排他锁以后，可以对锁定范围内的数据执行写 *** 作

有两个事务 A 和 B，如果事务 A 获取了一个元组的共享锁，事务 B 还可以立即获取这个元组的共享锁，但不能获取这个元组的排他锁，必须等到事务 A 释放共享锁之后。如果事务 A 获取了一个元组的排他锁，事务 B 不能立即获取这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到 A 释放排他锁之后

意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁和意向排他锁。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。有意表示事务想执行 *** 作但还没真正执行

锁的粒度主要分为表锁和行锁

表锁的开销最小，同时允许的并发量也是最小。MyISAM 存储引擎使用该锁机制。当要写入数据时，整个表记录被锁，此时其他读/写动作一律等待。一些特定的动作，如 ALTER TABLE 执行时使用的也是表锁

行锁可以支持最大的并发，InnoDB 存储引擎使用该锁机制。如果要支持并发读/写，建议采用 InnoDB 存储引擎

---