事务控制语言TCL

lg
227 阅读7分钟

事务控制语言TCL

事务

  • 数据库事务指的是一组数据操作。
  • 主要用于处理操作量大，复杂度高的数据。
  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

事务必须满足的4个条件

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

事务控制语句

  • 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事物处理的方法

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

事务的创建

隐式事务

  • 事务没有明显的开启和结束的标记。如INSERT、UPDATE、DELETE语句。
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

显式事务

  • 事务具有明显的开启和结束的标记
  • 必须先设置自动提交功能为禁用
mysql> set autocommit=0;  # 只对当前会话生效
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
创建事务步骤
  1. 开启事务
set aotocommit=0;
start transaction;    # 可选

  1. 编写事务语句：INSERT、UPDATE、DELETE语句

  2. 结束事务

commit | rollback;
事务示例
  • 创建银行表
mysql> use mydb;
mysql> create table bank(
    ->   id int primary key,
    ->   name varchar(20),
    ->   balance int
    -> );
Query OK, 0 rows affected (0.01 sec)
  • 插入数据
mysql> insert into bank values
    -> (1, '牛犇', 10000), (2, '张志刚', 10000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
  • 使用事务：正常提交
mysql> set autocommit=0;
mysql> update bank set balance=balance-1000 where name='牛犇';
mysql> update bank set balance=balance+1000 where name='张志刚';
# 此时在另一终端查看bank表，数据并未改变
mysql> commit;
  • 使用事务：回滚
mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';
mysql> update bank set balance=balance-1000 where name='张志刚';
# 此时在另一终端查看bank表，数据并未改变
mysql> rollback;

事务隔离

事务隔离要解决的问题

  • 脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。
  • 可重复读：可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据**更新（UPDATE）**操作。
  • 不可重复读：对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据**更新（UPDATE）**操作。
  • 幻读：幻读是针对数据**插入（INSERT）**操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别

  • 读未提交（READ UNCOMMITTED）
  • 读提交 （READ COMMITTED）
  • 可重复读 （REPEATABLE READ）
  • 串行化 （SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

隔离级别出现脏读出现不可重读出现幻读
读未提交可能可能可能
读提交不可能可能可能
可重复读不可能不可能可能
串行化不可能不可能不可能

设置事务隔离级别

  • 查看当前事务隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
  • 设置隔离事务级别
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
  • 测试
# 在第一个终端上执行以下2条语句
mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';

# 以下3条语句在第二个终端上执行
mysql> set session transaction isolation level read uncommitted;
mysql> set autocommit=0;
mysql> select * from bank;   # 此时牛犇账户上已经增加1000

# 回到第一个终端回滚
mysql> rollback;

# 在第2个终端上重新查询
mysql> select * from bank;   # 此时牛犇账户上又减少了1000

SAVEPOINT应用

基本用法

  • 使用mysql中的savepoint保存点来实现事务的部分回滚
  • 语法：
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
  • 使用 SAVEPOINT identifier 来创建一个名为identifier的回滚点
  • ROLLBACK TO identifier，回滚到指定名称的SAVEPOINT，这里是identifier
  • 使用 RELEASE SAVEPOINT identifier 来释放删除保存点identifier
  • 如果当前事务具有相同名称的保存点，则将删除旧的保存点并设置一个新的保存点。
  • 如果执行START TRANSACTION，COMMIT和ROLLBACK语句，则将删除当前事务的所有保存点。

SAVEPOINT示例

mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';
mysql> savepoint aaa;    # 创建保存点
mysql> select * from bank;   # 牛老师账号已增加1000
mysql> update bank set balance=balance-1000 where name='张志刚';
mysql> select * from bank;   # 张志刚账号已减少1000
mysql> rollback to aaa;  # 回滚到保存点aaa
mysql> select * from bank;   # 牛老师账号已增加1000，张志刚账号未改变
mysql> exit;  # 退出
# 再连入之后查询
mysql> select * from bank;  # 因为从未执行过commit。所以查到的结果与执行事务之前查到的结果一样。
avatar
文章
阅读
粉丝