Mysql的事务及行级锁

转自：http://www.cnblogs.com/edwinchen/p/4171866.html

以签到为例，每个用户每天只能签到一次，那么怎么去判断某个用户当天是否签到呢？因为当初表设计的时候，每个用户签到一次，即向表中插入一条记录，根据记录的数量和时间来判断用户当天是否签到。

这样的话就会有一个问题，如果是在网速过慢的情况下，用户多次点击签到按钮，那么变会发送多次请求，可能会导致一天多次签到，重复提交的问题，那么很自然的想到用事务。这次用的是spring + mybtais的框架，一开始设计的代码大致如下：    

public boolean signIn(SignInHistory signInHistory) {
        //编程式开启事务
        TransactionTemplate template = new TransactionTemplate(transactionManager);
        boolean result = (boolean) template.execute(new TransactionCallback<Object>() {
        public Object doInTransaction(TransactionStatus transactionStatus) {
            try {
                
                //获取用户所有签到记录
                List<SignInHistory> SignInHistoryList = signInMapper.select(signInHistory);
                //如果当前时间和List中某条签到时间相同，则当天已签到，代码略去
                
                //插入签到历史表
                signInMapper.insert(signInHistory);
            } catch (Exception e) {
                transactionStatus.setRollbackOnly();  
                logger.error(e);
                return false;
            }
            return true;
          }
        });
    }

但是在测试中，发现还是会发生重复提交。

Mysql文档中相关说明：

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED andREPEATABLE READ isolation levels. A consistent read does not

set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

如果是在read committed和repeatab read 下，普通的select语句并不会进行锁操作。其它session可以照常更新或插入操作。

所以在这里面就可以发现，如果只是普通select，不管在不在事务中，mysql都不会将select加锁，所以根本无法阻止其它事务插入记录-------数据库事务隔离级别，只是针对一个事务能不能读取其它事务的中间结果。

背景知识

1.事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。 

并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。

不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

MySQL事务隔离级别

数据库事务隔离级别，只是针对一个事务能不能读取其它事务的中间结果。

• Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

• Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

• Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的"幻影" 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

• Serializable（可串行化）

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

4种隔离级别比较

读数据一致性及允许的并发副作用 隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读（Read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交度（Read committed）	语句级	否	是	是
可重复读（Repeatable read）	事务级	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否

最后要说明的是：各具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供Read committed和Serializable两个标准隔离级别，另外还提供自己定义的Read only隔离级别；SQL Server除支持上述ISO/ANSI SQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。MySQL 支持全部4个隔离级别，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读，但某些情况下又不是，这些内容在后面的章节中将会做进一步介绍。

InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁。

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

 InnoDB行锁模式兼容性列表

请求锁模式    是否兼容 当前锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

 

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

恢复和复制的需要，对InnoDB锁机制的影响

MySQL通过BINLOG录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制（可以参见本书“管理篇”的介绍）。MySQL的恢复机制（复制其实就是在Slave Mysql不断做基于BINLOG的恢复）有以下特点。

一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。这与Oracle数据库不同，Oracle是基于数据库文件块的。

二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。这点也与Oralce不同，Oracle是按照系统更新号（System Change Number，SCN）来恢复数据的，每个事务开始时，Oracle都会分配一个全局唯一的SCN，SCN的顺序与事务开始的时间顺序是一致的。

从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的，有关InnoDB在不同隔离级别下加锁的差异在下一小节还会介绍。

InnoDB在不同隔离级别下的一致性读及锁的差异

InnoDB存储引擎中不同SQL在不同隔离级别下锁比较

隔离级别         一致性读和锁 SQL		Read Uncommited	Read Commited	Repeatable Read	Serializable
SQL	条件
select	相等	None locks	Consisten read/None lock	Consisten read/None lock	Share locks
	范围	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
update	相等	exclusive locks	exclusive locks	exclusive locks	Exclusive locks
	范围	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
Insert	N/A	exclusive locks	exclusive locks	exclusive locks	exclusive locks
replace	无键冲突	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	键冲突	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
delete	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
Select ... from ... Lock in share mode	相等	Share locks	Share locks	Share locks	Share locks
	范围	Share locks	Share locks	Share Next-Key	Share Next-Key
Select * from ... For update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
	范围	exclusive locks	Share locks	exclusive next-key	exclusive next-key
Insert into ... Select ... （指源表锁）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
	innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
create table ... Select ... （指源表锁）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
	innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key

从表中可以看出：对于许多SQL，隔离级别越高，InnoDB给记录集加的锁就越严格（尤其是使用范围条件的时候），产生锁冲突的可能性也就越高，从而对并发性事务处理性能的影响也就越大。因此，我们在应用中，应该尽量使用较低的隔离级别，以减少锁争用的机率。实际上，通过优化事务逻辑，大部分应用使用Read Commited隔离级别就足够了。对于一些确实需要更高隔离级别的事务，可以通过在程序中执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE动态改变隔离级别的方式满足需求。

事务传播级别

数据库事务传播级别，指的是事务嵌套时，应该采用什么策略，即在一个事务中调用别的事务，该怎么办

假如有一下两个事务：

ServiceA { 
        void methodA() { 
         ServiceB.methodB(); 
     } 
} 
  
ServiceB { 
     void methodB() { 
     }   
} 

• PROPAGATION_REQUIRED

加入当前正在执行的事务，如果不在另外一个事务里，那么就起一个新的事务。

比如说，ServiceB.methodB的事务级别定义为PROPAGATION_REQUIRED, 那么由于执行ServiceA.methodA的时候，ServiceA.methodA已经起了事务，这时调用ServiceB.methodB，ServiceB.methodB看到自己已经运行在ServiceA.methodA的事务内部，就不再起新的事务。而假如ServiceA.methodA运行的时候发现自己没有在事务中，他就会为自己分配一个事务。这样，在ServiceA.methodA或者在ServiceB.methodB内的任何地方出现异常，事务都会被回滚。即使ServiceB.methodB的事务已经被提交，但是ServiceA.methodA在接下来fail要回滚，ServiceB.methodB也要回滚

•  PROPAGATION_SUPPORTS

如果当前在事务中，即以事务的形式运行，如果当前不再一个事务中，那么就以非事务的形式运行。

• PROPAGATION_MANDATORY

必须在一个事务中运行。也就是说，他只能被一个父事务调用。否则，他就要抛出异常。

•  PROPAGATION_REQUIRES_NEW

这个就比较绕口了。比如我们设计ServiceA.methodA的事务级别为PROPAGATION_REQUIRED，ServiceB.methodB的事务级别为PROPAGATION_REQUIRES_NEW，那么当执行到ServiceB.methodB的时候，ServiceA.methodA所在的事务就会挂起，ServiceB.methodB会起一个新的事务，等待ServiceB.methodB的事务完成以后，他才继续执行。他与PROPAGATION_REQUIRED 的事务区别在于事务的回滚程度了。因为ServiceB.methodB是新起一个事务，那么就是存在两个不同的事务。如果ServiceB.methodB已经提交，那么ServiceA.methodA失败回滚，ServiceB.methodB是不会回滚的。如果ServiceB.methodB失败回滚，如果他抛出的异常被ServiceA.methodA捕获，ServiceA.methodA事务仍然可能提交。   

• PROPAGATION_NOT_SUPPORTED

当前不支持事务。比如ServiceA.methodA的事务级别是PROPAGATION_REQUIRED ，而ServiceB.methodB的事务级别是PROPAGATION_NOT_SUPPORTED ，那么当执行到ServiceB.methodB时，ServiceA.methodA的事务挂起，而他以非事务的状态运行完，再继续ServiceA.methodA的事务。

•  PROPAGATION_NEVER

不能在事务中运行。假设ServiceA.methodA的事务级别是PROPAGATION_REQUIRED，而ServiceB.methodB的事务级别是PROPAGATION_NEVER ，那么ServiceB.methodB就要抛出异常了。

•  PROPAGATION_NESTED

理解Nested的关键是savepoint。他与PROPAGATION_REQUIRES_NEW的区别是，PROPAGATION_REQUIRES_NEW另起一个事务，将会与他的父事务相互独立，而Nested的事务和他的父事务是相依的，他的提交是要等和他的父事务一块提交的。也就是说，如果父事务最后回滚，他也要回滚的。而Nested事务的好处是他有一个savepoint。

行级锁

如果有两个事务A,B 都有 read 和write操作，如果逻辑是如果表中没有记录则插入，那么因为read操作并没有加锁，A，B进行read操作时，有可能表中都没有记录，那么事务A,B都会进行插入操作，表中将会有两条记录。

如果要保证在事务并发时，每条事务读取到的数据都是最新的，那么只能采用锁。

在select语句后加上 FOR UPDATE，再测试，重复提交的问题被解决了。但是问题又来了，如果在select语句后加上LOCK IN SHARE MODE，那么会报死锁的错误。

查看mysql文档：

• SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits.
• If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.
• SELECT ... FOR UPDATE  sets an exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing.

  LOCK IN SHARE MODE会在读取的行上加共享锁，其他session只能读不能修改或删除，如果有其他事务修改了记录，那么会等待事务提交后，再读取。

  FOR UPDATE在读取行上设置一个排他锁。阻止其他session读取或者写入行数据。

  这样看起来似乎就能解释为什么使用LOCK IN SHARE MODE会产生死锁了，假如两个事务A、B都读取同一行记录，那么在这一行就加上了共享锁，但是A和B事务中都需要

　　　　　   修改这一行，那么都要等待对方释放共享锁才能进行，结果造成了死锁。只能使用for update 来防止死锁和重复插入。

这就是mysql的两种行级锁的区别。