MySQL多表&事务04-事务2

1、事务的基本介绍

   1. 概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。

  张三给李四转账500元:

    第1步操作:查询张三账户余额是否大于500

    第2步操作:张三账户金额-500

        --------------------------------出现异常,会导致500块钱不翼而飞了。被事务管理后出现异常则之前执行的操作都会回滚。

    第3步操作:李四账户金额+500

        --------------------------------第3步执行完了,要提交事务

    

   2. 操作(SQL语句)

  a. 开启事务:start transaction;

  b. 回滚:rollback;

  c. 提交:commit;

 3. 案例

准备数据

-- 创建数据表 
CREATE TABLE account (  
id INT PRIMARY KEY AUTO_INCREMENT,  NAME VARCHAR(10),  balance DOUBLE );  


-- 添加数据 
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000); 

执行操作

-- 张三给李四转账500元
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户-500
-- 加了开启事务执行后zhangsan并没有-500
UPDATE account SET balance = balance - 500 WHERE NAME='zhangsan';

-- 2. 李四账户+500
-- 出错了...会导致zhangsan-500但是lisi没有+500
出错了...
UPDATE account SET balance = balance + 500 WHERE NAME='lisi';

SELECT * FROM account;-- 加了开启事务发现zhangsan-500,但lisi并未+500


-- 发现执行没有问题,提交事务
COMMIT;

 -- 发现出问题了,回滚事务
 ROLLBACK;
 
 SELECT * FROM account; -- 回滚后zhangsan=1000,提交后zhangsan=500、lisi=1500

   4. MySQL数据库中事务默认自动提交

 a. 事务提交的两种方式:

  • 自动提交:
    • mysql就是自动提交的
    • 一条DML(增删改)语句会自动提交一次事务。
  • 手动提交:
    • 需要先开启事务,再提交
    • Oracle数据库默认是手动提交事务

 b. 修改事务的默认提交方式:

  • 查看事务的默认提交方式:SELECT @@autocommit; -- 1代表自动提交,0代表手动提交
  • 修改默认提交方式:set @@autocomment = 0;

2、事务的四大特征

   1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。

 2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据。

 3. 隔离性:多个事务之间相互独立。

 4. 一致性:事务操作前后,数据总量不变。

3、事务的隔离级别(了解)

   1. 概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。

 2. 存在问题:

  a. 脏读:一个事务读取到另一个事务中没有提交的数据

  b. 不可重复读(虚读):在同一个事务中两次读取到的数据不一样。

  c. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。MySQL数据库中演示不出来幻读。

 3. 隔离级别:

  a. read uncommitted:读未提交

    产生的问题:脏读、不可重复读、幻读

  b. read committed:读已提交(Oracle)

    产生的问题:不可重复读、幻读

  c. repeatable read:可重复读(MySQL默认)

    产生的问题:幻读

  d. serializable:串行化

    可以解决所有的问题

  注意:隔离级别从小到大安全性越来越高,但是效率越来越低。一般情况下不会修改数据库的默认隔离级别。

 4. 数据库查询隔离级别:select @@tx_isolation; 

  

 5. 数据库设置隔离级别:set global transaction isolation level 级别字符串; 

  

 设置完后需要断开连接,重新打开才能看到设置成功

    

4、演示设置不同隔离级别产生的问题

 a. 设置read uncommitted:

  操作步骤:

    i. cmd终端1:mysql -uroot -proot

    ii. use db3;

    iii. select * from account;

    iiii. SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

     

    v. START TRANSACTION;

      

    iv. 开另一个cmd终端2:执行i、ii、iii(查询结果zhansan=1000,lisi=1000)

    iiv. 终端2:select @@tx_isolation;

      

    iiiv. 终端2:执行第5步操作

       iiiiv. 终端1:执行转账操作

      update account set balance = balance -500 where id = 1;

      update account set balance = balance +500 where id =2;

    vv. 终端2:执行iii,查询结果:zhangsan=500,lisi=1500 ------------这就是脏读

    ivv. 终端1:rollback;

    iivv. 终端2:执行iii,查询结果:zhangsan=1000,lisi=1000-----------这就是不可重复读

 b. 设置read committed:

  操作步骤:

    i. cmd终端1:mysql -uroot -proot

    ii. use db3;

    iii. select * from account; -- zhangsan=1000,lisi=1000

    iiii. SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

     

    v. START TRANSACTION;

      

    iv. 开另一个cmd终端2:执行i、ii、iii(查询结果zhansan=1000,lisi=1000)

    iiv. 终端2:select @@tx_isolation;

      

    iiiv. 终端2:执行START TRANSACTION;

       iiiiv. 终端1:执行转账操作

      update account set balance = balance -500 where id = 1;

      update account set balance = balance +500 where id =2;

    vv. 终端2:执行iii,查询结果zhangsan=1000,lisi=1000 -------------------没有脏读问题

    ivv. 终端1:commit;

    iivv. 终端2:执行iii,查询结果:zhagnsan=500,lisi=1500  -----------------不可重复读问题

  c. 设置repeatable read:

  操作步骤:

    i. cmd终端1:mysql -uroot -proot

    ii. use db3;

    iii. select * from account; -- zhangsan=1000,lisi=1000

    iiii. SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

     

    v. START TRANSACTION;

      

    iv. 开另一个cmd终端2:执行i、ii、iii(查询结果zhansan=1000,lisi=1000)

    iiv. 终端2:select @@tx_isolation;

      

    iiiv. 终端2:执行START TRANSACTION;

       iiiiv. 终端1:执行转账操作

      update account set balance = balance -500 where id = 1;

      update account set balance = balance +500 where id =2;

    vv. 终端2:执行iii,查询结果zhangsan=1000,lisi=1000 -------------------没有脏读问题

    ivv. 终端1:commit;

    iivv. 终端2:执行iii,查询结果:zhangsan=1000,lisi=1000  -----------------没有不可重复读问题

    iiivv. 终端2:执行commit;

    iiiivv. 终端2:执行iii,查询结果:zhangsan=500,lisi=1500

 d. 设置serializable:

  操作步骤:

    i. cmd终端1:mysql -uroot -proot

    ii. use db3;

    iii. select * from account; -- zhangsan=500,lisi=1500

    iiii. SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    v. START TRANSACTION;

      

    iv. 开另一个cmd终端2:执行i、ii、iii(查询结果zhansan=500,lisi=1500)

    iiv. 终端2:select @@tx_isolation;

     

    iiiv. 终端2:执行START TRANSACTION;

       iiiiv. 终端1:执行转账操作

      update account set balance = balance -500 where id = 1;

      update account set balance = balance +500 where id =2;

    vv. 终端2:执行iii,查询结果:光标一直在第二行闪烁,查询并没有执行。只有当终端1回滚或提交之后终端2才会完成查询动作,相当于这张表被锁起来了。

      

     ivv. 终端1:执行commit,终端2立马出结果

      

     这就是串行化,它会解决所有问题,因为会把表锁上。这样效率就会很低。

 

posted on 2021-12-19 03:34  花溪月影  阅读(69)  评论(0编辑  收藏  举报