mysql-InnoDB中，不同事务隔离级别下的不同SQL语句的锁定情况

对于一个与数据库相关的并发应用，如果作为开发者的你不了解该类型数据库在不同事务隔离级别下的不同SQL语句的锁定情况，很难保证你的程序不做傻事，本文着重介绍了mysql-innodb的相关内容。

 

（读本文前请先自行学习事务相关的知识和数据库锁相关的知识，如：事务的隔离级别，事务的传播性，mysql-innodb的共享锁定，独占锁定，以及next-key锁定和持续无锁定读取，详情请见http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb-lock-modes《MYSQL官方帮助文档-innodb的事务与锁定篇》）

 

在mysql-innodb中，我们需要引起我们注意的锁有2种，他们都是行级锁，一种是共享锁S，另一种是独占锁X，通过他们的名字我想大家已经可以很清楚的明白他们的意思了，说白了就是表的同一行，可以同时被N个S锁锁定或只能由一个X锁锁定，当S锁锁定后，如果其他事务只能用S锁锁定这一行，而不能用X锁锁定这一行，当X锁锁定后，其他任何锁都不能锁定该行。

 

对于不同的事务隔离级别，不同操作伴随的自动加锁情况不同（下面黄色背景部分引子MYSQL5.1官方文档）：

 

在SQL查询处理中，一个锁定读，一个UPDATE或一个DELETE一般地对被扫描的每个索引记录设置记录锁定。如果在某查询中有一个WHERE条件是没什么关系的，而这个查询可能从查询的结果包中排除行。InnoDB不记得确切的WHERE条件，但是仅知道哪个索引范围被扫描。记录锁定是正常的next-key锁定，它也阻止对紧接着记录之前的间隙的插入。

如果锁定被设置为独占，则InnoDB总是取回集束的索引目录并对其设置锁定。

如果你没有适合查询的索引，MySQL不得不扫描整个表来处理查询，表的每行变成被锁定的，这样反过来阻止其它用户的所有对表的插入。创建一个好的索引让你的查询不必要扫描很多行是很重要的。

·         SELECT ... FROM是一个持续读，读取数据库的快照并且设置不锁定，除非事务隔离级别被设为SERIALIZABLE。对于 SERIALIZABLE级别，这个设置对它遇到的索引记录设置共享的next-key锁定。

·         SELECT ... FROM ... LOCK IN SHARE MODE对读遇到的所有索引记录设置共享的next-key锁定。

·         SELECT ... FROM ... FOR UPDATE对读遇到的所有索引记录设置独占的next-key锁定。

·         INSERT INTO ... VALUES (...)对被插入的行设置独占锁定。注意，这不是一个next-key锁定，并且不阻止其它用户在已插入行之前的间隙插入。如果发生重复键错误，对重复的索引记录设置共享锁定。

·         在一个表上初始化之前指定的AUTO_INCREMENT列之时，InnoDB在与AUTO_INCREMENT列相关联的索引的末尾设置独占锁定。在访问自动增长计数器中，InnoDB使用专用的表锁定模式AUTO-INC，其中锁定仅持续到当前SQL语句的结束，而不是到整个事务的结束。 请参阅15.2.10.2节，“InnoDB和AUTOCOMMIT”。

InnoDB取回先前初始化的AUTO_INCREMENT列的值而不设定任何锁定。

·         INSERT INTO T SELECT ... FROM S WHERE ... 对每个插入到T的行设置独占（非next-key）锁定。它在S上把搜索当作一个持续读，但是如果MySQL二进制日志功能被打开，它就对S设置一个共享的next-key锁定。InnoDB在后一种情况不得不设置锁定：在从一个备份的前滚恢复中，每个SQL语句不得不以与它最初被执行的方式完全同样的方式执行。

·         CREATE TABLE ... SELECT ... 把SELECT当作一个持续读来执行，或者带着共享锁定来执行，如前面的条目所述。

·         如果唯一键没有冲突，REPLACE象一个插入一样被做。另外，对必须更新的行设置一个独占的next-key锁定。

·         UPDATE ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。

·         DELETE FROM ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。

·         如果对一个表定义FOREIGN KEY约束，任何需要检查约束条件的插入，更新或删除对它看着检查约束的记录设置共享行级锁定。InnoDB在约束失败的情况下也设置这些锁定。

·         LOCK TABLES设置表锁定，但是是InnoDB层之上更高的MySQL层设置这些锁定。如果innodb_table_locks=1并且 and AUTOCOMMIT=0，InnoDB意识到表锁定，并且InnoDB之上的MySQL层知道行级锁定。另外，InooDB的自动死锁检测不能检测在这个表锁定被涉及之处的死锁。同样，既然更高的MySQL层不知道行级锁定，很可能对另一个用户当前对其有行锁定的表获得一个表锁定。尽管如此，这并不破坏事务的完整性，如15.2.10.10节，“死锁检测和回滚”中讨论的一样。请参阅15.2.16节，“对InnoDB表的限制”。

 

对于不同的事务隔离级别，事务自动释放各种锁的位置也不同

一般来说，共享锁的锁定时间与事务的隔离级别有关，如果隔离级别为Read Committed的默认级别，只在读取(select)的期间保持锁定，即在查询出数据以后就释放了锁；如果隔离级别为更高的Repeatable read或Serializable，直到事务结束才释放锁。另说明，如果select语句中指定了HoldLock提示，则也要等到事务结束才释放锁；而独占锁则不管在任何事务隔离级别下总是在事务结束时自动释放。

 

本文版权归“乱七八糟的代码（blog.csdn.net/alifel）”所有，如需转载请注明出处，否则必追究其版权问题！