MySQL8--事务管理

MySQL8事务管理

TCL:Transaction Control Language,事务控制语言

事务:在MySQL数据库中表示一条或多条Sql语句组合在一起的一个执行单元.这个执行单元要么全部执行,要么全部不执行,否则就会出现逻辑错误

比如银行里的转账:

A账号余额:1000
B账号余额:1000

现在A转500元给B,那么要完成这个转账的事务,数据中的SQL应该是这样的执行过程:

①A账号上要减少500元
update 储蓄表 set A.余额=A.余额-500 where 账号名=‘A’;

②B账号上要增加500元
update 储蓄表 set B.余额=B.余额+500 where 账号名=‘B’;

如果没有事务处理这个功能,上面的情况下,很可能会发生这样的情况:

①执行成功 A的余额变为:500
刚开始执行②的时候,突然出现某系统系统错误,导致②执行失败!
①√②×:A的钱减少了,B的钱没增加!

所以在类似的场景需求中我们需要事务处理:实现将①和②的SQL语句绑定在一起,要么都执行成功,没的事
要么不管是①执行出错还是②执行出错,数据库里的数据状态会回滚到没有执行任何①或②里的SQL语句之前

MySQL数据库中的存储引擎: innoDB

①什么是存储引擎:在mysql中的数据是用各种不同的技术来存储在磁盘文件(或内存)当中的,这种具体的存储技术就是我们说的存储引擎.

②我们可以通过show engines;命令来查看mysql支持的存储引擎.

③在mysql可以选择的这些存储引擎中,innodb,myisam,memory这个三个是最常用的,但是其中只有innodb支持事务处理,而其他是不支持事务处理的.

事务的ACID特点:

①原子性(Atomicity):组成事务的SQL语句不可在分,要么都执行,要么都不执行.

②一致性(Consistency):事务必须让数据的数据状态变化到另一个一致性的状态,比如:刚刚的例子中A和B的余额总和是2000,转账后,A和B的余额总和不能变.前后具有一致性.

③隔离性(Isolation):一个事务的执行,不受其他事务的干扰,相互应该是隔离的,但是实际上是很难做到的,要通过隔离级别做选择

④持久性(Durability):一个事务被提交,并成功执行,那么它对数据的修改就是永久性的.接下来的其他操作或出现的故障,不能影响到它执行的结果

MySQL的事务的创建:

①隐视事务:事务没有明显的开始和结束的标记.这时候像insert语句,update语句和delete语句,每一条SQL语句就默认是一个事务.

显然,隐视事务在类似转账的逻辑业务需求的时候,就无法处理了

②显视事务: 这个事务模式,就要我们中程序中手动的用命令来开启事务,和结束事务,并让事务里的多条SQL语句去执行.

注意:默认MySQL是开启自动提交事务的,用

show variables like 'autocommit';

命令可以查看到. 所以开启显示事务前,要关掉它,用

set autocommit=0;

只对本身会话有效.

①:开始事务

set autocommit=0;

start transaction; #可选的,执行set autocommit=0已经默认开启了

②:编写事务中的SQL语句

select update delete insert

语句1;语句2;…

③:结束事务
commit;提交事务去真正执行
rollback;回滚事务,恢复数据库执行前的状态

案例:

DROP TABLE IF EXISTS account;
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account(username,balance) VALUES('A',1000),('B',1000);

SET autocommit = 0; #开启事务
START TRANSACTION;

#编写sql语句

UPDATE account SET balance=balance-500 WHERE username='A';
UPDATE account SET balance=balance+500 WHERE username='B';

#结束事务

#commit;

ROLLBACK;

当有多个事务同时访问数据库中的同一个数据时,如果没有采取必要的隔离机制,就会导致各种并发错误发生:

SET autocommit = 0; #开启事务
START TRANSACTION;

#编写sql语句

UPDATE account SET balance=balance-500 WHERE username='A';

#新开终端连接数据库

SET autocommit = 0; #开启事务
START TRANSACTION;

#编写sql语句

UPDATE account SET balance=balance-500 WHERE username='A';

#语句执行过程等待

---

保存点 savepoint

SET autocommit = 0;#开启事务
START TRANSACTION;

#编写sql语句

DELETE FROM account WHERE id=1;

SAVEPOINT a; #设置保存点

DELETE FROM account WHERE id=2;

#结束事务

#commit;
ROLLBACK TO a; #回滚到保存点