likes
comments
collection
share

MySql事务

作者站长头像
站长
· 阅读数 61

1. 基本概念

事务是一组逻辑操作单元，使数据从一种状态变换到另一种状态。

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

dml语句 指的是 delete, insert, update

从1点回滚到11点, 就不能在从11点到12点,但是可以从1点到12点再到11点

保存点(savepoint). 保存点是事务中的点.用于取消部分事务，当结束事务时(commit)，会自动的删除该事务所定义的所有保存点当执行回退事务时，通过指定保存点可以回退到指定的点.

提交事务(commit) 使用commiti语句可以提交事务.当执行了commit语句子后，会确认事务的变化、结 束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其 它会话[其他连接]将可以查看到事务变化后的新数据所有数据就正式生效.

1.1事务的ACID特性

1.1.1原子性(atomicity)

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

1.1.2一致性(consistency)

(可以看看Wikipedia)

定义 : 指的是事务执行前后, 数据从一个合法状态 转化到另一个合法状态

这里的合法状态指的是, 在现实生活中的合理, 符合逻辑的状态, 而不是语法上的

案例

MySql事务

1.1.3隔离性(isolation)

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

后面还有很多

1.1.4永久性(durability)

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。 #日志

总结 : ACID四大特性, 原子性是基础, 隔离性是手段, 一致性是束缚条件, 而耐久性是我们的目的

1.2事务的状态

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

  • 活跃的(active) 事务对应的数据库操作正在执行过程中

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

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

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

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

状态之间的转化

MySql事务

1.3 如何使用事务

使用事务的两种方式, 分别显式事务和隐式事务

1.3.1 显式事务

  • 步骤一 START TRANSACTION 或者 BEGIN, 作用可以开启一个事务

    START TRANSACTION 
或者
BEGIN

    但是 START TRANSACTION 可以在后面添加一些修饰符

    1. READ ONLY
    2. READ WRITE (默认)
    3. WITH CONSISTENT SNAPSHOT 1 或 3 2或 3 可以相互组合

  • 步骤二 可以添加DML语句....

  • 步骤三 可以添加 保存点(SAVEPOINT) 看一张图

  • 步骤四 回滚 (ROLLBACK)

    ROLLBACK ON 某一个保存点
或者
ROLLBACK 直接回滚到事务开始时

  • 步骤五 提交COMMIT 提交之后数据就写入到磁盘之中, 永久保存下来

1.3.2 隐式事务

UPDATE account SET balance - 10 WHERE id = 1;

UPDATE account SET balance + 10 WHERE id = 2;

看上面两条语句是自动commit的, 是两条单独的事务

关键字 AUTOCOMMIT

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

关闭的自动提交的二种方式

# 第一种
SET AUTOCOMMIT = FALSE; # 只针对dml语句生效, 对DDL的操作无效

UPDATE account SET balance - 10 WHERE id = 1;

UPDATE account SET balance + 10 WHERE id = 2;

COMMIT ; # ROLLBACK

# 第二种
START TRANSACTION; # 或者BIGNE开启一个事务
# 这里也会下面语句关闭自动提交, 将下面的两种语句就会变成一个事务
UPDATE account SET balance - 10 WHERE id = 1;

UPDATE account SET balance + 10 WHERE id = 2;

COMMIT;  # ROLLBACK

1.3.3 隐式提交数据

SET AUTOCOMMIT = FALSE; 无法关闭的几种情况

MySql事务 MySql事务 MySql事务 MySql事务

1.3.4 举例使用

INNODB 和 MYISAM的区别 ?

MYISAM不支持事务

# 体会ROLLBACK和COMMIT

# 情况一
CREATE TABLE user3 (name VARCHAR(15) PRIMARY KEY);

SELECT * FROM user3;

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

BEGIN;
INSERT INTO user3 VALUES ('李四'); # 此时不会自动提交

ROLLBACK; # 结果只有一个张三


#情况二
TRUNCATE TABLE user3; # 清除表中的数据 是DDL语句，执行后无法回滚

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

INSERT INTO user3 VALUES ('李四'); 
INSERT INTO user3 VALUES ('李四'); # 这个是失败的状态ROLLBACK之后就变成终止状态
SELECT * FROM user3;
ROLLBACK; # 结果 张三 + 李四



# 体会	INNODB AND MYISAM
CREATE TABLE test1 (id INT) ENGINE = INNODB;
CREATE TABLE test2 (id INT) ENGINE = MYISAM;

#innodb
BEGIN;
INSERT INTO test1 VALUES(1);
ROLLBACK; 

SELECT * FROM test1;

#myisam
BEGIN;
INSERT INTO test2 VALUES(1);
ROLLBACK; 

SELECT * FROM test2;

2.数据并发问题

提前准备创建一张表

CREATE TABLE student (
	studentno INT,
	name VARCHAR(20),
	class VARCHAR(20),
	PRIMARY KEY (studentno)
) ENGINE = INNODB CHARSET = 'UTF8';

添加一个条数据

INSERT INTO student VALUES(1, '小谷', '1班');

这张表长这样

+-----------+--------+-------+
| studentno | name   | class |
+-----------+--------+-------+
|         1 | 小谷   | 1班   |
+-----------+--------+-------+
1 row in set (0.00 sec)

首先, 并发的前提不是串行 #串行 如果是串行的化不可能会出现这种问题. 当串行时不会有多个抢一个资源的情况, 执行完在执行下一个

并发产生的如下问题

脏写 (Dirty Write)

对于两个事务Session A、Session B,如果事务Session A修改了另一个未提交事务Session B修改过的数据，那就意味着发生了脏写，示意图如下：

MySql事务

Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新为'李四'然后Session A中的事务接着又把这条studentno?列为1的记录的name列更新为'张三'。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写。这时Session A中的事务就没有效果了，明明把数据更新了，最后也提交事务了，最后看到的数据什么变化也没有。这里大家对事务的隔离级比较了解的话，会发现默认隔离级别下，上面SessionA中的更新语句会处于等待状态，这里只是跟大家说明一下会出现这样现象。

脏读 (Dirty Read)

对于两个事务Session A、Session B,Session A读取了已经被Session B更新但还没有被提交的字段。之后若Session B回滚，Session A读取的内容就是临时且无效的。示意图如下

MySql事务

Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为'张三'，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。

不可重复读(Non-Repeatable Read)

对于两个事务Session A、Session B,Session A读取了一个字段，然后Session B更新了该字段。之后Session A 再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。

MySql事务

我们在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了studentno 列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。

幻读 (Phantom)

对于两个事务Session A、Session B,Session A从一个表中读取了一个字段，然后Session B在该表中插入了一些新的行。之后，如果SessionA再次读取同一个表，就会多出几行。那就意味着发生了幻读。

MySql事务

Session A中的事务先根据条件studentno > 0 这个条件查询表student,得到了name列值为'张三'的记录；之后Session B中提交了一个隐式事务，该事务向表student中插入了一条新记录；之后Session A中的事务再根据相同的条件studentno > 0查询表student,.得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。我们把新插入的那些记录称之为幻影记录。

注意点1

如果SessionB中删除了某一些数据, 那么SessionA中满足studentno > 0 变少了, 这个现象不算是幻读 幻读强调的是插入数据

注意点2

对于先前可以读取到, 后来读取不到的情况可以算是不可重复读

4种Sql隔离级别

MySql事务

由于脏写问题太过严重, 所以每种隔离级别解决了这个个问题. 所以就没有写出

对于数据并发问题的危害程度排序 脏写 > 脏读 > 不可重复读 > 幻读

为什么不写死一种隔离级别 ?

等级太高会影响效率 (和Java中的锁类似)

MySql事务

MySql的隔离级别

查看隔离级别
# MySql 5.7.20之前的版本

mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.00 sec)

# 之后的版本
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| transaction_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.00 sec)


#通用方法
SELECT @@transaction_isolation
设置隔离级别
MySql事务

设置时关键字 global 和 session的区别

  • 使用GLOBAL关键字 (在全局范围影响)

    • 对当前的会话无效
    • 只对执行完该语句之后产生的会话起作用

  • 使用SESSION关键字 (在会话范围影响)

    • 对当前会话的后续事务有效
    • 如果在事务之间执行, 对后续事务有效
    • 如果在已经开启地事务之间执行, 不会影响当下正在执行的事务
转载自:https://juejin.cn/post/7240765897768534075
评论
请登录