【mySQL】小册笔记 - 事务

BiaDisaForTruth
277 阅读9分钟

sample表

CREATE TABLE account (
    id INT NOT NULL AUTO_INCREMENT COMMENT '自增id',
    name VARCHAR(100) COMMENT '客户名称',
    balance INT COMMENT '余额',
    PRIMARY KEY (id)
) Engine=InnoDB CHARSET=utf8;

事务相关术语

事务4个特性

Atomicity - 原子性

  • 要么全做，要么全不做

Consistency - 一致性

如果数据库中的数据全部符合现实世界中的约束（all defined rules），我们说这些数据就是一致的，或者说符合一致性的。

  • CHECK()关键字
  • 定义触发器来定义一些约束条件

Isolation - 隔离性

保证其它的状态转换不会影响到本次状态转换，这个规则被称之为隔离性。

Durability - 持久性

持久性意味着该转换对应的数据库操作所修改的数据都应该在磁盘上保留下来，不论之后发生了什么事故，本次转换造成的影响都不应该被丢失掉

事务流程

把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称之为一个事务（英文名是：transaction）。

事务对应着若干个数据库操作，把这些操作执行的不同阶段划分为以下几个状态：

  • 活动的(active)

    事务对应的数据库操作正在执行过程中

  • 部分提交的（partially committed）

    事务操作都执行完成了（最后一个操作执行完成了），但操作都在内存中执行（缓存池中执行了），结果还没刷到磁盘中（脏页未从LRU或FLUSH更新到磁盘上）

  • 失败的（failed）

    事务处于active、parially committed状态时，遇到某些错误

    • 数据库自身错误（常见的比如id重复或非空字段为空等不满足约束条件）
    • 操作系统错误
    • 断电导致中断

    或手动停止当前事务，导致当前事务停止执行

  • 中止的（aborted）

    事务因执行事务后，变为失败状态而进行了执行操作的撤销（回滚）之后，事务就处于中止状态。

  • 提交的（committed）

    一个事务在部分提交状态下，将修改过的数据都同步到了磁盘后，事务就处于提交状态，即：提交状态是唯一的成功状态。

事务语法

事务的本质其实只是一系列数据库操作，只不过这些数据库操作符合ACID特性而已

  • 无论引擎是否支持事务，开启、关闭事务引擎的语句都是可以通过语法校验的，只是没有效果。

开启事务

可以采取以下两种方式，在SQL语句中开启事务：

  • BEGIN [WORK];

    BEGIN语句代表开启一个事务，后边的单词WORK可有可无。开启事务后，就可以继续写若干条语句，这些语句都属于刚刚开启的这个事务。

    mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> 加入事务的语句...

  • START TRANSACTION;

    START TRANSACTION语句和BEGIN语句有着相同的功效，都标志着开启一个事务，比如这样：

    mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> 加入事务的语句...

    不过比BEGIN语句牛逼一点儿的是，可以在START TRANSACTION语句后边跟随几个修饰符，就是它们几个：

    • READ ONLY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。

      小贴士：其实只读事务中只是不允许修改那些其他事务也能访问到的表中的数据，对于临时表来说（我们使用CREATE TMEPORARY TABLE创建的表），由于它们只能在当前会话中可见，所以只读事务其实也是可以对临时表进行增、删、改操作的。

    • READ WRITE：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。

    • WITH CONSISTENT SNAPSHOT：启动一致性读（先不用关心啥是个一致性读，后边的章节才会唠叨）。

    比如我们想开启一个只读事务的话，直接把READ ONLY这个修饰符加在START TRANSACTION语句后边就好，比如这样：

    START TRANSACTION READ ONLY;

    如果我们想在START TRANSACTION后边跟随多个修饰符的话，可以使用逗号将修饰符分开，比如开启一个只读事务和一致性读，就可以这样写：

    START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;

    或者开启一个读写事务和一致性读，就可以这样写：

    START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT

    不过这里需要大家注意的一点是，READ ONLY和READ WRITE是用来设置所谓的事务访问模式的，就是以只读还是读写的方式来访问数据库中的数据，一个事务的访问模式不能同时既设置为只读的也设置为读写的，所以我们不能同时把READ ONLY和READ WRITE放到START TRANSACTION语句后边。另外，如果我们不显式指定事务的访问模式，那么该事务的访问模式就是读写模式。

  • 事实上一般来说，需要开启事务的数据库语句组合，访问模式都需要为读写。

    • 只读的数据大部分情况下是允许不一致的，由于是读取数据库，因此也没有回滚的操作

提交事务

当最后一条语句写完了之后，我们就可以提交该事务了，提交的语句也很简单：

COMMIT [WORK]

COMMIT语句就代表提交一个事务，后边的WORK可有可无。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE account SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

手动中止事务

如果我们写了几条语句之后发现上边的某条语句写错了，我们可以手动的使用下边这个语句来将数据库恢复到事务执行之前的样子：

ROLLBACK [WORK]

ROLLBACK语句就代表中止并回滚一个事务，后边的WORK可有可无类似的。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE account SET balance = balance + 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

这里需要强调一下，ROLLBACK语句是我们程序员手动的去回滚事务时才去使用的，如果事务在执行过程中遇到了某些错误而无法继续执行的话，事务自身会自动的回滚。

小贴士： 我们这里所说的开启、提交、中止事务的语法只是针对使用黑框框时通过mysql客户端程序与服务器进行交互时控制事务的语法，如果大家使用的是别的客户端程序，比如JDBC之类的，那需要参考相应的文档来看看如何控制事务。

支持事务的存储引擎

除了总所周知的InnoDB外，NDB也支持事务。

现在MySQL中只有这两个存储引擎支持事务。

  • 如果某个事务中包含了修改使用 不支持事务引擎的表，那么如果进入了failed状态，不支持事务引擎的表是不会回滚的。

自动提交

相关说明

MySQL中有相关变量 autocommit,通过语句：

SHOW VARIABLES LIKE 'autocommit';

可以查看。一般默认是 on。这里的自动提交，指的是：

  • 每一个完整的操作，都被视作一次事务

一般有两种方法来绕过自动提交：

  • 显式使用相关的begin、commit等语法来进行事务范围的定义。

  • 将该系统变量修改为 off。

    这样的话，我们写入的多条语句就算是属于同一个事务了，直到我们显式的写出COMMIT语句来把这个事务提交掉，或者显式的写出ROLLBACK语句来把这个事务回滚掉。

隐式提交

当通过上述的方法绕过了自动提交事务就不会自动提交了。

但当某些特定语句被输入后，事务会进行 隐式提交。这些特定语句包括：

  • DDL（定义或修改数据库对象的数据定义语言，Data definition language）

所谓的数据库对象，指的就是数据库、表、视图、存储过程等等这些东西。当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时，就会隐式的提交前边语句所属于的事务，就像这样：

BEGIN;

SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其它语句

CREATE TABLE ... # 此语句会隐式的提交前边语句所属于的事务

  • 隐式使用或修改mysql数据库中的表

    当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。

  • 事务控制或关于锁定的语句

    当我们在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上一个事务，比如这样：

    BEGIN;

SELECT ... # 事务中的一条语句
UPDATE ... # 事务中的一条语句
... # 事务中的其它语句

BEGIN; # 此语句会隐式的提交前边语句所属于的事务

    或者当前的autocommit系统变量的值为OFF，我们手动把它调为ON时，也会隐式的提交前边语句所属的事务。

    或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前边语句所属的事务。

  • 加载数据的语句

    比如我们使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。

  • 关于MySQL复制的一些语句

    使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句时也会隐式的提交前边语句所属的事务。

  • 其它的一些语句

    使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。

保存点

如果开启了一个事务，同时并不想全部回滚，那么可以通过声明一些特定的位置，来进行指定位置的回滚。

这些声明的位置称为 保存点(savepoint)，定义语法如下：

SAVEPOINT 保存点名称;

当我们想回滚到某个保存点时，可以使用下边这个语句（下边语句中的单词WORK和SAVEPOINT是可有可无的）：

ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

不过如果ROLLBACK语句后边不跟随保存点名称的话，会直接回滚到事务执行之前的状态。

如果我们想删除某个保存点，可以使用这个语句：

RELEASE SAVEPOINT 保存点名称;

avatar
文章
阅读
粉丝