MySQL——事务与锁

904 阅读20分钟

MySQL的事务

        事务就是以可控的方式对数据资源进行访问的一组操作。事务本身持有4个限定属性,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability),这也就是常说的事务的ACID属性。

         MySQL服务器层不管理事务,事务是由下层的存储引擎实现的。MySQL默认采用自动提交(AUTOCOMMIT)模式。也就是说,如果不是显式地开始一个事务,则每个查询都被当作一个事务执行提交操作。在当前连接中,可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式:1或者0N表示启用,0或者0FF表示禁用。

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set

mysql> 

       当AUTOCOMMIT=0时,所有的查询都是在一个事务中,直到显式地执行COMMIT提交或者ROLLBACK回滚,该事务结束,同时又开始了另一个新事务。

隔离级别

       MySQL可以下面命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。

mysql> set session transaction isolation level 设置事务隔离级别

        在SQL标准中定义了四种隔离级别,每一种级别都规定了一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。较低级别的隔离通常可以执行更高的并发,系统的开销也更低。

  • READ UNCOMMITTED(未提交读):事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读(Dirty Read)。在实际应用中一般很少使用。
  • READ COMMITTED(已提交读):一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatableread),因为两次执行同样的查询,可能会得到不一样的结果。
  • REPEATABLE READ(可重复读):该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row)。InnoDB存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)解决了幻读的问题。 可重复读是MySQL的默认事务隔离级别。
  • SERIALIZABLE(可串行化):最高的隔离级别。它通过强制事务串行执行,避免了的幻读的问题。SERIALIZABLE会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别。

事务日志

       事务日志可以帮助提高事务的效率。使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。

事务的实现

       事务的隔离性由下面的锁得以实现。原子性、一致性、持久性通过数据库的redo和undo来完成。

重做(redo)日志

         在InnoDB存储引擎中,事务日志通过重做(redo)日志文件和InnoDB存储引擎的日志缓冲(InnoDB Log Buffer)来实现。当开始一个事务时,会记录该事务的一个LSN(LogSequence Number,日志序列号);当事务执行时,会往InnoDB存储引擎的日志缓冲里插入事务日志;当事务提交时,必须将InnoDB存储引擎的日志缓冲写入磁盘。事务日志持久以后,内存中被修改的数据在后台可以慢慢地刷回到磁盘。通常称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。

       InnoDB存储引擎通过预写日志的方式来保证事务的完整性。这意味着磁盘上存储的数据页和内存缓冲池中的页是不同步的,对于内存缓冲池中页的修改,先是写入重做日志文件,然后再写入磁盘,因此是一种异步的方式。

回滚(undo)日志

        重做日志记录了事务的行为,可以很好地通过其进行“重做”。但是事务有时还需要撤销,这时就需要undo。undo与redo正好相反,对于数据库进行修改时,数据库不但会产生redo,而且还会产生一定量的undo,即使你执行的事务或语句由于某种原因失败了,或者如果你用一条ROLLBACK语句请求回滚,就可以利用这些undo信息将数据回滚到修改之前的样子。与redo不同的是,redo存放在重做日志文件中,undo存放在数据库内部的一个特殊段(segment)中,这称为undo段(undo segment),undo段位于共享表空间内。

        我们通常对于undo有这样的误解:undo用于将数据库物理地恢复到执行语句或事务之前样子——但事实并非如此。数据库只是逻辑地恢复到原来的样子,所有修改都被逻辑地取消,但是数据结构本身在回滚之后可能大不相同,因为在多用户并发系统中,可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对于数据记录的并发访问。 如一个事务在修改当前一个页中某几条记录,但同时还有别的事务在对同一个页中另几条记录进行修改。因此,不能将一个页回滚到事务开始的样子,因为这样会影响其他事务正在进行的工作。

        例如:我们的事务执行了一个INSERT10万条记录的SQL语句,如果我们执行ROLLBACK时,会将插入的事务进行回滚。因此,当lnnoDB存储引擎回滚时,它实际上做的是与先前相反的工作。对于每个INSERT,InnoDB存储引擎会完成一个DELETE;对于每个DELETE,InnoDB存储引擎会执行一个INSERT;对于每个UPDATE,InnoDB存储引擎则会执行一个相反的UPDATE,将修改前的行放回去。

在事务中混合使用存储引擎

        MySQL服务器层不管理事务,事务是由下层的存储引擎实现的。所以在同一个事务中,使用多种存储引擎是不可靠的。 如果在事务中混合使用了事务型和非事务型的表(例如InnoDB和MyISAM表),在正常提交的情况下不会有什么问题。 但如果该事务需要回滚,非事务型的表上的变更就无法撤销,这会导致数据库处于不一致的状态,这种情况很难修复,事务的最终结果将无法确定。所以,为每张表选择合适的存储引擎非常重要。

隐式和显式锁定

        锁定InnoDB采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中,随时都可以执行锁定,锁只有在执行COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定,InnoDB会根据隔离级别在需要的时候自动加锁。 

分布式(XA)事务

         InnoDB存储引擎支持XA事务,通过XA事务可以来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源(transactional resources)参与一个全局的事务中。事务资源通常是关系型数据库系统,但也可以是其他类型的资源。全局事务要求在其中所有参与的事务要么都提交、要么都回滚,这对于事务原有的ACID要求又有了提高。另外,在使用分布式事务时,InnoDB存储引擎的事务隔离级别必须设置为SERIALIABLE。

           分布式事务由一个或者多个资源管理器(Resource Managers)、一个事务管理器(Transaction Manager)以及一个应用程序(Application Program)组成。 

  • 资源管理器:提供访问事务资源的方法。通常一个数据库就是一个资源管理器。 
  • 事务管理器:协调参与全局事务中的各个事务。需要和参与全局事务中的所有资源管理器进行通信。 
  • 应用程序:定义事务的边界,指定全局事务中的操作。 

      分布式事务使用两段式提交(two-phase commit)的方式。在第一个阶段,所有参与全局事务的节点都开始准备(PREPARE),告诉事务管理器它们准备好提交了。第二个阶段,事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交,则所有的节点都被告知需要回滚。

      实际上,在MySQL中有两种XA事务。一方面,MySQL可以参与到外部的分布式事务中;另一方面,还可以通过XA事务来协调存储引擎和二进制日志。

内部XA事务

        MySQL本身的插件式架构导致在其内部需要使用XA事务。MySQL中各个存储引擎是完全独立的,彼此不知道对方的存在,所以一个跨存储引擎的事务就需要一个外部的协调者。如果不使用XA协议,例如,跨存储引擎的事务提交就只是顺序地要求每个存储引擎各自提交。如果在某个存储提交过程中发生系统崩溃,就会破坏事务的特性。

        如果将MySQL记录的二进制日志操作看作一个独立的“存储引擎”,就不难理解为什么即使是一个存储引擎参与的事务仍然需要XA事务了。在存储引擎提交的同时,需要将“提交”的信息写入二进制日志,这就是一个分布式事务,只不过二进制日志的参与者是MySQL本身。 XA事务为MySQL带来巨大的性能下降。

外部XA事务

        MySQL能够作为参与者完成一个外部的分布式事务。但它对XA协议支持并不完整,例如,XA协议要求在一个事务中的多个连接可以做关联,但目前的MySQL版本还不能支持。

       因为通信延迟和参与者本身可能失败,所以外部XA事务比内部消耗会更大。如果在广域网中使用XA事务,通常会因为不可预测的网络性能导致事务失败。如果有太多不可控因素,例如,不稳定的网络通信或者用户长时间地等待而不提交,则最好避免使用XA事务。任何可能让事务提交发生延迟的操作代价都很大,因为它影响的不仅仅是自己本身,它还会让所有参与者都在等待。

        通常,还可以使用别的方式实现高性能的分布式事务。例如,可以在本地写入数据,并将其放入队列,然后在一个更小、更快的事务中自动分发。还可以使用MySQL本身的复制机制来发送数据。我们看到很多应用程序都可以完全避免使用分布式事务。 

       XA事务是一种在多个服务器之间同步数据的方法。如果由于某些原因不能使用MySQL本身的复制,或者性能并不是瓶颈的时候,可以尝试使用。

  在实际的数据库系统中,每时每刻都在发生锁定,当某个用户在修改某一部分数据时,MySQL会通过锁定防止其他用户读取同一数据。大多数时候,MySQL锁的内部管理都是透明的。

什么是锁

  锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。

锁的类型

共享锁与排他锁

  无论何时,只要有多个查询需要在同一时刻修改数据,都会产生并发控制的问题。在处理并发读或者写时,可以通过实现一个由两种类型的锁组成的锁系统来解决问题。

  这两种类型的锁通常被称为共享锁(shared lock)和排他锁(exclusive lock),也叫读锁(read lock)和写锁(write lock)。

InnoDB 存储引擎实现了如下两种标准的行级锁:

  • 共享锁(S Lock):允许事务读一行数据。

  • 排他锁(X Lock):允许事务删除或者更新一行数据。

  当一个事务已经获得了行 r 的共享锁,那么另外的事务可以立即获得行的共享锁,因为读取并没有改变行的数据,我们称这种情况为锁兼容。但如果有事务想获得行 r 的排他锁,则它必须等待事务释放行 r 上的共享锁一这种情况我们称为锁不兼容。

意向锁

  此外,InnoDB存储引擎支持多粒度锁定,这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作,InnoDB 存储引擎支持一种额外的锁方式,称之为意向锁 (Intention Lock)。意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度上进行加锁。

  InnoDB 存储引擎支持意向锁设计比较简练,其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:

  • 意向共享锁(IS Lock),事务想要获得一个表中某几行的共享锁。

  • 意向排他锁(IX Lock),事务想要获得一个表中某几行的排他锁。

  由于InnoDB存储引擎支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫以外的任何请求。

兼容性如下:

image.png

锁粒度

  一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据,而不是所有的资源。更理想的方式是,只对会修改的数据片进行精确的锁定。 任何时候,在给定的资源上,锁定的数据量越少,则系统的并发程度越高,只要相互之间不发生冲突即可。

表锁(table lock)

  表锁是MySQL中最基本的锁策略,并且是开销最小的策略。它会锁定整张表。一个用户在对表进行写操作(插入、删除、更新等)前,需要先获得写锁,这会阻塞其他用户对该表的所有读写操作。

  只有没有写锁时,其他读取的用户才能获得读锁,读锁之间是不相互阻塞的。 在特定的场景中,表锁也可能有良好的性能。例如,READ LOCAL表锁支持某些类型的并发写操作。另外,写锁也比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁队列的前面(写锁可以插入到锁队列中读锁的前面,反之读锁则不能插入到写锁的前面)。

  尽管存储引擎可以管理自己的锁,MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如,服务器会为诸如ALTER TABLE之类的语句使用表锁,而忽略存储引擎的锁机制。

行级锁(row lock)

  行级锁可以最大程度地支持并发处理(同时也带来了最大的锁开销)。在InnoDB和存储引擎中实现了行级锁。行级锁只在存储引擎层实现,而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。

多版本并发控制

  如果读取的行正在执行 DELETE 或 UPDATE 操作,这时读取操作不会因此去等待行上锁的释放。相反地,InnoDB存储引擎会去读取行的一个快照数据。InnoDB存储引擎通过行多版本控制(multi versioning)的方式实现。

image.png

  不仅是MySQL,包括Oracle、PostgreSQL等其他数据库系统也都实现了MVCC,但各自的实现机制不尽相同。 可以认为MVCC是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。实现了非阻塞的读操作,写操作也只锁定必要的行。

  MVCC的实现,是通过保存数据在某个时间点的快照来实现的。也就是说,不管需要执行多长时间,每个事务看到的数据都是一致的。根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。

  InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值,而是系统版本号(system version number)。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。

下面看一下在REPEATABLE READ隔离级别下,MVCC具体是如何操作的。

SELECT

InnoDB会根据以下两个条件检查每行记录:

  • InnoDB只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的。
  • 行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。 只有符合上述两个条件的记录,才能返回作为查询结果。

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE

InnoDB为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。

小结

  保存这两个额外系统版本号,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行。

  不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作。

  MVCC 只在可重复读和已提交读两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为未提交读总是读取最新的数据行,而不是符合当前事务版本的数据行。而可串行化则会对所有读取的行都加锁。

自增长和锁

  自增长在数据库中是非常常见的一种属性,也是很多首选的主键方式。 在 InnoDB 存储引擎的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。

  当对含有自增长计数器的表进行插入操作时,这个计数器会被初始化;插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的SQL语句后立即释放。

  虽然AUTO-INC Locking从一定程度上提高了并发插入的效率,但这里还是存在一些问题。首先,对于有自增长值的列的并发插入性能较差,所以必须等待前一个插入的完成(虽然不用等待事务的完成)。其次,对于INSERT......SELECT的大数据量的插入,会影响插人的性能,因为另一个事务中的插入会被阻塞。

  从 MySQL5.1.22 版本开始,InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode,默认值为1。有三个可选值:

  • 0:这是5.1.22版本之前自增长的实现方式,即通过表锁的AUTO-INC Locking方式。因为有了新的自增长实现方式,所以 0 这个选项不应该是你的首选项。

  • 1(默认值):简单查询,该值会用互斥量(mutex )去对内存中的计数器进行累加的操作。对于 INSERT......SELECT,使用传统表锁的AUTO-INC Locking 方式。

  • 2:在这个模式下,对于所有 INSERT 自增长值的产生都是通过互斥量,而不是 AUTO-INC Locking 的方式。显然,这是最高性能的方式。然而,这会带来一定的问题。因为并发插入的存在,所以每次插入时,自增长的值可能不是连续的。此外,最重要的是,基于主从复制会出现问题。因此,使用这个模式,应该使用基于基于行的复制。这样才能保证最大的并发性能和数据的同步。

锁的算法

InnoDB 存储引擎有3中行锁的算法设计,分别是:

  • Record Lock:单个行记录上的锁。

  • Gap Lock:间隙锁,锁定一个范围,但不包含记录本身。

  • Next-Key Lock:Gap Lock+Record Lock,锁定一个范围,并且锁定记录本身。

  Record Lock 总是会去锁住索引记录。如果 InnoDB 存储引擎表建立的时候没有设置任何一个索引,这时 InnoDB 存储引擎会使用隐式的主键来进行锁定

  Next-Key Lock 是结合了 Gap Lock 和Record Lock 的一种锁定算法,在 Next-Key Lock 算法下,InnoDB 对于行的查询都是采用这种锁定算法。例如一个索引有 10, 11,13和20这四个值,那么该索引可能被 Next-Key Locking 的区间为: (-∞,10] (10,11] (11,13] (13,20] (20,+∞)

参考

高性能MySQL
MySQL技术内幕:InnoDB存储引擎