MYSQL事务(5)

x33g5p2x  于2021-08-23 转载在 Mysql  
字(3.1k)|赞(0)|评价(0)|浏览(367)

一 前言

本篇内容是关于SQL事务知识,主要包括事务的概念,ACID,幻读,不可重复度,脏度等内容;

二 事务的概念

事务意指一组原子性的的SQL操作,即保证一组 SQL 语句要么全部执行,要么全部不执行;

一个经典的案例银行转账: 小知转账100元给小识,操作步骤如下

  1. 银行查询小知账号金额大于等于100
  2. 银行从小知账号扣除100元
  3. 银行将小识账号金额增加100元

假设 有张金额表 money,对应SQL语句如下

  1. begin;
  2. select balance from money where username = 'xiaozhi' and balance > = '100';
  3. update money set balance = balance - 100 where username = 'xiaozhi';
  4. update money set balance = balance + 100 where username = 'xiaoshi';
  5. commit;

如上语句就是 一次原子性操作,begin 为开启事务, commit为提交事务;假设没有begin 和 commit ,在执行语句3的时候发生了断电,小知的账号金额扣除了100,但小识的金额却没有加上100,这就造成了数据的不一致,故事务在SQL中占有主导性地位,特别是关于金额类操作;学习事务必须满足4个条件(ACID), 原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。

三 ACID

3.1 原子性

原子性的概念比较简单,就是事务被当初一个最小的单位,其不可分割,并且整个事务中的操作要么全部失败,要么全部成功;

3.2 一致性

一致性 的意思就是 数据从一种状态转移至另一种状态,其数据完整没有被破坏;如上例子小知扣除100元后,小识账号金额没有增加100,这就是状态转移后破坏了数据的一致性;

3.3 隔离性

隔离性是指,一个事务在做出修改后至到事务提交前,其它的事务是不可见的;如上例子小知账号有500元 扣除100元后, 假设有另外一个事务参与进来,其是不能看见小知账号为400元,应该是500元;

3.4 持久性

持久性的概念更加简单,即一旦事务提交,则数据修改操作永久保存到数据库中;如果服务器发生故障则不会对持久化的数据产生任何影响;

四 事务的简单操作

4.1 事务语句

  • bengin 或者 start transction 表示开启事务;
  • commit也可以使用 commit work 表示提交事务,提交事务后数据就持久化了;
  • rollback 也可以使用 rollback work 表示回滚事务,即我后悔刚刚的SQL操作,一切不算数;
  • savepoint identifier,savepoint 允许在事务中创建一个保存点,一个事务中可以有多个 savepoint ;
  • release savepoint identifier 删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
  • rollback to identifier 事务回滚到保存点;
  • set transction 用来设置事务的隔离级别;

mysql 中 常用 的就是 begin commit rollback 三个语句;

4.2 事务的隔离级别

事务的隔离级别在每种数据库的存储引擎中都不一样,一般是提交读,但mysql使用InnoDB 时 是可重复度;

  • 读未提交(read uncommited) 即事务修改后语句后并没有提交,其修改的内容对其它事务是可见的,此时就会出现脏读,如上例子 小知账号500扣除 100 元 ,被其它事务读看见读取了400元,就出现了脏度;所以读未提交在实际生产环境中基本不会使用到
  • 读已提交 (read commited) 即一个事务只能读取到另一个事务已经提交后的数据;如上例子假设小知账号金额500元, 小知账号金额扣除100,小识账号金额 加100,事务提交后,另一个事务读取小知账号金额400;
  • 可重复读 (repeatable read)即同一个事务多次读取的数据前后一致;如上例子,小知账号500元,当执行如上操作事务完成后,另一个事务读取n次小知的账号都是400元;
  • 可串行化 (serializable)即在每行的数据上都加上一行读锁,会导致锁竞争问题,数据库性能会降低;事务的最高级别;实际生产环境中也很少用到;

mysql 中可以使用 set transction 来设置 事务的隔离级别 即(read-uncommitted、read-committed、repeatable-read 和 serializable)

-- mysql 5版本
select @@tx_isolation;
set tx_isolation = 'read-uncommitted';

-- mysql 8版本 
select @@transaction_isolation;
set transaction_isolation = '';

4.3 事务并发问题

  • 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
  • 不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
  • 幻读:A将数据库中的数据进行修改,但是B就在这个时候插入了一条数据,当A改完后发现还有一条记录没有改过来,这就叫幻读。所以幻读针对插入语句

mysql 的 InnoDB 通过 多版本并发控制 (MVCC) 解决了幻读问题;

4.3 实际操作

一张顾客表,建表语句如下

CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_name` varchar(255) DEFAULT NULL COMMENT '顾客名称',
  `gender` varchar(255) DEFAULT NULL COMMENT '性别',
  `telephone` varchar(255) DEFAULT NULL COMMENT '电话号码',
  `register_time` timestamp NULL DEFAULT NULL COMMENT '注册时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COMMENT='顾客表';

提交事务示例

begin;
INSERT INTO `zszxz`.`customer`(`customer_name`, `gender`, `telephone`, `register_time`) VALUES ('知识追寻者', '男', '9991', NULL);
commit;

此时 数据库中会增加一条数据;

回滚示例

begin;
INSERT INTO `zszxz`.`customer`(`customer_name`, `gender`, `telephone`, `register_time`) VALUES ('知识追寻者', '男', '9991', NULL);
rollback;

此时数据中并没有添加新数据

4.3 mysql 事务的自动提交

实际上Mysql 中每次的事务操作默认都是自动提交(AUTOCOMMIT) , 即每条语句操作都会自动提交;

读者可以使用 如下 语句查看 mysql 的 AUTOCOMMIT 是否 开启

SHOW VARIABLES LIKE 'AUTOCOMMIT'

操作示例如下:

tXlB9A.png

使用如下语句可以对 自动提交进行设置

  • SET AUTOCOMMIT=0 (0 或者OFF 表示禁止自动提交)
  • SET AUTOCOMMIT=1 (1或者ON开启自动提交)

相关文章