MySQL 事务与锁机制

时间：2022-03-06 23:30:55

　　下表展示了本人安装的MariaDB(10.1.19，MySQL的分支)所支持的所有存储引擎概况，其中支持事务的有InnoDB、SEQUENCE，另外InnoDB还支持XA事务，MyISAM不支持事务。锁可以通过SQL语句(如 LOCK TABLES )显式申请，也可以由InnoDB引擎自动为你获取。下文将讨论InnoDB和MyISAM在事务与锁定方面的相关话题

ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
CSV YES CSV storage engine NO NO NO
MyISAM YES MyISAM storage engine NO NO NO
MRG_MyISAM YES Collection of identical MyISAM tables NO NO NO
Aria YES Crash-safe tables with MyISAM heritage NO NO NO
MEMORY YES Hash based, stored in memory, useful for temporary... NO NO NO
PERFORMANCE_SCHEMA YES Performance Schema NO NO NO
SEQUENCE YES Generated tables filled with sequential values YES NO YES
InnoDB DEFAULT Percona-XtraDB, Supports transactions, row-level l... YES YES YES

一. 事务四要素

　　数据库事务正确执行的四个基本要素包括原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），简称ACID。目前要实现ACID主要有两种方式：一种是Write Ahead Logging，也就是日志式的方式(现代数据库均基于这种方式)；另一种是Shadow Paging。

  • 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态
  • 一致性：事务执行前与执行后都必须始终保持系统处于一致的状态
  • 隔离性：并发事务之间不会相互干扰，彼此独立执行
  • 持久性：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中

二. MyISAM表锁定

　　所有用户行为最后执行的基本单位是单条操作命令(SELECT、INSERT、UPDATE、DELETE等)，它们都是原子操作。按照事务术语，MyISAM表总能高效地工作在AUTOCOMMIT=1模式下，原子操作通常能提供可比较的完整性以及更好的性能。这意味着，你能确信在每个特性更新运行的同时，其他用户不能干涉它，而且不会出现自动回滚（如果你不小心，对于事务性表，这种情况可能发生），MySQL服务器还能保证不存在脏读。

　　一般而言，所有由事务解决的完整性问题均能用LOCK TABLES或原子更新解决，从而确保了服务器不会自动中断，后者是事务性数据库系统的常见问题。MyISAM只支持表级锁定，允许多重读或一次写。LOCK TABLES可以锁定用于当前线程的表，如果表被其它线程锁定，则造成堵塞，直到可以获取所有锁定为止。UNLOCK TABLES可以释放被当前线程保持的任何锁定。当线程发布另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁。如果您想要确保在同一业务流中的SELECT和UPDATE之间没有其它线程访问修改数据，就必须使用LOCK TABLES。如果你获得了对某一表的READ LOCAL锁定（与写锁定相对），该表允许在表尾执行并行插入，当其他客户端执行插入操作时，允许执行读操作。新插入的记录不会被有读锁定属性的客户端看到，直至解除了该锁定为止。使用INSERT DELAYED，能够将插入项置于本地队列中，直至锁定解除，不会让客户端等待插入完成。

　　如果您正在对一组MyISAM表运行许多操作，锁定您正在使用的表，可以快很多。锁定MyISAM表可以加快插入、更新或删除的速度。不利方面是，没有线程可以更新一个用READ锁定的表（包括保持锁定的表），也没有线程可以访问用WRITE锁定的表（除了保持锁定的表以外）。有些MyISAM操作在LOCK TABLES之下更快的原因是，MySQL不会清空用于已锁定表的关键缓存，直到UNLOCK TABLE被调用为止。通常，关键缓存在每个SQL语句之后被清空。

三. InnoDB事务与锁定

　　在 InnoDB 中，所有用户行为都在事务内发生，事务是其执行的基本单位。如果自动提交模式被允许，即 AUTOCOMMIT = 1，每个SQL语句都将以一个单独的事务来运行；如果自动提交模式被用 SET AUTOCOMMIT = 0 关闭，那么我们可以认为一个用户总是有一个事务打开着，一个SQL COMMIT或ROLLBACK语句结束当前事务并且一个新事务开始，两个语句都释放所有在当前事务中被设置的InnoDB行锁定。

用LOCK TABLES对InnoDB表加锁时AUTOCOMMIT = 0，否则MySQL不会给表加锁；事务结束前不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。如下：

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ;

...

COMMIT;

UNLOCK TABLES;

　　InnoDB除了表级锁定之外，还支持更细粒度的行级锁定，且行锁和表锁多重粒度可共存。下面是InnoDB中与锁定有关的几个重要概念：

  • 非锁定读：InnoDB对无格式SELECT执行非锁定读，但在不同事务隔离级别条件下表现有所不同。
  • Shared (S) Locks 与 Exclusive (X) Locks：行级锁定事实上是索引记录锁定，即通过索引检索行时才使用行锁定，否则将使用表锁定。

    (1)一个共享锁S允许事务获取锁来读取一行(READ)。如果事务T1持有对行 r 的 S 锁， 那么另外一个事务T2对行 r 的锁需求会如下k处理，T2对S锁的请求会被马上授权，因此T1、T2都对r同时分别持有一个共享锁。但T2对r的X锁请求不会被马上授权，这看上去似乎READ优先于WRITE，甚至WRITE请求被饿死；
    (2)一个排他锁X允许事务获取锁来更新或删除一行(WRITE)。如果事务T1持有一个r的X锁, 那么T2对r的任何锁类型都无法被马上授权。

    脏读：事务A更新了某个数据项D，由于某种错误事件发生导致事务A回滚；但在回滚之前，另一个事务B读取了数据项D的值，
    A回滚后数据项D恢复到原值，此时事务B读取的数据项D值称为脏数据。造成脏读的原因是事务A、B缺少隔离性。
  • Intention Locks：意向锁在InnoDB中是表锁，他表明S或X锁将会在一个事务中对某一行使用。Intention shared (IS) 表明事务T打算设置S锁到表t上，Intention exclusive (IX) 表明事务T打算设置X锁到行上。意向锁协议如下： 
    1. 在一个事务获取表t的某行的S锁之前, 他必须获取表t的一个IS锁或更强的锁

2. 在一个事务获取表t某行的X锁之前, 他必须获取一个t的IX锁

3. 这些规则可以总结为如下锁类型兼容矩阵：
    
 	X	IX	S	IS
    
X	Conflict	Conflict	Conflict	Conflict
    
IX	Conflict	Compatible	Conflict	Compatible
    
S	Conflict	Conflict	Compatible	Compatible
    
IS	Conflict	Compatible	Compatible	Compatible

4. 一个锁如果和已经存在的锁兼容, 就可以授权给请求他的事务, 但如果和已存在的锁不兼容则不行

    一个事务必须等待直到冲突的锁被释放。如果一个锁请求和一个已经存在的锁冲突， 并且一直不能被授权， 就会造成死锁。一旦死锁发生，InnoDB会选择其中一个报错并释放其持有的锁，直至解除死锁。意向锁并不会阻塞任何事情，除非是对全表的请求(例如, LOCK TABLES ... WRITE)， IX和IS锁的主要目的是表示有人正在或者准备锁定一行

  • Next-Key锁定：Next-Key锁定匹配的行(Record Lock)以及相应的缺口(Gap Lock)，以防止所谓的“幽灵行问题”。 
    假设你想要从有一个标识符值大于100的child读并锁定所有某些记录，并想着随后在选定行中更新一些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

假设在id列有一个索引，查询从id大于100的第一个记录开始扫描。如果设置在索引记录上的锁定不把在间隙生成的插入排除在外，
    一个新行可能与此同时被插进表中。如果你在同一事务内执行同样的SELECT，你可能会在该查询返回的结果包里看到一个新行。
    这与事务的隔离原则是相反的：一个事务已经读的数据在事务过程中不被其他事务改变。如果我们把结果集视为一个数据项，
    新的“幽灵”记录出现会违反这一隔离原则。所以该例中，InnoDB设置的锁定会阻止child表中出现id大于100的记录
  • Insert Intention Locks：插入意向锁是一种Gap Lock类型，多个事务同时往同一个Gap插入数据时，只要不在同一位置插入就不冲突。插入意向锁是先于(插入行)排他锁而设置的。
  • 事务隔离级别：MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别，默认是可重复读的（REPEATABLE READ）。你可以在命令行用--transaction-isolation选项或在选项文件里，为所有连接设置隔离级别，或通过 SET TRANSACTION 
    SET [GLOBAL | SESSION] TRANSACTION
    
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic:
    
    ISOLATION LEVEL level
    
  | READ WRITE
    
  | READ ONLY

level:
    
     REPEATABLE READ
    
   | READ COMMITTED
    
   | READ UNCOMMITTED
    
   | SERIALIZABLE

    下面将分别介绍这四个事务隔离级别：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

    1· READ UNCOMMITTED
    
每个无格式SELECT都是非锁定非一致读，可能读到其他失败事务曾经修改过但尚未完成回滚的数据,也被称为“脏读”（Dirty Read）;除此而外，其他约束与READ COMMITTED一样作用。

2· READ COMMITTED
    
每个无格式SELECT都是非锁定一致读，拥有自己快照。SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE和DELETE仅锁定索引记录(使用Record Lock)，而不锁定记录前的间隙(不使用Gap Lock)，因而允许其他事务紧挨着已锁定的记录插入新记录。对于 UPDATE 或 DELETE，InnoDB先锁定那些未被锁定的行，然后对不满足WHERE条件的行释放锁，可以减少死锁发生的可能性；另外对于UPDATE，如果一个行已经被锁定，InnoDB先执行“半一致读”获取最新提交版本值，如果该值满足WHERE条件，InnoDB将再执行一次读并获取或等待锁定。 

3· REPEATABLE READ
    
每个无格式SELECT都是非锁定一致读，相同读共享同一快照(由第一次读所确定的快照)。SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE使用Record Lock或Gap Lock(Next-Key Lock)。SELECT可看到发布该SELECT的事务本身所做的改变。UPDATE 锁住所有可锁定的行(包括那些并不满足WHERE条件的行)，直到提交或回滚后再释放锁。

4· SERIALIZABLE
    
这个级别类似REPEATABLE READ。但在autocommit未开启情形下，无格式SELECT语句将被隐式转换成SELECT ... LOCK IN SHARE MODE；而在autocommit开启情形下，无格式SELECT语句是非锁定读且可串行化的。

    从上可知，READ UNCOMMITTED隔离约束最弱，SERIALIZABLE隔离约束最强，隔离约束强度逐级提高。

四. 行锁与表锁优劣对比

　　行级锁定的优点：

  • 当在许多线程中访问不同的行时只存在少量锁定冲突
  • 回滚时只有少量的更改
  • 可以长时间锁定单一的行

　　行级锁定的缺点：

  • 比页级或表级锁定占用更多的内存
  • 当在表的大部分数据集中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁
  • 如果你在大部分数据集上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多
  • 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定　　

　　MySQL表锁定机制：当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。这意味着，如果你在一个表上有许多更新，SELECT语句将等待直到没有更多的更新。表更新通常情况认为比表检索更重要，因此给予它们更高的优先级，但这应确保更新一个表的活动不能“饿死”，即使该表上有很繁重的SELECT活动

　　对WRITE，MySQL使用的表锁定方法原理如下：

  • 如果在表上没有锁，在它上面放一个写锁
  • 否则，把锁定请求放在写锁定队列中

　　对READ，MySQL使用的表锁定方法原理如下：

  • 如果在表上没有写锁定，把一个读锁定放在它上面
  • 否则，把锁请求放在读锁定队列中

　　在以下情况下，表锁定优于行级锁定：

  • 表的大部分语句用于读取
  • 对严格的unique_key进行读取和更新，你可以更新或删除可以用单一的读取的关键字来提取的一行： 
    UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

DELETE FROM tbl_name WHERE unique_key_col=key_value;

  • SELECT 结合并行的INSERT语句，并且只有很少的UPDATE或DELETE语句

  • 在整个表上有许多扫描或GROUP BY操作，没有任何写操作

  • 对于大表，对于大多数应用程序，表锁定比行锁定更好，但存在部分缺陷

　　表锁定的有关事项：

  • 使用SET LOW_PRIORITY_UPDATES=1语句可指定具体连接中的所有更新应使用低优先级；
    或用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级；
    或用HIGH_PRIORITY属性给与一个特定的SELECT语句较高优先级；
    或为max_write_lock_count系统变量指定一个低值来启动mysqld来强制MySQL在具体数量的插入完成后临时提高所有等待一个表的SELECT语句的优先级；
    或用--low-priority-updates启动mysqld，这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级

  • 如果你有关于INSERT结合SELECT的问题，切换到使用新的MyISAM表，因为它们支持并发的SELECT和INSERT；
    如果你对同一个表混合插入和删除，INSERT DELAYED将会有很大的帮助；
    如果你对同一个表混合使用SELECT和DELETE语句出现问题，DELETE的LIMIT选项可以有所帮助；
    对SELECT语句使用SQL_BUFFER_RESULT可以帮助使表锁定时间变短；
    可以更改mysys/thr_lock.c中的锁代码以使用单一的队列，在这种情况下，写锁定和读锁定将具有相同的优先级；
    如果不混合更新与需要在同一个表中检查许多行的选择，可以进行并行操作；
    可以使用LOCK TABLES来提高速度，因为在一个锁定中进行许多更新比没有锁定的更新要快得多，另外将表中的内容切分为几个表也可以有所帮助

五. 选择MyISAM

　　一般而言，是否使用MyISAM存储引擎，取决于主要的操作类型。如果是读多写少， 使用MyISAM会更佳，且MyISAM支持并发select和insert。存储引擎使用表级锁定不会发生死锁，因为所有必要的锁定在查询开始时就能以同样的顺序确定下来。

　　可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> SHOW STATUS LIKE 'Table%';

+-----------------------+---------+

| Variable_name         | Value   |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited    | 15324   |

+-----------------------+---------+

　　如果数据文件不包含空块(从表的中部删除行导致空洞），INSERT语句不冲突，可以*为MyISAM表混合并行的INSERT和SELECT语句而不需要锁定，你可以在其它客户正读取MyISAM表的时候插入行，记录总是插入在数据文件的尾部；如果不能同时插入，为了在一个表中进行多次INSERT和SELECT操作，可以在临时表中插入行并且立即用临时表中的记录更新真正的表，这也适合做批量延迟插入：

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;

mysql> INSERT INTO real_table SELECT * FROM insert_table;

mysql> TRUNCATE TABLE insert_table;

mysql> UNLOCK TABLES;

　　

六. 选择InnoDB

　　InnoDB使用行锁定，可能存在死锁。这是因为，在SQL语句处理期间，InnoDB自动获得行锁定，而不是在事务启动时获得。对于InnoDB和BDB(BerkeleyDB)表，如果你用LOCK TABLES显式锁定表，MySQL只使用表锁定；建议不要使用LOCK TABLES，InnoDB将自动行级锁定而BDB将页级锁定来保证事务隔离，效率更高。

　　InnoDB支持事务以及外键约束。

七. 悲观锁与乐观锁

　　悲观锁和乐观锁不是MySQL数据库中的标准概念，而只是一种通俗说法。

  • 悲观锁：悲观锁指对数据被意外修改持保守态度，依赖数据库原生支持的锁机制来保证当前事务处理的安全性，防止其他并发事务对目标数据的破坏或破坏其他并发事务数据，将在事务开始执行前或执行中申请锁定，执行完后再释放锁定。这对于长事务来讲，可能会严重影响系统的并发处理能力 
    LOCK TABLES a WRITE;
    
INSERT INTO a VALUES (1,23),(2,34),(4,33);
    
INSERT INTO a VALUES (8,26),(6,29);
    
UNLOCK TABLES;

    锁定表可以加速用多个语句执行的INSERT操作，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新，如果能用一个语句插入所有的行，就不需要锁定；对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入

  • 乐观锁：乐观锁相对悲观锁而言，先假想数据不会被并发操作修改，没有数据冲突，只在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则宣告失败，否则更新数据。这就要求避免使用长事务和锁机制，以免导致系统并发处理能力降低，保障系统生产效率。下面将说明使用乐观锁时的大致业务处理流程： 
    首 步：执行一次查询 select some_column as old_value from some_table where id = id_value (假设该值在当前业务处理过程中不会被其他并发事务修改)
    ...
    
第n步：old_value参与中间业务处理，比如old_value被自己修改 new_value = f(old_value)。这期间可能耗时很长，但不会为持有 some_column 而申请所在的行或表锁定，因此其他并发事务可以获得该锁
    ...
    
尾 步：执行条件更新 update some_table set some_column = new_value where id = id_value and some_column = old_value (条件更新中检查old_value是否被修改)

    有一种乐观锁是通过CAS(Compare and Swap)实现的，CAS本身是原子操作。CAS有3个操作数，目标属性V，旧的预期值A，要修改的新值B，当且仅当预期值A和当前V相同时，将V的值修改为B，否则失败重试。现代的CPU提供了特殊的指令，允许算法执行读-修改-写操作，而无需害怕其他线程同时修改变量，因为如果其他线程修改变量，那么CAS会检测它并失败，算法可以对该操作重新计算。

相关文章