Innodb中的事务隔离级别和锁以及MVCC之间的关系

我们都知道事务的几种性质，数据库为了维护这些性质，尤其是一致性和隔离性，一般使用加锁这种方式。同时数据库又是个高并发的应用，同一时间会有大量的并发访问，如果加锁过度，会极大的降低并发处理能力。所以对于加锁的处理，可以说就是数据库对于事务处理的精髓所在。这里通过分析MySQL中InnoDB引擎的加锁机制，来抛砖引玉，让读者更好的理解，在事务处理中数据库到底做了什么。

ACID

ACID 是为了解决上述问题所总结出，为保证事务是正确可靠的，所必须具备的四个特性：

1. 原子性（Atomicity）

事务中的原子性是一个常常被大家误解的特性，因为这个原子性的意思和我们通常语境下的原子性不太一样，大多数时候原子性是指一条不可再分割、不会被中断影响的指令，比如读取一个内存地址的值、将值写回内存地址、redis的SETNX（set if not exists），这些操作都符合我们常说的原子性。
可是事务中的原子性，并不是指事务具有不被中断影响的特点，它仅仅是指，事务中的所有操作应该被看作不可分割的一组指令，任何一个指令不能独立存在，要么全部成功执行，要么全部不发生（也就是回滚）。
还有很多同学对这里所说的“成功执行”有误解，成功执行是指数据库层面的，而不是业务层面的，举个例子，客户购买商品A，可是在购买时，商家刚好下架了商品，那么此时执行 update products set price = 1 where product_id=A and status=销售中 ，由于product的status已经变成“下架”，导致被更新的行数为0，这个算成功执行吗？算！数据库不报错、不宕机、正常运行就是成功，更新行数为0是数据库的正常返回结果，这在业务上是失败，在数据库层面是成功，这种情况数据库不会执行回滚，需要程序员判断更新行数，如果为0，手动回滚。
如果数据库由于硬件或者系统问题发生宕机、报错，这样才算是指令执行失败，此时数据库会重试或者直接回滚，然后将错误返回给开发者。
原子性不止为开发者保证了事务的可靠性（不会因为数据库出错而产生脏数据），还能让开发者手动回滚，提供了业务的便利性。

2. 一致性（Consistency）

这个名词也是相当令人困惑，与数据库主从复制中所说的“一致性”不同，主从复制的一致性是指多个副本间是否完成同步、数据相同，而这里的一致性是指事务是否产生非预期中间状态或结果。比如脏读和不可重复读，产生了非预期中间状态，脏写与丢失修改则产生了非预期结果。一致性实际上是由后面的隔离性去进一步保证的，隔离性达到要求，则可以满足一致性。也就是说，隔离不足会导致事务不满足一致性要求，所以务必理解各个隔离级别，才能少写Bug。

3. 隔离性（Isolation）

简单来说，隔离性就是多个事务互不影响，感觉不到对方存在，这个特性就是为了做并发控制。在多线程编程中，如果大家都读写同一块数据，那么久可能出现最终数据不一致，也就是每条线程都可能被别的线程影响了。按理说，最严格的隔离性实现就是完全感知不到其他并发事务的存在，多个并发事务无论如何调度，结果都与串行执行一样。为了达到串行效果，目前采用的方式一般是两阶段加锁（Two Phase Locking），但是读写都加锁效率非常低，读写之间只能排队执行，有时候为了效率，原则是可以妥协的，于是隔离性并不严格，它被分为了多种级别，从高到低分别为：
⬇️可串行化（Serializable）
⬇️可重复读（Read Repeatable）
⬇️已提交读（Read Committed）
⬇️未提交读（Read Uncommitted）
每一个级别都只是指导标准，每个数据库对其的实现都有差异，有的数据库在Read Committed级别时，就已经实现了Read Repeatable的效果，有的数据库干脆不提供Read Uncommitted级别。
在隔离级别为Serializable时，就会感觉到事务像一个完完全全的原子操作，不被任何中断、并发所影响。
很多开发者理解的事务可能就在Serializable级别，大家误以为事务都是可串行化的，其实并不是，大多数的数据库默认隔离级别都不是可串行化，大多数在Read Repeatable或者Read Committed，要是按照可串行化的思维去编程，却用着低于可串行化的隔离级别，就很容易写出导致数据在业务层面不一致的代码，所以开发者一定要理解各个隔离级别及其原理，更好地支撑业务开发，下面会仔细地讲隔离级别及其实现。

4. 持久性（Duration）

这是ACID中最好理解的，即事务成功提交后，对数据的修改永久的，即使系统发生故障，也不会丢失，这里所说的故障，也只是一般错误比如宕机、系统Bug、断电，如果是硬盘损毁，那就没办法，数据一定会丢失。

一次封锁or两段锁？

因为有大量的并发访问，为了预防死锁，一般应用中推荐使用一次封锁法，就是在方法的开始阶段，已经预先知道会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式可以有效的避免循环死锁，但在数据库中却不适用，因为在事务开始阶段，数据库并不知道会用到哪些数据。

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化（串行化很重要，尤其是在数据恢复和备份的时候）的。

事务中的加锁方式

事务的四种隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。我们的数据库锁，也是为了构建这些隔离级别存在的。

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

MySQL中锁的种类

MySQL中锁的种类很多，有常见的表锁和行锁，也有新加入的Metadata Lock等等,表锁是对一整张表加锁，虽然可分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做ddl处理时使用。

行锁则是锁住数据行，这种加锁方法比较复杂，但是由于只锁住有限的数据，对于其它数据不加限制，所以并发能力强，MySQL一般都是用行锁来处理并发事务。这里主要讨论的也就是行锁。

Read Committed（读取提交内容）

在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。效果如下

MySQL> show create table class_teacher \G\
Table: class_teacher
Create Table: CREATE TABLE `class_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_teacher_id` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.02 sec)
MySQL> select * from class_teacher;
+----+--------------+------------+
| id | class_name   | teacher_id |
+----+--------------+------------+
|  1 | 初三一班     |          1 |
|  3 | 初二一班     |          2 |
|  4 | 初二二班     |          2 |
+----+--------------+------------+

由于MySQL的InnoDB默认是使用的RR级别，所以我们先要将该session开启成RC级别，并且设置binlog的模式

SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';（或者是MIXED）

为了防止并发过程中的修改冲突，事务A中MySQL给teacher_id=1的数据行加锁，并一直不commit（释放锁），那么事务B也就一直拿不到该行锁，wait直到超时。

这时我们要注意到，teacher_id是有索引的，如果是没有索引的class_name呢？update class_teacher set teacher_id=3 where class_name = ‘初三一班’; 那么MySQL会给整张表的所有数据行的加行锁。这里听起来有点不可思议，但是当sql运行的过程中，MySQL并不知道哪些数据行是 class_name = ‘初三一班’的（没有索引嘛），如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤。

但在实际使用过程当中，MySQL做了一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录释放锁 (违背了二段锁协议的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。可见即使是MySQL，为了效率也是会违反规范的。（参见《高性能MySQL》中文第三版p181）

这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候，如果无法使用相应的索引，MySQL Server过滤数据的的时候特别慢，就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。

Repeatable Read（可重读）

这是MySQL中InnoDB默认的隔离级别。我们姑且分“读”和“写”两个模块来讲解。

读

读就是可重读，可重读这个概念是一事务的多个实例在并发读取数据时，会看到同样的数据行，有点抽象，我们来看一下效果。

RC（不可重读）模式下的展现

事务B修改id=1的数据提交之后，事务A同样的查询，后一次和前一次的结果不一样，这就是不可重读（重新读取产生的结果不一样）。这就很可能带来一些问题，那么我们来看看在RR级别中MySQL的表现：

我们注意到，当teacher_id=1时，事务A先做了一次读取，事务B中间修改了id=1的数据，并commit之后，事务A第二次读到的数据和第一次完全相同。所以说它是可重读的。那么MySQL是怎么做到的呢？这里姑且卖个关子，我们往下看。

不可重复读和幻读的区别

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

悲观锁和乐观锁

悲观锁

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

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

要说明的是，MVCC的实现没有固定的规范，每个数据库都会有不同的实现方式，这里讨论的是InnoDB的MVCC。

MVCC在MySQL的InnoDB中的实现

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。在可重读Repeatable reads事务隔离级别下：

SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
INSERT时，保存当前事务版本号为行的创建版本号
DELETE时，保存当前事务版本号为行的删除版本号
UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。

我们不管从数据库方面的教课书中学到，还是从网络上看到，大都是上文中事务的四种隔离级别这一模块列出的意思，RR级别是可重复读的，但无法解决幻读，而只有在Serializable级别才能解决幻读。于是我就加了一个事务C来展示效果。在事务C中添加了一条teacher_id=1的数据commit，RR级别中应该会有幻读现象，事务A在查询teacher_id=1的数据时会读到事务C新加的数据。但是测试后发现，在MySQL中是不存在这种情况的，在事务C提交后，事务A还是不会读到这条数据。可见在MySQL的RR级别中，是解决了幻读的读问题的。

读问题解决了，根据MVCC的定义，并发提交数据时会出现冲突，那么冲突时如何解决呢？我们再来看看InnoDB中RR级别对于写数据的处理。

“读”与“读”的区别

可能有读者会疑惑，事务的隔离级别其实都是对于读数据的定义，但到了这里，就被拆成了读和写两个模块来讲解。这主要是因为MySQL中的读，和事务隔离级别中的读，是不一样的。

我们且看，在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

快照读：就是select
select * from table ….;
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update ;
delete;

事务的隔离级别实际上都是定义了当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”，就需要另外的模块来解决了。

写（”当前读”）

事务的隔离级别中虽然只定义了读数据的要求，实际上这也可以说是写数据的要求。上文的“读”，实际是讲的快照读；而这里说的“写”就是当前读了。

为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁。

Next-Key锁

Next-Key锁是行锁和GAP（间隙锁）的合并，行锁上文已经介绍了，接下来说下GAP间隙锁。

行锁可以防止不同事务版本的数据修改提交时造成数据冲突的情况。但如何避免别的事务插入数据就成了问题。我们可以看看RR级别和RC级别的对比

RC级别：

RR级别：

通过对比我们可以发现，在RC级别中，事务A修改了所有teacher_id=30的数据，但是当事务Binsert进新数据后，事务A发现莫名其妙多了一行teacher_id=30的数据，而且没有被之前的update语句所修改，这就是“当前读”的幻读。

RR级别中，事务A在update后加锁，事务B无法插入新数据，这样事务A在update前后读的数据保持一致，避免了幻读。这个锁，就是Gap锁。

MySQL是这么实现的：

在class_teacher这张表中，teacher_id是个索引，那么它就会维护一套B+树的数据关系，为了简化，我们用链表结构来表达（实际上是个树形结构，但原理相同）

如图所示，InnoDB使用的是聚集索引，teacher_id身为二级索引，就要维护一个索引字段和主键id的树状结构（这里用链表形式表现），并保持顺序排列。

Innodb将这段数据分成几个个区间

(negative infinity, 5],
(5,30],
(30,positive infinity)；

update class_teacher set class_name=‘初三四班’ where teacher_id=30;不仅用行锁，锁住了相应的数据行；同时也在两边的区间，（5,30]和（30，positive infinity），都加入了gap锁。这样事务B就无法在这个两个区间insert进新数据。

受限于这种实现方式，Innodb很多时候会锁住不需要锁的区间。如下所示：

update的teacher_id=20是在(5，30]区间，即使没有修改任何数据，Innodb也会在这个区间加gap锁，而其它区间不会影响，事务C正常插入。

如果使用的是没有索引的字段，比如update class_teacher set teacher_id=7 where class_name=‘初三八班（即使没有匹配到任何数据）’,那么会给全表加入gap锁。同时，它不能像上文中行锁一样经过MySQL Server过滤自动解除不满足条件的锁，因为没有索引，则这些字段也就没有排序，也就没有区间。除非该事务提交，否则其它事务无法插入任何数据。

行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

Serializable

这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

这里要吐槽一句，不要看到select就说不会加锁了，在Serializable这个级别，还是会加锁的！

锁的替代——使用MVCC提高并发度

可串行化虽然保证了事务的绝对安全，但是并发度很低，很多操作都需要排队进行，为了提高效率，SQL标准在隔离级别上进行了妥协，由此有了可重复读、读提交的隔离级别，它们都允许部分并发问题，这里先讲可重复读隔离级别。
SQL标准中，可重复读仅仅需要完全避免脏写、脏读、不可重复读三种异常，此时如果再用加锁实现，读-写排队未免效率太低，于是MVCC诞生了。
MVCC全称Multiple Version Concurrency Control，也就是多版本并发控制，重点在多版本，简单来说，它为每个事务生成了一个快照，保证每个事务只能读到自己的快照数据，不论其他事务如何更新一条记录，这个事务所读到的数据都不会产生变化，也就是说，会为一条记录保留多个版本，多个事务读到的版本不同，MVCC代替了读锁，实现了读-写不阻塞。
MVCC的意义只是替代读锁，写依旧是加锁的，这样避免了脏写，下面先讲一下MVCC的实现思路，认识MVCC如何避免并发问题，最后讨论MVCC在并发中的局限性。

1. MVCC实现原理

版本链（Undo Log）

在MVCC中，每条记录都有多个版本，串成了一个版本链，也就是说，记录被UPDATE时并不是In Place Update，而是将记录复制然后修改存一份到版本链，被DELET时，也不是马上从文件删除，而是将记录标记为被删除，它也是版本链的一环。
在InnoDB中每条记录中都有2个隐藏列，1个是trx_id，一个是roll_pointer。

trx_id代表这条记录版本是被哪个事务创建的，数据库有一个全局的事务ID分配器，它一定是递增的，新的事务ID一定不会和旧的事务ID重复。
roll_pointer是连接版本链的指针。

Read View

MVCC中最常听到的概念就是快照，其实快照只是最终结果，而不是实现方式，快照 = 版本链 + Read View。
MVCC并不是将表中所有的记录都为这个事务冻结了一份快照，而是在事务执行第一条语句时时生成了一个叫做Read View的数据结构，注意，Read View是事务执行语句时才会生成的，仅仅执行start transaction是不会生成Read View的。
Read View保存着以下信息：

Read View结合版本链使用，当事务读取某条记录时，会根据此事务的Read View判断此记录的哪个版本是这个事务可见的：

如果记录的trx_id与creator_trx_id相同，则代表这个版本是此事务创建的，可以读取。
如果记录的trx_id小于min_trx_id，代表这个版本是此事务生成Read View之前就已经创建的，可以读取。
如果记录的trx_id大于等于max_trx_id，代表这个版本是此事务生成Read View之后开启的事务创建的，一定不能被读取。
如果记录的trx_id处于min_trx_id与max_trx_id之间，则判断trx_id是否在m_ids中，如果不在，则代表这个版本是此事务生成Read View时已经提交的，可以读取。

有了版本链和Read View，即使其他事务修改了记录，先生成Read View的事务也不会读到，只要Read View不改变，每次读到的版本一定相同。MySQL中可重复读和读提交级别都基于MVCC，区别只是生成Read View的时机不同，可重复读级别是在事务执行第一个SQL时生成Read View，而读提交级别是在事务每执行一条SQL时都会重新生成Read View。

2. MVCC的局限性

MVCC取代了读锁的位置，它不阻塞写入虽然有提高效率的优势，但是同时也无法防止所有并发问题。

1. MVCC能避免幻读吗

事务是无法读到Read View生成后别的事务产生的记录版本，因此可以在不加间隙锁的情况下也不会读到别的事务的插入，那MVCC能避免幻读吗？
先说结论：MVCC不可以避免幻读。
导致这个问题的根本原因是：InnoDB将Update、Insert、Delete都视为特殊操作，特殊操作对记录进行的是当前读（Current Read），也就是会读取最新的记录，也就是说Read View只对SELECT语句起作用。
如果users表中有id为1、2、3共3条记录，事务A先读，事务B插入一条记录并提交，事务A更新被插入的记录是可以成功的，因为UPDATE是进行当前读，更新时可以读到id为4的记录存在，因此可以成功更新，事务A成功更新id为4的记录后，将在id为4的记录版本链上新增一条事务A的版本，因此事务A再次SELECT，就可以名正言顺地读到这条记录，符合Read View规则，但产生了幻读。

如果要避免幻读，可以使用MVCC+间隙锁的方式。

2. 无法避免Read Skew与Write Skew

由于MVCC中读-写互不阻塞，因此事务读取的快照可能已经过期，读到的可能已经成为陈旧数据，因此可能出现Read Skew与Write Skew。

3. 无法避免丢失更新

还是由于读-写不阻塞的特性：
R1(A) => R2(A) => W2(A) => W1(A)
事务1读出的A值已经过期，但是它不知道，还是根据旧的A值去更新A，最后覆盖了事务2的写入。
在Postgrel中，Repeatable Read级别就已经避免了丢失更新，因为它使用MVCC+乐观锁，如果事务1去写入A，存储引擎检测到A值已经在事务1开启后被别的事务修改过，则会报错，阻止事务1的写入。单纯的MVCC并不能防止丢失更新，需要配合其他机制。

三、事务更佳实践

在进行业务开发时应该先了解项目使用的数据库的事务隔离级别以及其原理、表现，然后根据事务实现原理去思考更好的编码方式。

1. 避免死锁

语句顺序不同导致死锁

这种情况大家一定很熟悉了：

死锁

因此建议在不同的业务中，尽量统一操作相同记录语句的顺序。

索引顺序不同导致死锁

锁都是加在索引上的（这里最好先理解一下B+Tree索引），所以一条SQL如果涉及多个索引，会为每个索引加锁，比如有一张users表（id,user_name,password），主键为id，在user_name上有一个唯一索引（Unique Index），以下语句：

UPDATE users SET user_name = 'j.huang@aftership.com' WHERE id = 1;

这条语句中涉及到了id与user_name两个索引，InnoDB是索引组织表，主键是聚簇索引，因此记录是存在主键聚簇索引结构中的，那么这条SQL的加锁顺序为：

为表加上IX锁
为主键加上X锁
为索引user_name加上X锁

此时如果另一条事务执行如下语句：

UPDATE users SET password = '123' WHERE user_name = 'j.huang@aftership.com';

则可能产生死锁。
原因大家可以先思考一下。
这条语句的加锁顺序是：

找到user_name为'j.huang@aftership.com'的索引，加X锁
为表加IX锁
为主键加X锁

他们都会对同一个主键索引加锁和同一个二级索引，但是加锁顺序不同，因此可能造成死锁，这种情况很难避免，MySQL中可以通过SHOW ENGINE INNODB STATUS查看InnoDB的死锁检测情况。

2. 避免不必要的事务

其实很多业务场景并不需要事务，比如说领取优惠券，并不需要开启一个Serializable级别的事务去SELECT优惠券剩余数量，判断是否有余量，再UPDATE领取优惠券，完全可以一条语句解决：

UPDATE coupons SET balance = balance - 1 WHERE id = 1 and balance >= 1;

语句返回后判断更新行数，如果更新行数为1，则代表领取成功，更新行数为0，代表没有符合条件的记录，领取失败。
（注意：这里只考虑领取优惠券的场景，如果业务还需要将优惠券写入users表等其他一系列操作，就需要根据业务需求放入事务）

3. 避免将不必要的SELECT放入事务

首先应该理解将SELECT放入事务的意义是什么？

需要读取事务自己的版本，则必须将SELECT放入事务
需要依赖SELECT结果作为其他语句的前提，此时不止要把SELECT放入事务，还必须保证事务是Serializable级别的

如果不是以上两个原因，则SELECT是没有必要放入事务的，比如下单一件产品，如果只是SELECT它的product_name去写入orders表，这种非强一致要求的数据，没有必要放入事务，因为product_name即使被改变了，写入order的product_name是1秒前的旧数据，也是可以接受的。

4. 不要迷信事务

很多开发者误以为将SELECT放入事务，将结果作为判断条件或者写入条件是安全的，其实根据隔离级别不同，是不一定的，举个例子：

SELECT users表某个用户等级信息，如果是钻石会员，则为他3倍积分
将算出的积分UPDATE到user_scores表

将这两条语句放入事务也不一定是安全的，这取决于事务的实现，如果是InnoDB的Repeatable Read级别，那么这个事务是不安全的，因为SELECT读到的是快照，在UPDATE之前，其他事务可能就已经修改了user的等级信息，他可能已经不满足3倍积分条件，而此时再去UPDATE user_scores表，这个事务是个业务不安全的事务。
因此，要先了解事务，再去使用，否则容易用错。