赞
踩
# 为了便于演示,将mysql的默认隔离级别设置成读未提交。
mysql> set global transaction isolation level READ UNCOMMITTED;
# 需要重启终端,进行查看
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
create table if not exists account(
id int primary key,
name varchar(50) not null default '',
blance decimal(10,2) not null default 0.0
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
-- 查看事务是否自动提交。故意设置成自动提交,看看该选项是否影响begin mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ mysql> start transaction; -- 开始一个事务begin也可以,推荐begin mysql> savepoint save1; -- 创建一个保存点save1 mysql> insert into account values (1, '张三', 100); -- 插入一条记录 mysql> savepoint save2; -- 创建一个保存点save2 mysql> insert into account values (2, '李四', 10000); -- 再插入一条记录 mysql> select * from account; -- 两条记录都在了 +----+--------+----------+ | id | name | blance | +----+--------+----------+ | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | +----+--------+----------+ mysql> rollback to save2; -- 回滚到保存点save2 mysql> select * from account; -- 一条记录没有了 +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+ mysql> rollback; -- 直接rollback,回滚在最开始 mysql> select * from account; -- 所有刚刚的记录没有了 Empty set (0.00 sec)
-- 终端A mysql> select * from account; -- 当前表内无数据 Empty set (0.00 sec) mysql> show variables like 'autocommit'; -- 依旧自动提交 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ mysql> begin; --开启事务 mysql> insert into account values (1, '张三', 100); -- 插入记录 mysql> select * from account; --数据已经存在,但没有commit,此时同时查看终端B +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+ mysql> Aborted -- ctrl + \ 异常终止MySQL --终端B mysql> select * from account; --终端A崩溃前 +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+ mysql> select * from account; --数据自动回滚 Empty set (0.00 sec)
--终端 A mysql> show variables like 'autocommit'; -- 依旧自动提交 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ mysql> select * from account; -- 当前表内无数据 Empty set (0.00 sec) mysql> begin; -- 开启事务 mysql> insert into account values (1, '张三', 100); -- 插入记录 mysql> commit; --提交事务 mysql> Aborted -- ctrl + \ 异常终止MySQL --终端 B mysql> select * from account; --数据存在了,所以commit的作用是将数据持久化到MySQL中 +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+
-- 终端 A mysql> select *from account; --查看历史数据 +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+ mysql> show variables like 'autocommit'; --查看事务提交方式 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ mysql> set autocommit=0; --关闭自动提交 mysql> show variables like 'autocommit'; --查看关闭之后结果 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ mysql> begin; --开启事务 mysql> insert into account values (2, '李四', 10000); --插入记录 mysql> select *from account; --查看插入记录,同时查看终端B +----+--------+----------+ | id | name | blance | +----+--------+----------+ | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | +----+--------+----------+ mysql> Aborted --再次异常终止 -- 终端B mysql> select * from account; --终端A崩溃前 +----+--------+----------+ | id | name | blance | +----+--------+----------+ | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | +----+--------+----------+ mysql> select * from account; --终端A崩溃后,自动回滚 +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+
-- 实验一 -- 终端A mysql> select * from account; +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+ mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ mysql> set autocommit=0; --关闭自动提交 mysql> insert into account values (2, '李四', 10000); --插入记录 mysql> select *from account; --查看结果,已经插入。此时可以在查看终端B +----+--------+----------+ | id | name | blance | +----+--------+----------+ | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | +----+--------+----------+ mysql> ^DBye --ctrl + \ or ctrl + d,终止终端 -- 终端B mysql> select * from account; --终端A崩溃前 +----+--------+----------+ | id | name | blance | +----+--------+----------+ | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | +----+--------+----------+ mysql> select * from account; --终端A崩溃后 +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+ -- 实验二 -- 终端A mysql> show variables like 'autocommit'; --开启默认提交 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ mysql> select * from account; +----+--------+--------+ | id | name | blance | +----+--------+--------+ | 1 | 张三 | 100.00 | +----+--------+--------+ mysql> insert into account values (2, '李四', 10000); mysql> select *from account; --数据已经插入 +----+--------+----------+ | id | name | blance | +----+--------+----------+ | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | +----+--------+----------+ mysql> Aborted --异常终止 -- 终端B mysql> select * from account; --终端A崩溃前 +----+--------+----------+ | id | name | blance | +----+--------+----------+ | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | +----+--------+----------+ mysql> select * from account; --终端A崩溃后,并不影响,已经持久化。autocommit起作用 +----+--------+----------+ | id | name | blance | +----+--------+----------+ | 1 | 张三 | 100.00 | | 2 | 李四 | 10000.00 | +----+--------+----------+
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。