事务 是 DBMS 中一个非常重要的基本概念,主要用于处理操作量大,复杂度高的数据,比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!当我们在启动一个事务时,这里就引出了锁概念,并且这 2 个者对于初学者容易搞混淆。虽然事务和锁的基本原理是一样的,但是不同 RDBMS 的实现是有差别的,这里还是以 MySQL 中的事务和锁来讲解和说明基本操作。

知识要点

  • 事务基本概念
  • MySQL 事务操作
  • MySQL 锁

事物

像上面说的一样,在使用 MySQL 处理大量数据操作或负载数据操作时,就会启用事务!一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(一致性)、Isolation(隔离性)、Durability(持久性)

  1. 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
  2. 一致性 : 在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  3. 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
  4. 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

在 MySQL 中要使用事务时,需要注意以下内容:

  • 在MySQL中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
  • 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
  • 事务主要用来管理insert,update,delete语句

在我们使用 insert,update,delete 语句时,会自动启用事务。事务其实也会影响到 select 语句的查询结果,这个主要是受 MySQL 事务隔离级别决定的。对于表结构更改语句,不受事务控制,只要执行会立即提交当前更改。

在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

事务隔离级别

SQL 标准中定义了 4 个隔离级别:未提交读(Read uncommitted)、已提交读(Read committed)、可重复读(Repeatable read)、可序列化(Serializable)

  • 未提交读(Read uncommitted):一个事务修改了一行,另一个事务也可以读到该行。
  • 已提交读(Read committed(RC)):试图通过只读取提交的值的方式来解决脏读的问题,但是这又引起了不可重复读取的问题。
  • 可重复读(Repeatable read(RR)):在一个事务对数据行执行读取或写入操作时锁定了这些数据行,但是这种方式又引发了幻想读的问题。 因为只能锁定读取或写入的行,不能阻止另一个事务插入数据,后期执行同样的查询会产生更多的结果。InnoDB通过多版本并发控制机制(MVCC)解决的幻读的问题。
  • 可序列化(Serializable):事务被强制为依次执行。这是 SQL 标准建议的默认行为。

上面可以看到不同的隔离级别会导致数据的脏读、不可重复读或幻读,这个也是通常说的事务的级别,它们关系如下:

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

比较常用的级别是:RC 和 RR。

在 MySQL 中可以查看 当前会话的事务级别系统当前事务 级别。

MySQL 事务控制语句

  • BEGIN或START TRANSACTION;显式地开启一个事务;
  • COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;
  • ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
  • SAVEPOINT identifier;SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
  • RELEASE SAVEPOINT identifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
  • ROLLBACK TO identifier;把事务回滚到标记点;
  • SET TRANSACTION;用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MySQL 事务处理的主要两种方式:

  1. 用 BEGIN, ROLLBACK, COMMIT来实现
    • BEGIN 开始一个事务
    • ROLLBACK 事务回滚
    • COMMIT 事务确认
  2. 直接用 SET 来改变 MySQL 的自动提交模式:
    • SET AUTOCOMMIT=0 禁止自动提交
    • SET AUTOCOMMIT=1 开启自动提交

测试

  1. 使用 navcat 打开 2 个查询页面,分别制定为 Query1Query2

  2. Query2 中查询班级所有信息,看现在表表中已有的数据,和后面做对比

    select * from class;
    
  3. Query1 启动一个事务,且插入一条数据,但是不要提交事务

    # 开启事务
    begin;
    # 插入数据
    INSERT INTO `sql-learn`.`class`
    VALUES (-1, '事务测试 1', '老师1', '2018-04-02', '2018-04-18', 1, '2018-04-18 14:16:22');
    
  4. 再在 Query2 中执行第 2 步中的查询语句,现在看到的数据,应该和第 2 步中的一样

  5. 现在在 Query1 提交事务

    # 提交事务
    commit
    
  6. 最后在 Query2 中执行第 2 步中相同的查询语句,这时会在返回结果的第一行看到我们我们刚插入的测试数据。

事务的隔离级别就是由锁实现的,理解清楚它们之间的关系对分析和理解问题会有很大帮助。

共享锁和排他锁:在一般的应用中,为了应付大量并发,我们一般使用一次封锁法,在方法的开始阶段,已经预先知道会用到哪些数据,然后全部锁住,在方法运行之后,再全部解锁。在这种方法在数据库中却不适用,因为在事务开始阶段,数据库并不知道会用到哪些数据。数据库遵循的是两段锁协议,将事务分成两个阶段,加锁阶段和解锁阶段(所以叫两段锁)。

  • 加锁阶段:在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁(共享锁,其它事务可以继续加共享锁,但不能加排它锁),在进行写操作之前要申请并获得X锁(排它锁,其它事务不能再获得任何锁)。加锁不成功,则事务进入等待状态,直到加锁成功才继续执行。
  • 解锁阶段:当事务释放了一个封锁以后,事务进入解锁阶段,在该阶段只能进行解锁操作不能再进行加锁操作。

大多数数据库的MVCC通过对数据版本的乐观锁实现。何谓数据版本?即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。

  • MySQL 做种类很多,常见的有:表级锁、行级锁和页级索。不多存储引擎支持有所不同。
  • 所有表类型都支持表级锁,但是 MyISAM 只支持表级锁
  • 有两种类型的表级锁:读锁(共享锁)和写锁(排它锁)。
    • 读锁是共享锁,支持并发读,写操作被锁。
    • 写锁是独占锁,上锁期间其他线程不能读表或写表。

不同事务隔离级别,读和写的所操作有所不同,(在分析的时候还需要结合表存储引擎支持的锁种类进行)

  1. 在 RC 级别下,数据的读取都是不加锁的,但是数据的写入、修改和删除是需要加锁的。
  2. RR 级别(InnoDB 默认使用此级别)
    1. 读:读就是可重读,可重读这个概念是一事务的多个实例在并发读取数据时,会看到同样的数据行。
    2. 写:针对需要修改的数据加入排它锁

总结

  • 事务:事务基本特性、开始事务、事务提交、事务回滚
  • 事务隔离级别
  • 锁:MySQL锁类型和事务的关系