(十三)事务处理


1、概述

  事务是数据库区别于文件系统的重要特征之一,其用于保证数据库的完整性,事务能使批量的SQL语句要么完全执行,要么完全不执行。

  例如最简单的转账示例:假设用户A要给用户B转账100元,用户A的转账指令已经成功发出,而用户B由于未知的原因接收失败,如果两个命令单独执行,那么用户A的账户少了100元,但是用户B又没收到这100元,显然是不合理的;此时将用户A向用户B转账100元的这个过程当成一个事务处理,那么由于用户B接收的失败,整个转账事务都将失败,用户A不会减少100元,用户B更不会增加100元,这才是合理的情况。

  事务的特性有以下几点:

  • 原子性(Atomicity):事务对数据的修改,要么全部执行,要么全部不执行;
  • 一致性(Consistency):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;
  • 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行;
  • 持久性(Durability):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

2、控制事务处理

  事务处理的关键在于将SQL语句组分解为逻辑块,并明确规定在数据何时应该回退,何时不应该回退。

显式的开启一个事务

  START TRANSACTION;
  或
  BEGIN;

回滚

  回滚会结束用户的事务,并撤销正在进行的所有未提交的修改,分为直接回滚和部分回滚:

  • 直接回滚:会回滚到设置BEGIN之前的状态。
    ROLLBACK;

  • 部分回滚:通过设置保存点,回滚到保存点设置前的状态。
    设置一个事务保存点及标识符名称:
    SAVEPOINT identifier;
    回滚到设置保存点之前的状态:
    ROLLBACK TO SAVEPOINT identifier;
    从当前事务的一组保存点中删除指定的保存点:
    RELEASE SAVEPOINT identifier;
    使用COMMIT或ROLLBACK结束事务后,所有保存点会被删除。

提交事务

  使已对数据库进行的所有修改成为永久性的,分为自动提交和手动提交:

  • 自动提交:MySQL默认是自动提交的,即执行SQL语句后就会马上执行COMMIT操作。
  • 手动提交:如果正在使用的是一个事务安全型的存储引擎,如“InnoDB”,那么当显式的开启事务后,MySQL的自动提交会被禁用,直到使用COMMIT或ROLLBACK结束事务后,MySQL会恢复为自动提交。
    查询当前自动提交功能状态:
    SELECT @@AUTOCOMMIT;
    改变MySQL的自动提交模式:
    SET AUTOCOMMIT=0; 禁用
    SET AUTOCOMMIT=1; 启用
    仅对当前连接生效。

3、案例演示

  创建数据表“users”,并查看其存储引擎为“InnoDB”:


  查看此时的自动提交状态,并修改为禁用自动提交:


  禁用自动提交后,查看数据表记录,此时为空,添加两条记录,显示成功,但注意目前并未提交,即这两条记录没有被存储到磁盘中,因此调用另一个MySQL窗口查看该表时,其数据仍然为空:


  此时执行COMMIT语句,记录被写入磁盘,调用其他的MySQL窗口也可以查询到数据:


  若此时执行ROLLBACK语句,则会回滚到添加记录之前,查询数据,显示为空:


注意:
  之所以没有回到创建数据表之前的状态,而仅仅是记录消失,是因为有些SQL语句会隐式的结束一个事务,即使用了COMMIT语句。
  例如:
ALTER FUNCTION、ALTER PROCEDURE、ALTER TABLE
BEGIN
CREATE DATABASE、CREATE FUNCTION、CREATE INDEX、CREATE PROCEDURE、CREATE TABLE
DROP DATABASE、DROP FUNCTION、DROP INDEX、DROP PROCEDURE、DROP TABLE
LOAD MASTER DATA、LOCK TABLES
RENAME TABLE
SET AUTOCOMMIT=1
START TRANSACTION
TRUNCATE TABLE
UNLOCK TABLES

  之后修改为自动提交,添加两条记录后,数据会被直接写入磁盘,调用其他的MySQL窗口也可以查询到数据:


  此时显示的创建一个事务,会默认禁用自动提交,因此添加记录后,在其他的MySQL窗口无法查询到数据:


  在添加用户“Alice”后,创建保存点“sp1”;之后清空数据表记录,查询后显示该表记录为空,回滚到保存点“sp1”的位置,再次查询数据后,显示用户“Alice”也在数据表中;此时执行ROLLBACK语句,会直接回滚到创建事务时的状态,即添加用户“Alice”之前,因此查询数据显示只有4条记录:


注意:
  有些语句不能被回滚,通常包括数据定义语言语句(DDL),例如创建或取消数据库的语句,和创建、取消或更改表或存储的子程序的语句。
  因此在设计事务时,不应包含这类语句。如果在事务的前部调用了一个不能被回滚的语句,则后部的其它语句会发生错误,在这些情况下,通过使用ROLLBACK语句不能回滚事务的全部效果。


4、锁定机制

  上述操作都是针对单个连接进行的,但实际会存在多个连接同时操作数据的情况,举例来说,如果用户A和用户B几乎同时登录到订票APP,发现某场次电影的最佳观影位置只剩最后一张票,于是用户都纷纷下单,此时冲突就产生了。对于这种当多个连接对记录进行修改的操作,为保证数据的一致性和完整性,需要用到锁系统:

  • 共享锁(读锁):
    在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会有任何变化;
  • 排他锁(写锁):
    在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或写锁操作。

语法结构
LOCK TABLES
tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

注意:
  写锁定通常比读锁定拥有更高的优先权,以确保更新被尽快地处理。这意味着,如果一个线程获得了一个读锁定,则另一个线程会申请一个写锁定,后续的读锁定申请会等待,直到写线程获得锁定并释放锁定。此时可以使用LOW_PRIORITY WRITE锁定来允许其它线程在该线程正在等待写锁定时获得读锁定。前提是当此时没有线程拥有读锁定时,才应该使用LOW_PRIORITY WRITE锁定。

  对于锁系统还需要了解锁的粒度,即锁定时的单位。原则上只需要对待修改的数据精确加锁即可,而不是所有资源都枷锁,以减少系统的开销:

  • 表锁,是一种占有系统开销最小的锁策略;
  • 行锁,是一种占有系统开销最大的锁策略。

5、隔离级别

  在SQL的标准中,定义了四种隔离级别。每一种级别都规定了,在一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的:

  1. READ UNCOMMITTED (未提交读) :可以读取未提交的记录,会出现脏读。
  2. READ COMMITTED (提交读) :事务中只能看到已提交的修改。但不可重复读,会出现幻读。(在InnoDB中,会加行锁,但是不会加间隙锁)该隔离级别是大多数数据库系统的默认隔离级别。
  3. REPEATABLE READ (可重复读) :在InnoDB中是这样的:RR隔离级别保证对读取到的记录加锁 (行锁),同时保证对读取的范围加锁,新的满足查询条件的记录不能够插入 (间隙锁),因此不存在幻读现象,但是标准的RR只能保证在同一事务中多次读取同样记录的结果是一致的,而无法解决幻读问题。InnoDB的幻读解决是依靠MVCC的实现机制做到的,该隔离级别是MySQL的默认隔离界别。
  4. SERIALIZABLE (可串行化):该隔离级别会在读取的每一行数据上都加上锁,退化为基于锁的并发控制,即LBCC。
隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED
READ COMMITTED ×
REPEATABLE READ × ×
SERIALIZABLE × × ×

查看隔离级别
全局隔离:SELECT @@global.tx_isolation;
会话隔离:SELECT @@tx_isolation;

修改隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE};

  低级别的隔离可以执行更高级别的并发,性能好,但是会出现脏读、幻读等错误的现象:

更新丢失

  当两个或多个事务选择同一条记录,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生更新丢失的问题,即最后的更新覆盖了之前由其他事务所做的更新。

脏读

  一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;此时,另一个事务也来读取同一条记录,如果不加控制,第二个事务会读取到尚未提交的脏数据,并据此做进一步的处理,这种现象被形象地叫做"脏读"。

左侧为A窗口,右侧为B窗口
左侧为A窗口,右侧为B窗口

  如案例所示,将A窗口隔离级别修改为允许脏读后,显式创建事务,并修改用户名,此时A窗口始终未执行提交指令,意味着数据其实并没有写入磁盘,因此在B窗口首次查询时并没有出现修改后的记录,但是将B窗口的隔离级别也修改至允许脏读后,再次查询发现脏数据被读出来了。

不可重复读

  一个事务在读取某些数据后,再次读取之前读过的数据,却发现这些数据已经被另一个已提交的事务修改过,这种现象被称为“不可重复读”。

左侧为A窗口,右侧为B窗口
左侧为A窗口,右侧为B窗口

  如案例所示,将A窗口隔离级别修改为允许不可重复读后,显式创建事务,并修改用户名,假设在A窗口执行提交指令前,B窗口进行了第一次查询,显示的是原始记录,但在B窗口查询不久后,A窗口执行了提交指令,因此当B窗口再次查询相同的数据时,数据发生了变化,即为不可重复读。

幻读

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

左侧为A窗口,右侧为B窗口
左侧为A窗口,右侧为B窗口

  如案例所示,将A窗口隔离级别修改为允许幻读后,显式创建事务,并添加新用户,假设在A窗口执行提交指令前,B窗口进行了第一次查询,显示的是原始记录,但在B窗口查询不久后,A窗口执行了提交指令,因此当B窗口再次执行相同的查询指令时,出现了新的记录,即为幻读。


5、死锁

  死锁是指两个或者多个事务在同一资源上相互作用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时,就可能产生死锁。多个事务同时锁定同一个资源时,也会产生死锁。

左侧为A窗口,右侧为B窗口
左侧为A窗口,右侧为B窗口

  如案例所示,在A窗口显式创建事务,并修改id为3的用户名称,之后在B窗口显式创建事务并修改id为6的用户名称,此时在A窗口再次修改id为6的用户名称,A窗口会等待B窗口提交后释放锁定,在B窗口修改id为3的用户名称,B窗口也会等待A窗口提交后释放锁定,此时互相等待陷入死锁,这时MySQL会使B窗口强制解除锁定,使A窗口继续执行。


6、事务处理的SQL语句汇总

  • 显式的开启一个事务
    START TRANSACTION;

    BEGIN;

  • 回滚
    直接回滚:
    ROLLBACK;
    部分回滚:
    设置一个事务保存点及标识符名称:SAVEPOINT identifier;
    回滚到设置保存点之前的状态:ROLLBACK TO SAVEPOINT identifier;
    从当前事务的一组保存点中删除指定的保存点:RELEASE SAVEPOINT identifier;

  • 提交事务
    COMMIT;
    查询当前自动提交功能状态:
    SELECT @@AUTOCOMMIT;
    改变MySQL的自动提交模式:
    SET AUTOCOMMIT=0; 禁用
    SET AUTOCOMMIT=1; 启用

  • 锁定
    LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
    UNLOCK TABLES

  • 隔离级别
    查看隔离级别:
    全局隔离:SELECT @@global.tx_isolation;
    会话隔离:SELECT @@tx_isolation;
    修改隔离级别:
    SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
    {READ UNCOMMITTED | READ COMMITTED
    | REPEATABLE READ | SERIALIZABLE};


版权声明:欢迎转载,欢迎扩散,但转载时请标明作者以及原文出处,谢谢合作!             ↓↓↓

推荐阅读更多精彩内容