MySQL-事务那点事

文章目录

事务

课前题目

建立一个触发器，当订货时，会判断库存是否够，如果够，则订货信息会顺利的写入order_detail表，
库存信息更新，如果该商品库存不够，报错"商品库存不足"。

create table goods(   #商品表
    gid char(10),  #商品编号
    number int   #库存数目
);

create table order_detail(   #订单表
    gid char(10),
    number int,  #下单数目
    ordertime timestamp
);
insert into goods values("P1",100),("P2",100);
insert into order_detail values("P1",100,"2020-01-20 12:33:33"),("P2",100,"2020-01-20 12:33:33");

CREATE TRIGGER tao
BEFORE INSERT
ON order_detail
for EACH row
BEGIN
	DECLARE blance INT;#blance为库存表中的余额
	SELECT number INTO blance FROM goods WHERE gid = new.gid;#根据商品ID来查询goods表中的库存
	IF blance < new.number THEN SIGNAL SQLSTATE '45000'#如果库存小于要订的数量就库存不足
		SET message_text = '商品库存不足',MYSQL_ERRNO = 1888;
	ELSE
		UPDATE goods SET number = blance - new.number WHERE gid = new.gid;#如果库存大于要订的数量就修改库存中的库存数量
	END if;
END;

#库存不足
INSERT INTO order_detail VALUES("P1",101,"2020-01-20 12:33:33");

#库存-20
INSERT INTO order_detail VALUES("P1",20,"2020-01-20 12:33:33");

INSERT INTO order_detail VALUES("P2",30,"2020-01-20 12:33:33");

Navicat中如何打开触发器编辑环境
在这里插入图片描述
在这里插入图片描述

image-20201130202004305

image-20201130202021451

image-20201130202033497

一、事务的含义

事务就是将一组SQL语句放在同一批次内去执行，作为一个整体密不可分。

如果一个SQL语句出错，则该批次内的所SQL都将被取消执行。

image-20201130203949165

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一 旦执行失败或产生错误，整个单元将会回滚。所有受到影 响的数据将返回到事物开始以前的状态；如果单元中的所 有SQL语句均执行成功，则事物被顺利执行。

image-20201130204002654

在转账过程中，可能会思考下列问题：
1.如何同时保证上述交易中，A账户总金额减少100，B账户总金额增加100？
2.A账户如果同时在和C账户交易(T2)，如何让这两笔交易互不影响？
3.如果交易完成时数据库突然崩溃，如何保证交易数据成功保存在数据库中？
4.如何在支持大量交易的同时，保证数据的合法性(钱没凭空多出来或者消失) ？

二、特点（ACID）

A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
C 一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的
D 持久性：一个事务一旦提交了，则永久的持久化到本地

三、事务的使用步骤 ★

了解：

3.1 隐式（自动）事务：

没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete
显式事务：具有明显的开启和结束

image-20201130204031072

3.2 使用显式事务

① 开启事务

set autocommit=0;
start transaction;#可以省略

② 编写一组逻辑sql语句

注意：sql语句支持的是insert、update、delete

③ 设置保存点

除了撤销整个事务，用户还可以使用ROLLBACK TO语句使事务回滚到某个点，在这之前需要使用SAVEPOINT语句来设置一个保存点。
SAVEPOINT语法格式为：
SAVEPOINT identifier
其中，identifier为保存点的名称。
ROLLBACK TO SAVEPOINT语句会向已命名的保存点回滚一个事务。如果在保存点被设置后，当前事务对数据进行了更改，则这些更改会在回滚中被撤销，语法格式为：
ROLLBACK TO SAVEPOINT identifier
当事务回滚到某个保存点后，在该保存点之后设置的保存点将被删除。
savepoint 保存点名;

④ 结束事务

提交：commit;只有commit才能将内存中的数据提交到数据库中

  • 结束事务

    COMMIT语句是提交语句，它使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，也标志一个事务的结束，其语法格式为：
    COMMIT；
    注意：MySQL使用的是平面事务模型，因此嵌套的事务是不允许的。在第一个事务里使用START TRANSACTION命令后，当第二个事务开始时，自动地提交第一个事务。同样，下面的这些MySQL语句运行时都会隐式地执行一个COMMIT命令：

DROP DATABASE / DROP TABLE
CREATE INDEX / DROP INDEX
ALTER TABLE / RENAME TABLE
LOCK TABLES / UNLOCK TABLES
SET AUTOCOMMIT=1
  • 回滚：rollback

    撤销事务
    ROLLBACK语句是撤销语句，它撤销事务所做的修改，并结束当前这个事务。
    语法格式：
    ROLLBACK
    在前面的举例中，若在最后加上以下这条语句：
    rollback;
    执行完这条语句后，前面的动作将被撤销，可以使用SELECT语句查看该行数据是否还原。
    回滚到指定的地方：rollback to 回滚点名;

  • 注意：修改引擎为innoDB

    在这里插入图片描述

    3.3 使用案例

    模拟网上支付

    顾客A在线购买一款商品，价格为500.00元，采用网上银行转账的方式支付。

    假如顾客A银行卡的余额为2000.00元，且向卖家B支付购买商品费用500.00元，起始卖家B的账号金额10000.00元

    创建数据库shop和创建表account并插入2条数据，如下图所示：

    image-20201130210116560

    

    CREATE TABLE account(
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(32),
    cash int,
    vaild INT COMMENT '0为冻结，1位正常'
);

INSERT INTO  account (name,cash,vaild) VALUES ('A',2000,1);
INSERT INTO  account (name,cash,vaild) VALUES ('B',10000,1);

#关闭自动提交
SET autocommit = 0;

# ① 开启事务
START transaction;
INSERT INTO account(id) VALUES(3);
INSERT INTO account(id) VALUES(3);#这句出错就不会执行下面的操作，所以当我们把这四句一起执行的时候，一条记录都无法插入，因为这句出错了就不会执行committ提交事务。
#运行的语句都已经将结果存放在内存中，如果commint提交了就会将内存中得到的结果存入到数据库中。
# ④ 提交事务
COMMIT;#如果将四句都运行了，再来运行commit就可以将第一句select语句执行的结果直接从内存插入到数据库中

    转账前数据表为：

    image-20201130212839968

    1. A账户给B账户转账，如果正常，A账户的钱会减少，B账户钱会增加，但A账户钱不够或者B账户不可用，这时则需返回A账户的500元，达到账户总额的平衡

    要求：使用事务结合存储过程模拟以上过程

    思路：

    #首先修改了转出账户余额 = 转出账户余额 - 转账金额
    #确定 余额 = 转出账户 - 转账金额是否大于0，大于0就继续执行下面的操作。小于0就要回滚，让余额恢复回去
    #如果余额大于0，就执行修改转入账户的余额，修改为 转入账户余额 = 转入账户余额 + 转账金额。
    #接下来判断转入账户是否有效，根据表中的valid来查询是否有效。有效才能转入成功，无效就要回滚事务，将钱退回到A账户。

    navicat中过程的定义位置
    在这里插入图片描述

    ——

    #outid转出账户，inid转入账户，money是转账金额
CREATE PROCEDURE p_money(IN outid int,IN inid int,IN money int)
BEGIN
	DECLARE blance,val int;
	update account SET cash = cash - money WHERE id = outid;
	#从account表中把cash字段的值查出来赋给blance变量
	SELECT cash into blance FROM account WHERE id = outid;
	IF blance < 0
		THEN ROLLBACK;
	ELSE 
		UPDATE account SET cash = cash + money WHERE id = inid;
		SELECT vaild INTO val FROM account WHERE id = inid;
		IF val = 0 THEN ROLLBACK;
		END if;
	END if;
	COMMIT;
END;

START transaction;
select * from account;
CALL p_money(1,2,500);
select * from account;
COMMIT;

    转账后数据表为：

    image-20201130213116362

    A账户转给B账户500元，A账户的余额为1500元，B账户的余额为10500元。

    2. A给B转账成功后，B给C转账2倍金额（比如A->B 500元，B->C 1000元），如果B账户钱不够或者C账户不可用，这时候回滚到A->B刚转完的阶段。
    要求：使用事务结合存储过程模拟以上过程

    思路：

    和上面的解题思路没什么变化，只是如果A和B交易成功了，在执行B和C的交易之前设置一个保存点，保存A和B的交易，在和C交易过程中，如果失败了就直接回滚到保存点，之前A和B的交易并不会变化，不会受影响。

    CREATE PROCEDURE p_money2(IN outid int,IN inid int,IN inidc int,IN money int)
BEGIN
	DECLARE blance,blanceb,val,valc int;
	update account SET cash = cash - money WHERE id = outid;
	SELECT cash into blance FROM account WHERE id = outid;
	IF blance < 0
		THEN ROLLBACK;
	ELSE 
		UPDATE account SET cash = cash + money WHERE id = inid;
		SELECT vaild INTO val FROM account WHERE id = inid;
		IF val = 0 THEN ROLLBACK;
			ELSE 
				SAVEPOINT s1;
				UPDATE account SET cash = cash - 2*money WHERE id = inid;
				SELECT cash INTO blanceb FROM account WHERE id = inid;
				if blanceb < 0
					THEN ROLLBACK to SAVEPOINT s1;
				ELSE
					UPDATE account SET cash = cash + 2*money WHERE id = inidc;
					SELECT vaild INTO valc FROM account WHERE id = inidc;
						IF valc = 0 THEN ROLLBACK to SAVEPOINT s1;
						END if;
				END if;
		END if;
	END if;
	COMMIT;
END;

#修改account表中的数据id为3的记录，加入C账户，并将它的账户为0
UPDATE account SET name = 'C',cash = 0,vaild = '1' WHERE id = 3;
COMMIT;

CALL p_money2(1,2,3,500);

    使用存储过程前：

    image-20201130214605099

    使用存储过程后：

    image-20201130215142132

    A账户转给B账户500元，A账户的余额为1500元，B账户的余额为10500元。但是B账户又要转2*500=1000给C账户，所以B账户的余额为为10000元，C账户的余额为1000元。

    #现在我把C账户的账户从正常改为冻结，那么执行存储过程的得到的结果应该是A和B之间的交易正常执行，只是B和C直接的交易无法正常执行
UPDATE account SET vaild = 0 WHERE id = 3;
COMMIT;

CALL p_money2(1,2,3,500);

    image-20201130220357763

    image-20201130220500742

    可以看到保存点的作用起到了效果，C账户的余额无变化，但是A账户和B账户的余额都发生了正常交易。

    四、并发事务

    4.1 事务的并发问题是如何发生的？

    多个事务同时操作同一个数据库的相同数据时。

    和我之前Java文章多线程相似，多个线程同时共享一组数据时，会出现线程安全问题

    4.2 并发问题都有哪些？

    脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
    不可重复读：一个事务多次读取，结果不一样
    幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据

    4.3 如何解决并发问题

    通过设置隔离级别来解决并发问题

    4.4 隔离级别

    x为不能避免，√为能避免脏读不可重复读幻读
    read uncommitted: 读未提交×××
    read committed：读已提交√××
    repeatable read：可重复读√√×
    serializable：串行化√√√

    mysql中默认为第三个隔离级别 repeatable read

    MySQL默认为REPEATABLE READ隔离级，这个隔离级适用于大多数应用程序，只有在应用程序有具体的对于更高或更低隔离级的要求时才需要改动。没有一个标准公式来决定哪个隔离级适用于应用程序—大多数情况下，这是一个主观的决定，它是基于应用程序的容错能力和应用程序开发者对于潜在数据错误的影响的判断。隔离级的选择对于每个应用程序也是没有标准的。例如，同一个应用程序的不同事务基于执行的任务需要不同的隔离级。

    图中列出了事务隔离级和性能之间的关系。

    image-20201130223524739

    判断题：

    数据库的隔离级别越高，并发性能越低。 √

    事务执行时间越短，并发性能越高。 ×

    oracle中默认为第二个隔离级别 read committed

    查看隔离级别
    select @@tx_isolation;
    设置隔离级别
    set session|global transaction isolation level 隔离级别;

    未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

    提交读(Read Committed)：只能读取到已经提交的数据，避免脏读

    可重复读(Repeated Read)：可重复读,在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读。

    串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞，消除不可重复，脏读，幻读

    4.5 隔离机制

    MySQL提供了LOCK TABLES语句来锁定当前的表，实现隔离机制，语法格式如下：

    LOCK TABLES 表名 [AS 别名] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

    说明： 表锁定支持以下类型的锁定。

    lREAD：读锁定，确保用户可以读取表，但是不能修改表。

    WRITE：写锁定，只有锁定该表的用户可以修改表，其他用户无法访问该表。

    锁是一种用来防止多个客户端同时访问数据而产生问题的机制。

    其实和我之前java多线程文章中的同步锁机制十分相似。

    InnoDB实现了以下两种类型的行锁

    • 独占锁（X锁或写锁）：若事务T对数据对象A加上X锁则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其他事务在T释放A上的锁之前不能修改A，但可以读取A。
    • 共享锁（S锁或读锁）：若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了T释放A上的S锁之前不能对A做任何修改。

    4.6 情景模拟

    4.6.1 脏读：

    Alex给Bill转账100元购买商品。

    Alex开启事务后转账，但不提交事务，通知Bill来查询，

    如果Bill的隔离级别较低，就会读取到Alex的事务中未提交的数据，

    发现Alex确实给自己转了100元，就给Alex发货。

    等Bill发货成功后，Alex将事务回滚，Bill就会受到损失。

    CREATE TABLE money(
	id int PRIMARY KEY,
	name VARCHAR(20),
	money int
);

INSERT into money VALUES(1,'Alex',200);
INSERT into money VALUES(2,'Bill',200);
    1. 将两个连接设置隔离级别为未提交读

    image-20201201063902729

    1. 关闭自动提交

      image-20201201064035706

    2. 在Alex这个连接上去转账给Bill,但是先不提交

      image-20201201064549814

    3. 然后在Bill连接上查看自己的money时，发现果真多了100元

      image-20201201064716234

    4. 这时候Alex干坏事，直接回滚，然后Bill再重新查询自己的money

      Alex执行回滚

      image-20201201064844838

      Bill再查看自己的money，怎么变成200了,我的100元呢

      image-20201201065016422

      其实这里也可以看到Bill去查询自己money时，不仅读到了脏数据（脏读），同时在读取一个事务中结果是不一样的，这也就是不可重复读和幻读的体现。

      解决方案：提交读(Read Committed)

      1. 将两个连接隔离级别改为提交读

    #设置隔离级别为未提交读
set session transaction isolation level Read committed

#查看隔离级别
select @@tx_isolation;

    重复上述Alex给Bill转账的过程，但是未提交事务。再从Bill那里读取money

    image-20201201070640416

    Bill发现自己的钱还是200，于是他对Alex说，你不转钱给我怎么给你发货

  • 然后Alex不得不把钱转给他，就没有执行回滚，直接提交了。Bil再看看自己money.

    image-20201201070926711

    这时候知道钱到账了，解决了脏读。但是同一个事务查询的结果不同，刚开始200，现在300，所以还是没避免不可重复读和幻读。

  • 4.6.2 不可重复读：

    银行在网站后台统计所有用户的总金额来做报表，同一个报表事务中，先后两次查询。

    第1次查询Alex有200元，

    第2次查询Alex有500元。

    原因：第2次查询前Alex存了了300元。

    先要把隔离级别设置为提交读
set session transaction isolation level Read committed

SELECT * FROM money WHERE name = 'Alex'


#银行工作人员
START TRANSACTION;
SELECT * FROM money WHERE name = 'Alex';#第一次查询，没提交事务就去干别的了
SELECT * FROM money WHERE name = 'Alex';#第二次查询

#Alex存钱
START transaction;
UPDATE money SET money = 500 WHERE name = "Alex";
COMMIT;#趁工作人员上厕所偷偷存了一笔钱
    1. 首先呢银行工作人员查这个Alex的余额，一看他还有200元。然后他突然去上个厕所。而且没提交事务。

    image-20201201140825789

    1. 这时候可能恰巧Alex就存了300元，余额就变为500元。并且提交了事务。

      image-20201201141207590

    2. 这时候上厕所的工作人员回来，再查看一下Alex的账户余额，突然多了300元，就是和上一次查询的结果不一样，所以被称为不可重复读。

      image-20201201141341191

    解决方案：可重复读(Repeated Read)

    1. 将隔离界别改为可重复读，然后上厕所之前查询Alex的余额为200。

    image-20201201142440369

    1. 然后Alex趁工作人员去上厕所，偷偷存了300元。

    image-20201201142459145

    1. 上完厕所再来查一遍，还是200元。

    image-20201201142513203

    1. 等银行工作人员提交了这个事务，再来重新开一个事务查询的话，就是500元了。

    image-20201201142855313

    4.6.3 幻读：

    银行职员A收到开户申请，新开一个ID为3的用户账号，在开户事务中：

    1. 查看ID为3的用户账号是否已经存在（颜色加深了的代码块才是我执行的）

    image-20201201143920174

    1. 不存在，则插入ID为3的用户账号

    2. 发现无法插入，数据库表示已有ID为3的用户账号

    image-20201201144006920

    1. 再度查看，ID为3的账号仍旧不存在

    image-20201201144023003

    原因：在事务运行1,2步骤中途，B员工往里面插入了3号用户，这里commit提交了。

    image-20201201144100697

    解决方案：串行读(Serializable)

    先将职员A的隔离界别改为串行读。再去执行上述的1、2步骤。

    SET session TRANSACTION ISOLATION LEVEL Serializable;

    在1、2步骤之间通过B职员插入信息的时候，查询一直运行，最后结果超时报错。此时其实就是A职员拿到了锁，然后B职员一直在等A职员释放锁，就是A职员一直未提交事务，所以B职员开启事务后无法对表中的相同数据修改。知道A提交事务。

    image-20201201144527640

    这时候插入就不会报错了

posted @ 2022-04-06 23:52  szmtjs10  阅读(71)  评论(0)  编辑  收藏  举报