MySQL事务基础

目录

一.事务概述

1.1存储引擎支持情况

1.2基本概念

1.3事务的ACID特性

1.3.1原子性(atomicity)

1.3.2一致性(consistency)

1.3.3隔离型(isolation)

1.3.4持久性(durability)

1.3.5总结

1.4事务的状态

二.如何使用事务

2.1显式事务

2.2隐式事务

2.3 隐式提交数据的情况

2.4 使用举例1:提交与回滚

2.5 使用举例2:测试不支持事务的engine

2.6 使用举例3:SAVEPOINT


一.事务概述

事务是数据库区别于文件系统的重要特性之一,当有了事务就会让数据库始终保持一致性,同时还能通过事务的机制恢复到某个时间点,这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失

1.1存储引擎支持情况

SHOW ENGINES 命令来查看当前 MySQL 支持的存储引擎都有哪些,以及这些存储引擎是否支持事务。

SHOW ENGINES;
/*
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
*/

能看出在MySQL中,只有InnoDB是支持事务的

1.2基本概念

事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
事务处理的原则:保证所有事务都作为 一个工作单元 来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交( commit ),那么这些修改就 永久 地保存下来;要么数据库管理系统将 放弃 所作的所有 修改 ,整个事务回滚( rollback )到最初状态。

1.3事务的ACID特性

1.3.1原子性(atomicity)

        原子性是指事务是一个不可分割的工作单位,要么全部提交,要么全部失败回滚。即要么转账成功,要么转账失败,是不存在中间的状态。如果无法保证原子性会怎么样?就会出现数据不一致的情形,A账户减去100元,而B账户增加100元操作失败,系统将无故丢失100元。

1.3.2一致性(consistency)

根据定义,一致性是指事务执行前后,数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的,跟具体的业务有关。

补充:

某本书上的一致性概念–> 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发地完成预定的工作。

那什么是合法的数据状态呢?满足预定的约束的状态就叫做合法的状态。通俗一点,这状态是由你自己来定义的(比如满足现实世界中的约束)。满足这个状态,数据就是一致的,不满足这个状态,数据就是不一致的!如果事务中的某个操作失败了,系统就会自动撤销当前正在执行的事务,返回到事务操作之前的状态。

举例1:A账户有200元,转账300元出去,此时A账户余额为-100元。你自然就发现了此时数据是不一致的,为什么呢?因为你定义了一个状态,余额这列必须>=0。
举例2:A账户200元。转账50元给B账户,A账户的钱扣了,但是B账户因为各种意外,余额并没有增加。你也知道此时数据是不一致的,为什么呢?因为你定义了一个状态,要求A+E的总余额必须不变。
举例3:在数据表中将姓名字段设置为唯一性约束,这时当事务进行提交或者事务发生回滚的时候,如果数据表中的姓名不唯一,就破坏了事务的一致性要求。

1.3.3隔离型(isolation)

联系操作系统的临界区的概念

事务的隔离性是指一个事务的执行不能被其他事务干扰 ,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。

如果无法保证隔离性会怎么样?假设A账户有200元,B账户0元。A账户往B账户转账两次,每次金额为50元,分别在两个事务中执行。如果无法保证隔离性,会出现下面的情形:

UPDATE accounts SET money = money - 50 WHERE NAME = 'AA';

UPDATE accounts SET money = money + 50 WHERE NAME = 'BB';

1.3.4持久性(durability)

持久性是指一个事务一旦被提交,它对数据库中数据的改变就是 永久性的 ,接下来的其他操作和数据库故障不应该对其有任何影响。
持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志 。当我们通过事务对数据进行修改的时候,首先会将数据库的变化信息记录到重做日志中,然后再对数据库中对应的行进行修改。这样做的好处是,即使数据库系统崩溃,数据库重启后也能找到没有更新到数据库系统中的重做日志,重新执行,从而使事务具有持久性。

1.3.5总结

ACID是事务的四大特性,在这四个特性中,原子性是基础,隔离性是手段,一致性是约束条件,而持久性是目的。

数据库事务,其实就是数据库设计者为了方便起见,把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称为一个事务

1.4事务的状态

我们现在知道 事务 是一个抽象的概念,它其实对应着一个或多个数据库操作,MySQL根据这些操作所执行的不同阶段把 事务 大致划分成几个状态:
活动的(active)

事务对应的数据库操作正在执行过程中时,就说该事务处在 活动的 状态。

部分提交的(partially committed)

当事务中的最后一个操作执行完成,但由于操作都在内存中执行,所造成的影响并 没有刷新到磁盘时,我们就说该事务处在 部分提交的 状态。

失败的(failed)

当事务处在 活动的 或者 部分提交的 状态时,可能遇到了某些错误(数据库自身的错误、操作系统错误或者直接断电等)而无法继续执行,或者人为的停止当前事务的执行,就说该事务处在失败的状态

中止的(aborted)

如果事务执行了一部分而变为失败的 状态,那么就需要把已经修改的事务中的操作还原到事务执行前的状态。换句话说,就是要撤销失败事务对当前数据库造成的影响。把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时,也就是数据库恢复到了执行事务之前的状态,就说该事务处在了 中止的 状态。

提交的(committed)
当一个处在 部分提交的 状态的事务将修改过的数据都 同步到磁盘 上之后,就可以说该事务处在了 提交的 状态。

一个基本的状态转换图如下所示:

图中可见,只有当事务处于提交的或者中止的状态时,一个事务的生命周期才算是结束了。对于已经提交的事务

来说,该事务对数据库所做的修改将永久生效,对于处于中止状态的事务,该事务对数据库所做的所有修

被回滚到没执行该事务之前的状态。

二.如何使用事务

使用事务有两种方式,分别为显式事务隐式事务

2.1显式事务

#09-事务的基础知识

#1.事务的完成过程
#步骤1:开启事务:
#步骤2:一系列的DML操作
#...
#步骤3:结束的状态:提交的状态(COMMIT)、中止的状态(ROLLBACK)

步骤1: START TRANSACTION 或者 BEGIN ,作用是显式开启一个事务。

BEGIN;
#或者
START TRANSACTION;

START TRANSACTION 语句相较于 BEGIN 特别之处在于,后边能跟随几个 修饰符 :

① READ ONLY :标识当前事务是一个 只读事务 ,也就是属于该事务的数据库操作只能读取数据,而不

能修改数据。

补充:只读事务中只是不允许修改那些其他事务也能访问到的表中的数据,对于临时表来说(使用CREATE TMEPORARY TABLE创建的表),由于它们只能在当前会话中可见,所以只读事务其实也是可以对临时表进行增、删、改操作的

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

③ WITH CONSISTENT SNAPSHOT :启动一致性读。

比如:

START TRANSACTION READ ONLY;#开启一个只读事务
START TRANSACTION READ ONLY,WITH CONSISTENT SNAPSHOT;#开启只读事多和一致性读
START TRANSACTION READ WRITE,WITH CONSISTENT SNAPSHOT;#开启读写事务和一致性读

注意:

READ ONLY和READ WRITE是用来设置所谓的事务访问模式的,就是以只读还是读写的方式来访问数据库中的数据,一个事务的访问模式不能同时既设置为只读的又设置为读写的,所以不能同时把READ ONLY和READ WRITE放到START TRANSACTION语句后边

如果不显式指定事务的访问模式,那么该事务的访问模式就是读写模式。

步骤2:一系列事务中的操作(主要是DML,不含DDL)

步骤3:提交事务 或 中止事务(即回滚事务)

# 提交事务。当提交事务后,对数据库的修改是永久性的
COMMIT;
# 回滚事务。即撤销正在进行的所有没有提交的修改
ROLLBACK;
# 将事务回滚到某个保存点
ROLLBACK TO [SAVEPOINT]

其中关于SAVEPOINT相关操作有:

#在事务中创建保存点,方便后续针对保存点进行回滚。一个事务中可么存在多个保存点
SAVEPOINT 保存点名称;
#删除某个保存点
RELEASE SAVEPOINT保存点名称;
#2.显式事务

#2.1 如何开启?使用关键字:start transaction 或 begin

# start transaction 后面可以跟read only/read write(默认) / with consistent snapshot

#2.2 保存点(SAVEPOINT)

2.2隐式事务

MySQL中有一个系统变量 autocommit

SHOW VARIABLES LIKE 'autocommit';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
*/

默认情况下,如果不显式的使用START TRANSACTION或者BEGIN语句开启一个事务,那么每一条语句都算是一个独立的事务,这种特性称之为事务的自动提交。也就是说,不以START TRANSACTION或者BEGIN语句显式的开启一个事务,那么下边这两条语句就相当于放到两个独立的事务中去执行

#3.隐式事务

#3.1 关键字:autocommit 
#set autocommit = false;

SHOW VARIABLES LIKE 'autocommit';#默认是ON

UPDATE account SET balance = balance - 10 WHERE id = 1; #此时这条DML操作是一个独立的事务

UPDATE account SET balance = balance + 10 WHERE id = 2; #此时这条DML操作是一个独立的事务

当然,如果想关闭这种 自动提交 的功能,可以使用下边两种方法之一:

  • 显式的的使用 START TRANSACTION 或者 BEGIN语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
  • 把系统变量 autocommit 的值设置为 OFF ,就像这样:
SET autocommit = OFF;
#或
SET autocommit = 0;

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

补充: Oracle 默认不自动提交,需要手写COMMIT命令,而MySQL 默认自动提交。

#3.2 如何关闭自动提交?
#方式1:
SET autocommit = FALSE; #针对于DML操作是有效的,对DDL操作是无效的。

UPDATE account SET balance = balance - 10 WHERE id = 1;

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

#方式2:在autocommit为true的情况下,使用start transaction 或begin开启事务,那么DML操作就不会自动提交数据


START TRANSACTION;

UPDATE account SET balance = balance - 10 WHERE id = 1;

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

2.3 隐式提交数据的情况

数据定义语言(Data definition language,缩写为:DDL)
数据库对象,指的就是数据库视图存储过程等结构。当使用CREATEALTERDROP等语句去修改数据库对象时,就会隐式的提交前边语句所属于的事务。即:

BEGIN;

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

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

隐式使用或修改mysql数据库中的表
当使用ALTER USERCREATE USERDROP USERGRANTRENAME USERREVOKE.、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务
事务控制或关于锁定的语句
当在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时,会隐式的提交上—个事务。即:

BEGIN;

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

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

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

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

  • 加载数据的语句
    使用LOAD DATA语句来批量往数据库中导入数据时,也会隐式的提交前边语句所属的事务。
    关于MySQL复制的一些语句
    使用START SLAVESTOP SLAVERESET SLAVECHANGE MASTER TO等语句时会隐式的提交前边语句所属的事务。
    其它的一些语句
    使用ANALYZE TABLECACHE INDEXCHECK TABLEFLUSHLOAD INDEX INTO CACHEOPTIMIZE TABLEREPAIR TABLERESET 等语句也会隐式的提交前边语句所属的事务。

2.4 使用举例1:提交与回滚

我们看下在 MySQL 的默认状态下,下面这个事务最后的处理结果是什么。

#4.案例分析
#SET autocommit = TRUE; 
#举例1: commit 和 rollback

USE atguigudb2;
#情况1:
CREATE TABLE user3(NAME VARCHAR(15) PRIMARY KEY);

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;

BEGIN; #开启一个新的事务
INSERT INTO user3 VALUES('李四'); #此时不会自动提交数据
INSERT INTO user3 VALUES('李四'); #受主键的影响,不能添加成功
ROLLBACK;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

#情况2:
TRUNCATE TABLE user3;  #DDL操作会自动提交数据,不受autocommit变量的影响。

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;

INSERT INTO user3 VALUES('李四');# 默认情况下(即autocommit为true),DML操作也会自动提交数据。
INSERT INTO user3 VALUES('李四'); #事务的失败的状态

ROLLBACK;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
| 李四   |
+--------+
*/

#情况3:
TRUNCATE TABLE user3;

SELECT * FROM user3;

SELECT @@completion_type;

SET @@completion_type = 1;

BEGIN;
INSERT INTO user3 VALUES('张三'); 
COMMIT;


SELECT * FROM user3;

INSERT INTO user3 VALUES('李四');
INSERT INTO user3 VALUES('李四'); 

ROLLBACK;


SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

能看到相同的SQL代码,只是在事务开始之前设置了SET @@completion_type = 1;结果就和第一次处理的一样,只有一个“张三”。这是为什么呢?

这里讲解下 MySQL中completion_type参数的作用,实际上这个参数有3种可能:

1.completion=0,这是默认情况。当执行COMNIT的时候会提交事务,在执行下一个事务时,还需要使START TRANSACTION 或者BEGIN来开启。

2.completion=1,这种情况下,当提交事务后,相当于执行了COMMIT AND CHAIN,也就是开启一个链式事务,即提交事务之后会开启一个相同隔离级别的事务。

3.completion=2,这种情况下CONMMIT=COMMIT AND RELEASE,也就是提交后,会自动与服务器断开连接

当我们设置 autocommit=0 时,不论是否采用 START TRANSACTION 或者 BEGIN 的方式来开启事务,都需要用 COMMIT 进行提交,让事务生效,使用 ROLLBACK 对事务进行回滚。

当我们设置 autocommit=1 时,每条 SQL 语句都会自动进行提交。 不过这时,如果你采用 START TRANSACTION 或者 BEGIN 的方式来显式地开启事务,那么这个事务只有在 COMMIT 时才会生效,在 ROLLBACK 时才会回滚。

2.5 使用举例2:测试不支持事务的engine

USE atguigudb3;
#举例2:体会INNODB 和 MyISAM

CREATE TABLE test1(i INT) ENGINE = INNODB;

CREATE TABLE test2(i INT) ENGINE = MYISAM;

#针对于innodb表
BEGIN
INSERT INTO test1 VALUES (1);
ROLLBACK;

SELECT * FROM test1;
#Empty set (0.00 sec)


#针对于myisam表:不支持事务
BEGIN
INSERT INTO test2 VALUES (1);
ROLLBACK;

SELECT * FROM test2;
#1

2.6 使用举例3:SAVEPOINT

#举例3:体会savepoint

CREATE TABLE user3(NAME VARCHAR(15),balance DECIMAL(10,2));

BEGIN
INSERT INTO user3(NAME,balance) VALUES('张三',1000);
COMMIT;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

BEGIN;
UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

SAVEPOINT s1;#设置保存点

UPDATE user3 SET balance = balance + 1 WHERE NAME = '张三';

ROLLBACK TO s1; #回滚到保存点


SELECT * FROM user3;
#801->800

ROLLBACK; #回滚操作

SELECT * FROM user3;
#1000

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值