mysql事务绑定_21、MySQL事务

一、(了解)定义

全称(Transaction Control Language)翻译成中文 事务控制语言,事务是访问并可能更新数据库各种数据项的一个程序执行单元,

是并发控制的单元，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，sql 能将逻辑相关的一组操作绑定在一起，以便服务器 保持数据的完整性。

二、 (了解)为什么要事务

设想网上购物的一次交易，其付款过程至少包括以下几步数据库操作：

更新客户所购商品的库存信息

保存客户付款信息--可能包括与银行系统的交互

生成订单并且保存到数据库中

更新用户相关信息，例如购物数量等等

正常的情况下，这些操作将顺利进行，最终交易成功，与交易相关的所有数据库信息也成功地更新。但是，如果在这一系列过程中任何一个环节出了差错，例如在更新商品库存信息时发生异常、该顾客银行帐户存款不足等，都将导致交易失败。一旦交易失败，数据库中所有信息都必须保持交易前的状态不变，比如最后一步更新用户信息时失败而导致交易失败，那么必须保证这笔失败的交易不影响数据库的状态--库存信息没有被更新、用户也没有付款，订单也没有生成。否则，数据库的信息将会一片混乱而不可预测。

数据库事务正是用来保证这种情况下交易的平稳性和可预测性的技术

默认情况下会自动提交，也就是说每个SQL语句都是在其完成时提交到数据库。

事务只针对DDL操作

三、(熟练掌握)事务的特性(ACID)

1、案例

A账户向B账号汇钱的例子来说明如何通过数据库事务保证数据的准确性和完整性

1、从A账号中把余额读出来(500)

2、对A账号做减法操作(500-100)

3、把结果写回A账号中(400)

4、从B账号中把余额读出来(500

5、对B账号做加法操作(500+100)

6、把结果写回B账号中(600)

2、原子性(Atomicity)

概念

事务是数据库的逻辑工作单位，而且是必须是原子工作单位，对于其数据修改，要么全部执行，要么全部不执行。

说明

保证1-6所有过程要么都执行，要么都不执行。一旦在执行某一步骤的过程中发生问题，就需要执行回滚操作。 假如执行到第五步的时候，B账户突然不可用(比如被注销)，那么之前的所有操作都应该回滚到执行事务之前的状态

3、一致性(Consistency)

概念

指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。也就是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

注: **

业务逻辑上的一致性 由开发人员进行保证。

数据库层面 在一个事务执行之前和之后，数据会符合你设置的约束(唯一约束，外键约束,check约束**等)和触发器设置,并且同一个事务内部的一组操作必须全部执行成功(原子操作)

但是，原子性并不能完全保证一致性。在多个事务并行进行的情况下，即使保证了每一个事务的原子性，仍然可能导致数据不一致的结果。为了保证并发情况下的一致性，引入了隔离性

说明

在转账之前，A和B的账户中共有500+500=1000元钱。在转账之后，A和B的账户中共有400+600=1000元。也就是说，数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态。同时一致性还能保证账户余额不会变成负数等

4、隔离性(Isolation)

概念

也称为独立性，是指并行事务的修改必须与其他并行事务的修改相互独立。一个事务处理数据，要么是其他事务执行之前的状态，要么是其他事务执行之后的状态，但不能处理其他正在处理的数据。

企业级的数据库每一秒钟都可能应付成千上万的并发访问，因而带来了并发控制的问题。

说明

在A向B转账的整个过程中，只要事务还没有提交(commit)，查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。

如果在A给B转账的同时，有另外一个事务执行了C给B转账的操作，那么当两个事务都结束的时候，B账户里面的钱应该是A转给B的钱加上C转给B的钱再加上自己原有的钱

5、持久性(Durability)

概念

一个事务一旦提交，事物的操作便永久性的保存在DB中。即使此时再执行回滚操作也不能撤消所做的更改

说明

一旦转账成功(事务提交)，两个账户的里面的钱就会真的发生变化(会把数据写入数据库做持久化保存)

四、(熟练掌握)基本操作

1、事务常用的语句

语句

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。

2、MYSQL 事务处理主要有两种方法：

用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT事务提交

直接用 SET 来改变 MySQL 的自动提交模式

SET AUTOCOMMIT=0 | off 禁止自动提交

**SET AUTOCOMMIT=1 | on ** 开启自动提交

3 、案例

前期准备工作

-- 创建银行账户表

create table account(

aid int primary key auto_increment comment '主键',

card_no varchar(16) not null unique comment '银行卡号',

name varchar(20) not null comment '姓名',

money decimal(10,2) default 0.0 comment '金额'

)

-- 插入数据

insert into account (card_no,name,money) values

('1', '小明', 1000),

('2', '娇娇', 1000);

第一步 开始事务

-- 告诉系统以下所有操作，不要直接写入数据库，先存到事务日志。

BEGIN

第二步 减少账户的余额

update account set money = money - 1000 where cardno =1

第三步 增加账户的余额

update account set money = money + 1000 where cardno = 2;

第四步 提交事务

commit;

第五步 或者回滚

rollback

五、(掌握)事务隔离 - 并发控制

数据库事务处理相关命令

操作命令

说明

SHOW CREATE TABLE 表名；

查看存储引擎

SET AUTOCOMMIT=0或1;或者 SET AUTOCOMMIT=off 或 on

设置是否自动提交

SELECT @@AUTOCOMMIT; 或者 show variables like '%commit%';

查询自动提交功能状态

SELECT @@tx_isolation;

查看事务隔离级别

SET tx_isolation='READ-UNCOMMITTED';

设置事务的隔离级别

1、并发控制

数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性

2、不考虑事务的隔离性，会出现什么问题？

脏读：一个事务读取到另一个事务的未提交数据

不可重复读：两次读取的数据不一致(强调update 针对单行)

虚读(幻读)：两次读取的数据不一致(强调insert或delete,范围查询)

丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖(事务A和B并发执行，A事务执行更新后，提交；B事务在A事务更新后，B事务结束前也做了对该行数据的更新操作，然后回滚，则两次更新操作都丢失了)

3、四种隔离级别

Read-uncommitted：最低级别，以上情况均无法保证。(读未提交)

Read-committed：可避免脏读情况发生(读已提交)

Repeatable-read：可避免脏读、不可重复读情况的发生。(可重复读)不可以避免虚读

Serializable：可避免脏读、不可重复读、虚读情况的发生。(串行，不仅有read、write锁还有range lock范围锁(没有where锁全表，有where锁where范围)；对一张表的所有增删改操作必须顺序执行，性能最差)

| 隔离级别 | 脏读 | 不可重复读 | 幻读 |

| ---------------- | ---- | ---------- | ---- |

| Read uncommitted | √ | √ | √ |

| Read committed | × | √ | √ |

| Repeatable read | × | × | √ |

| Serializable | × | × | × |

六、(掌握)隔离级别详解

1、Read uncommitted

作用

所有事务都可以看到其他未提交事务的执行结果

举个栗子

又到月底了,小明的老婆要准备给小明发生活费了,小明的老婆给小明打了500块,但该事务并没有提交,而此时小明正好在查余额,发现是550块,高兴的差点蹦了起来.天有不测风云,突然小明的老婆发现多打了50块,于是回滚事务,修改金额,然后将事务提交,最后小明空欢喜异常。

示例图

image

示例代码

-- ****打开两个窗口 事务A窗口****

-- 1.查看事务隔离级别

SELECT @@TX_ISOLATION;

+------------------+

| @@tx_isolation |

+------------------+

| READ-UNCOMMITTED |

+------------------+

-- 2.设置事务的隔离级别为读取未提交

SET tx_isolation='READ-UNCOMMITTED';

-- 3.查看自动提交状态

SELECT @@AUTOCOMMIT;

-- 4.如果自动提交开启,关闭自动提交

SET AUTOCOMMIT = 0;

-- ****在事务A中执行更新语句，且不提交****

-- 5. 开启事务

START TRANSACTION;

-- 6. 将账户小明的账号的钱减100

UPDATE account SET money = money - 100 WHERE aid = 1;

-- 不要提交 切换到事务B的窗口

-- 7. 事务回滚

ROLLBACK

-- 事务B窗口

-- 1. 查看事务的隔离级别

SELECT @@TX_ISOLATION;

-- 2.设置事务的隔离级别

SET TX_ISOLATION = 'READ-UNCOMMITTED';

-- 3.查询小明的账号

SELECT * from account WHERE aid = 1

-- ****显示的信息****

+-----+---------+--------+--------+

| aid | card_no | name | money |

+-----+---------+--------+--------+

| 1 | 1 | 小明 | 700.00 |

+-----+---------+--------+--------+

-- 4. 将事务隔离级别设置成读取已提交或者其他

SET TX_ISOLATION = 'READ-COMMITTED';

-- 5. 在次查询发现账号余额是800

SELECT * from account WHERE aid = 1;

-- ****显示的信息****

+-----+---------+--------+--------+

| aid | card_no | name | money |

+-----+---------+--------+--------+

| 1 | 1 | 小明 | 800.00 |

+-----+---------+--------+--------+

-- 切回事务B 使用回滚 rollback

2、Read committed

作用

一个事务只能看见已经提交事务所做的改变

举个栗子

某个夜黑风高的夜晚，小明丰富的夜生活开始了,小明拿着工资卡去消费,pos机读取卡的信息的时候有500，

而此时小红也正好在网上转账，把小明工资卡的500元转到另一账户，并小明之前提交了事务，当小明扣款时，

系统检查到小明的工资卡已经没有钱，扣款失败，小明十分纳闷，明明卡里有钱,为什么会说余额不足,

出现上述情况，即我们所说的不可重复读，两个并发的事务，“事务1：小明消费”、“事务2：小红网上转账”，事务1事先读取了数据，

事务2紧接了更新了数据，并提交了事务，而事务1再次读取该数据时，数据已经发生了改变,

当隔离级别设置为Read committed时，避免了脏读，但是可能会造成不可重复读

示意图

image

备注

Sql Server ,Oracle的默认级别

示例代码

第一步窗口一(事务1)

-- 事务1

-- 1. 设置隔离级别为读取已提交

SET TX_ISOLATION = 'READ-COMMITTED';

-- 2. 查看当前连接的事务级别

SELECT @@TX_ISOLATION;

+----------------+

| @@TX_ISOLATION |

+----------------+

| READ-COMMITTED |

-- 3. 关闭自动提交

SET AUTOCOMMIT = 0;

-- 4. 关闭自动提交

SELECT @@AUTOCOMMIT;

-- 5.开启事务

START TRANSACTION;

-- 6.账号余额-100

UPDATE account

SET account.money= money - 100

WHERE aid = 1;

第二步窗口二(事务2)

-- 事务2

-- 1. 设置隔离级别为读取已提交

SET TX_ISOLATION = 'READ-COMMITTED';

-- 2. 查看当前连接的事务级别

SELECT @@TX_ISOLATION;

-- 3. 关闭自动提交

SET AUTOCOMMIT = 0;

-- 4. 关闭自动提交

SELECT @@AUTOCOMMIT;

-- 5. 事务1 ******还没有提交事务****

-- 查询账户信息

BEGIN;

SELECT * FROM account WHERE aid = 1

+-----+---------+--------+---------+

| aid | card_no | name | money |

+-----+---------+--------+---------+

| 1 | 1 | 小明 | 1000.00 |

+-----+---------+--------+---------+

第三步窗口一(事务1)

-- 7.提交事务

COMMIT

第四步窗口二(事务2)

-- 5. 此时事务已经提交 两次查询的结果不一致

SELECT * FROM account WHERE aid = 1

COMMIT;

+-----+---------+--------+--------+

| aid | card_no | name | money |

+-----+---------+--------+--------+

| 1 | 1 | 小明 | 900.00 |

+-----+---------+--------+--------+

-- 相同的select语句，结果却不一样

3、Repeatable read

说明

当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时

举个栗子

小红最近发现小明总是很晚回家并且经常不接电话,于是小红开始查小明当月信用卡的总消费金额，

消费金额为50,而小明此时正好在收银台买单，消费1000元，即新增了一条1000元的消费记录,并提交了事务，

随后小红将小明当月信用卡消费的明细打印了出来，却发现消费总额为1050元，小红很诧异，以为出现了幻觉

示例图

image

备注

MySQL的默认隔离级别

区别

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改(但mysql由于MVCC机制并不会有)，

幻读的重点在于新增或者删除比如多次范围读取发现记录增多或减少了。

示例代码

第一步窗口一(事务1)

-- 事务1

-- 1. 查看当前连接的事务级别

SELECT @@TX_ISOLATION;

+----------------+

| @@TX_ISOLATION |

+----------------+

| READ-COMMITTED |

-- 2. 关闭自动提交

SET AUTOCOMMIT = 0;

-- 3. 关闭自动提交

SELECT @@AUTOCOMMIT;

-- 4.开启事务

START TRANSACTION;

-- 5.账号余额-100

UPDATE account SET account.money= money - 100 WHERE aid = 1;

第二步窗口二(事务2)

-- 事务2

-- 1. 查看当前连接的事务级别

SELECT @@TX_ISOLATION;

-- 2. 关闭自动提交

SET AUTOCOMMIT = 0;

-- 3. 关闭自动提交

SELECT @@AUTOCOMMIT;

-- 查询账户信息

BEGIN;

SELECT * FROM account WHERE aid = 1

+-----+---------+--------+---------+

| aid | card_no | name | money |

+-----+---------+--------+---------+

| 1 | 1 | 小明 | 1000.00 |

+-----+---------+--------+---------+

第三步窗口一(事务1)

-- 7.提交事务

COMMIT

第四步窗口二(事务2)

-- 5. 此时事务已经提交 两次查询的结果不一致

SELECT * FROM account WHERE aid = 1

COMMIT;

+-----+---------+--------+--------+

| aid | card_no | name | money |

+-----+---------+--------+--------+

| 1 | 1 | 小明 | 1000.00 |

+-----+---------+--------+--------+

-- 相同的select语句，结果一样

但幻读还是问题还是有(演示幻读)

第一步窗口一(事务1)

-- 事务1

-- 1 开始事务

BEGIN;

-- 2. 查询

SELECT *

FROM account WHERE aid > 1;

-- 查询一条记录

image

第二步窗口二(事务2)

-- 事务2

BEGIN;

INSERT INTO account(CARD_NO, NAME, MONEY) VALUE ('99999', '老王', 0.00);

INSERT INTO account(CARD_NO, NAME, MONEY) VALUE ('5555', '樱樱是谁', 10.00);

COMMIT

-- 并且提交

第三步窗口一(事务1)

-- 3.批量更新数据

-- MVCC只对读有效，对写操作无效，由于update是写操作，

UPDATE account

SET money = 0.00

WHERE aid > 1;

-- 4. 再次查询出现幻读

SELECT *

FROM account

WHERE aid > 1;

COMMIT;

说明

从原理上看，可重复读是靠MVCC(多版本并发控制)保证的，该模式下，保证事务只能读取到当前事务开启之前已经提交的事务进行的修改以及当前事务本身对数据的修改

4、Serializable(串行)(了解)

说明

最高级别：防止上述3种情况，事务串行执行，慎用

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决不读脏，可重复读，不可幻读。

简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争,并发性能最差,在分布式事务中可能会被用到