一个业务中,多次访问数据库, 如果其中有一条SQL语句出现异常,则所有已经执行的SQL语句要进行回滚,回到没有执行前的状态。 事务中所有的SQL语句要么都执行成功,要么都失败。
转账的操作
-- 创建数据表 CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); -- 添加数据 INSERT INTO account (NAME, balance) VALUES ('Jack', 1000), ('Rose', 1000);
模拟Jack给Rose转500元钱,一个转账的业务操作最少要执行下面的2条语句:
Jack账号-500
Rose账号+500
UPDATE account SET balance = balance - 500 WHERE NAME='jack';
UPDATE account SET balance = balance + 500 WHERE NAME='rose';
假设当Jack账号上-500元,服务器崩溃了。
Rose的账号并没有+500元,数据就出现问题了。
我们需要保证其中一条SQL语句出现问题,整个转账就算失败。
只有两条SQL都成功了转账才算成功。
这个时候就需要用到事务。
MYSQL中可以有两种方式进行事务的操作:
1) 手动提交事务
2) 自动提交事务
手动提交事务的SQL语句
-- 开启事务 start transaction -- 提交事务 commit -- 回滚事务 rollback
事务提交
模拟Jack给Rose转500元钱(成功) 目前数据库数据如下: 1) 使用DOS控制台进入MySQL 2) 执行以下SQL语句: 1.开启事务, 2.Jack账号-500, 3.Rose账号+500 START TRANSACTION; UPDATE account SET balance = balance - 500 WHERE NAME='jack'; UPDATE account SET balance = balance + 500 WHERE NAME='rose'; 3) 使用SQLYog查看数据库:发现数据并没有改变 SELECT * FROM account; 4) 在控制台执行commit提交任务: COMMIT; 5) 使用SQLYog查看数据库:发现数据改变 SELECT * FROM account;
事务回滚
模拟Jack给Rose转500元钱(失败) 1) 在控制台执行以下SQL语句:1.开启事务, 2.Jack账号-500 START TRANSACTION; UPDATE account SET balance = balance - 500 WHERE NAME='jack'; 2) 使用SQLYog查看数据库:发现数据并没有改变 SELECT * FROM account; 3) 在控制台执行rollback回滚事务: ROLLBACK; 4) 使用SQLYog查看数据库:发现数据没有改变 SELECT * FROM account; -- 总结: -- 如果事务中SQL语句没有问题,commit提交事务,会对数据库数据的数据进行改变。 -- 如果事务中SQL语句有问题,rollback回滚事务,会回退到开启事务时的状态。
自动提交事务
MySQL默认每一条DML(增删改)语句都是一个单独的事务, 每条语句都会自动开启一个事务,执行完毕自动提交事务, MySQL默认开始自动提交事务 ! @@autocommit=1;
取消自动提交
将金额重置为1000 UPDATE account SET balance = 1000; 更新其中某一个账户 UPDATE account SET balance = balance - 500 WHERE NAME='jack'; 使用SQLYog查看数据库:发现数据已经改变 SELECT * FROM account; 将金额重置为1000 UPDATE account SET balance = 1000; 查看MySQL是否开启自动提交事务,此时@@autocommit = 1; SELECT @@autocommit; 取消自动提交事务 SET @@autocommit = 0; 执行更新语句,使用SQLYog查看数据库,发现数据并没有改变 UPDATE account SET balance = balance - 500 WHERE NAME='jack'; SELECT * FROM account; 在控制台执行commit提交任务,发现数据已经改变 COMMIT; SELECT * FROM account;
事务原理
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到commit命令才会同步到数据表中, 其他任何情况都会清空事务日志(rollback,断开连接)
事务的步骤:
1) 客户端连接数据库服务器,创建连接时创建此用户临时日志文件 2) 开启事务以后,所有的操作都会先写入到临时日志文件中 3) 所有的查询操作从表中查询,但会经过日志文件加工后才返回 4) 如果事务提交则将日志文件中的数据写到表中,否则清空日志文件。
回滚点
在某些成功的操作完成之后,后续的操作有可能成功有可能失败, 但是不管成功还是失败,前面操作都已经成功,可以在当前成功的位置设置一个回滚点。 可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称之为回滚点。
回滚点的操作语句
回滚点的操作语句 | 语句 |
---|---|
设置回滚点 | savepoint 回滚点名字 |
回到回滚点 | rollback to 回滚点名字 |
具体操作:
1) 将数据还原到1000 UPDATE account SET balance = 1000; 2) 开启事务 START TRANSACTION; 3) 让Jack账号减3次钱,每次10块 UPDATE account SET balance = balance - 10 WHERE NAME='jack'; UPDATE account SET balance = balance - 10 WHERE NAME='jack'; UPDATE account SET balance = balance - 10 WHERE NAME='jack'; 4) 设置回滚点: SAVEPOINT three_times; SELECT * FROM account; 5) 让Jack账号减4次钱,每次10块 UPDATE account SET balance = balance - 10 WHERE NAME='jack'; UPDATE account SET balance = balance - 10 WHERE NAME='jack'; UPDATE account SET balance = balance - 10 WHERE NAME='jack'; UPDATE account SET balance = balance - 10 WHERE NAME='jack'; SELECT * FROM account; 6) 回到回滚点: ROLLBACK TO three_times; SELECT * FROM account; 7) 分析执行过程 -- 总结:设置回滚点可以让我们在失败的时候回到回滚点,而不是回到事务开启的时候。