MYSQL（基本命令6）

1.数据库事务

1.什么是事务

    事务是一个整体 , 由一条或者多条 SQL 语句组成 , 这些 SQL 语句要么都执行成功 , 要么都执行失败 , 只要有一条 SQL 出现异常 , 整个操作就会回滚 , 整个业务执行失败

比如 : 银行的转账业务 , 张三给李四转账 500 元 , 至少要操作两次数据库 , 张三 -500, 李四 + 500, 这中 间任何一步出现问题 , 整个操作就必须全部回滚 , 这样才能保证用户和银行都没有损失

2.模拟转账操作

1.创建账户表

-- 创建账户表

CREATE TABLE account(

-- 主键

id INT PRIMARY KEY AUTO_INCREMENT ,

-- 姓名

NAME VARCHAR ( 10 ),

-- 余额

money DOUBLE

);

-- 添加两个用户

INSERT INTO account (NAME, money) VALUES ( 'tom' , 1000 ), ( 'jack' , 1000 );

2. 模拟 tom 给 jack 转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：

-- tom 账户 -500 元

UPDATE account SET money = money - 500 WHERE NAME = 'tom' ;

-- jack 账户 + 500 元

UPDATE account SET money = money + 500 WHERE NAME = 'jack' ;

2.MySQL事务操作

---

• MYSQL 中可以有两种方式进行事务的操作：

• 手动提交事务
• 自动提交事务

1.手动提交事务

1.1 语法格式

功能	语句
开启事务	start transaction; 或者 BEGIN;
提交事务	commit;
回滚事务	rollback;

START TRANSACTION

这个语句显式地标记一个事务的起始点。

COMMIT

表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写

到磁盘上的物理数据库中，事务正常结束。

ROLLBACK

表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中

对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

1.2 手动提交事务流程

• 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
• 执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

 

1.3 成功案例 演示

1.3.1 模拟张三给李四转 500 元钱

1.3.2 命令行登录数据库

1.3.3 使用db2数据库

USE db2;

1.3.4 执行以下 SQL

1.开启事务

start transaction ;

 

2.tom账户 -500

update account set money = money - 500 where name = 'tom'

3.jack账户 +500

update account set money = money + 500 where name = 'jack' ;

 

4. 此时我们使用 sqlYog查看表,发现数据并没有改变

5.在控制台执行 commit 提交事务

commit ;

 6.再次使用sqlYog查看, 发现数据在事务提交之后,发生改变

1.3.5 事务回滚演示

• 如果事务中，有某条sql语句执行时报错了，我们没有手动的commit，那整个事务会自动回滚

1.命令行 开启事务

start transaction ;

2. 插入两条数据

INSERT INTO account VALUES ( NULL , ' 张百万 ' , 3000 );

INSERT INTO account VALUES ( NULL , ' 有财 ' , 3500 );

3.不去提交事务 直接关闭窗口 , 发生回滚操作 , 数据没有改变

 

1.3.6 自动提交事务

• MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句
• 执行完毕 自动提交事务，MySQL 默认开始自动提交事务
• MySQL默认是自动提交事务

1.将tom账户金额 +500元

 2.使用 SQLYog 查看数据库：发现数据已经改变

 3.取消自动提交

• MySQL默认是自动提交事务,设置为手动提交.  登录mysql，查看autocommit状态。

SHOW VARIABLES LIKE 'autocommit'

 

on ：自动提交

off : 手动提交

 4. 把 autocommit 改成 off;

SET @@autocommit =off：

5.再次修改,需要提交之后才生效

• 将jack 账户金额 -500元

-- 选择数据库

use db2;

-- 修改数据

update account set money = money - 500 where name = 'jack' ;

-- 手动提交

commit ;

 

3.事务的四大特性 ACID

特 性	含义
原 子 性	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都 失败。
一 致 性	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前 2 个人的 总金额 是 2000 ，转账后 2 个人总金额也是 2000.
隔 离 性	事务与事务之间不应该相互影响，执行时保持隔离的状态 .
持 久 性	一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的 .

4.MySQL 事务隔离级别(了解)

1 数据并发访问

    一个数据库可能拥有多个访问客户端 , 这些客户端都可以并发方式访问数据库 . 数据库的相同数据可能被多个事务同时访问 , 如果不采取隔离措施 , 就会导致各种问题 , 破坏数据的完整性

.2 并发访问会产生的问题

   事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个 数据。可能引发并发访问的问题

并发访问 的问题	说明
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复 读	一个事务中两次读取的数据内容不一致 , 要求的是在一个事务中多次读取时数据是一 致的 . 这是进行 update 操作时引发的问题
幻读	一个事务中 , 某一次的 select 操作得到的结果所表征的数据状态 , 无法支撑后续的业务 操作 . 查询得到的数据状态不准确 , 导致幻读2.

3.四种隔离级别

通过设置隔离级别 , 可以防止上面的三种并发问题 .

MySQL 数据库有四种隔离级别 上面的级别最低，下面的级别最高

• ✔ 会出现问题
• ✘ 不会出现问题

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库的默认隔离级别
1	读未提交	read uncommitted	✔	✔	✔
2	读已提交	read committed	✘	✔	✔	Oracle 和 SQLServer
3	可重复读	repeatable read	✘	✘	✔	MySql
4	串行化	serializable	✘	✘	✘

4.隔离级别相关命令

1.select @@tx_isolation;

2.设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

set global transaction isolation level 级别名称 ; read uncommitted 读未提交 read committed 读已提交 repeatable read 可重复读 serializable 串行化

1.1修改隔离级别为 读未提交

set global transaction isolation level read uncommitted ;

5.隔离性问题演示

1.脏读演示

脏读 : 一个事务读取到了另一个事务中尚未提交的数据

1.1 打开窗口登录 MySQL，设置全局的隔离级别为最低

  1.登录是 MySQL

2.使用db2 数据库

use db2;

3. 设置隔离级别为最低 读未提交

set global transaction isolation level read uncommitted ;

 

4.关闭窗口,开一个新的窗口A ,再次查询隔离级别

 5.查询隔离级别

select @@tx_isolation ;

7.再开一个新窗口

 7.1 选择数据库

use db2;

7.2 开启事务

start transaction ;

7.3 查询        

select * from account;

 7.4 A窗口执行

1 .选择数据库

use db2;

2. 开启事务

start transaction ;

3.执行修改操作

-- tom 账户 -500 元

UPDATE account SET money = money - 500 WHERE NAME = 'tom' ;

-- jack 账户 + 500 元

UPDATE account SET money = money + 500 WHERE NAME = 'jack' ;

  4.. B 窗口查询数据  

1.查询账户信息

select * from account;

2.A窗口转账异常,进行回滚

rollback ;

3.B 窗口再次查询 账户

select * from account;

8.解决脏读问题

• 脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电 话给李四说钱 已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
• 解决方案
• 将全局的隔离级别进行提升为:  read committed

8.1. 在 A 窗口设置全局的隔离级别为 read committed

set global transaction isolation level read committed ;

8.2. 重新开启A窗口, 查看设置是否成功

select @@tx_isolation ;

8.3 开启B 窗口, A 和 B 窗口选择数据库后, 都开启事务

8.4 A 窗口 只是更新两个人的账户, 不提交事务

-- tom 账户 -500 元 UPDATE account SET money = money - 500 WHERE NAME = 'tom' ; -- jack 账户 + 500 元 UPDATE account SET money = money + 500 WHERE NAME = 'jack' ;

8.5 B 窗口进行查询,没有查询到未提交的数据

mysqlselect * from account;

 8.6 A窗口commit提交数据

commit ;

8.7 B 窗口查看数据

select * from account;

 9.不可重复读演示

不可重复读 : 同一个事务中 , 进行查询操作 , 但是每次读取的数据内容是不一样的

9.1 恢复数据 ( 把数据改回初始状态 )

9.2 打开两个 窗口A 和 窗口B,选择数据库后 开启事务

use db2; start transaction ;

 9.2.1 B 窗口开启事务后, 先进行一次数据查询

select * from account;

 9.2.2 在 A 窗口开启事务后，将用户tom的账户 + 500 ,然后提交事务

-- 修改数据 update account set money = money + 500 where name = 'tom' ; -- 提交事务 commit ;

 9.2.3 B 窗口再次查询数据

 

• 两次查询输出的结果不同，到底哪次是对的？
• 不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。
• 我们可以考虑这样一种情况:
• 比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户，结果在一个事务
• 中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银
• 行工作 人员就不知道以哪个为准了

10. 解决不可重复读问题

• 将全局的隔离级别进行提升为： repeatable read
• 10.1. 恢复数据

UPDATE account SET money = 1000

10.2. 打开A 窗口, 设置隔离级别为：repeatable read

-- 查看事务隔离级别 select @@tx_isolation ; -- 设置事务隔离级别为 repeatable read set global transaction isolation level repeatable read ;

        

 10.3 重新开启 A,B 窗口 选择数据库 ,同时开启事务

10.4 B 窗口事务 先进行第一次查询

select * from account;

 10.5 A 窗口更新数据, 然后提交事务

-- 修改数据 update account set money = money + 500 where name = 'tom' ; -- 提交事务 commit ;

10.6 B 窗口 再次查询

select * from account;

• 同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别 

11.幻读演示

幻读 : select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，

无法插入，此时就发生了幻读。

11.1 打开 A B 窗口 , 选择数据库 开启事务

11.2 A 窗口 先执行一次查询操作

-- 假设要再添加一条 id 为 3 的 数据 , 在添加之前先判断是否存在 select * from account where id = 3 ;

 11.3 B 窗口 插入一条数据 提交事务

INSERT INTO account VALUES ( 3 , 'lucy' , 1000 ); commit ;

11.4 A 窗口执行 插入操作, 发现报错. 出现幻读

12.解决幻读问题

• 将事务隔离级别设置到最高 SERIALIZABLE ，以挡住幻读的发生

如果一个事务，使用了 SERIALIZABLE—— 可串行化隔离级别时，在这个事务没有被提交之前 , 其

他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且，影响数据库的

性能，数据库不会使用这种隔离级别

12.1 恢复数据

DELETE FROM account WHERE id = 3 ;

12.2 打开A 窗口 将数据隔离级别提升到最高

set global transaction isolation level SERIALIZABLE ;

12.3 打开 A B 窗口, 选择数据库 开启事务

 12.4 A 窗口 先执行一次查询操作

SELECT * FROM account WHERE id = 3 ;

12.5 B 窗口插入一条数据

INSERT INTO account VALUES ( 3 , 'lucy' , 1000 );

12.6 A 窗口执行 插入操作, 提交事务 数据插入成功.

INSERT INTO account VALUES ( 3 , 'lucy' , 1000 ); commit ;

 12.7 B 窗口在 A窗口提交事务之后, 再执行,但是主键冲突出现错误

 

总结:

• serializable 串行化可以彻底解决幻读,但是 事务只能排队执行,严重影响效率,
• 数据库不会使用这种隔离级.