哈尔滨网站建设 哈尔滨网站推广,通栏网站,咨询公司注册流程及费用,网络推广软件哪个好总结 自动提交 查看自动提交状态#xff1a;SELECT AUTOCOMMIT #xff1b; 设置自动提交状态#xff1a;SET AUTOCOMMIT 0 。 手动提交 AUTOCOMMIT 0 时#xff0c;使用 COMMIT 命令提交事务。 事务回滚 AUTOCOMMIT 0 时#xff0c;使用 ROLLBACK 命令回滚事务。 … 总结 自动提交 查看自动提交状态SELECT AUTOCOMMIT 设置自动提交状态SET AUTOCOMMIT 0 。 手动提交 AUTOCOMMIT 0 时使用 COMMIT 命令提交事务。 事务回滚 AUTOCOMMIT 0 时使用 ROLLBACK 命令回滚事务。 事务的实际应用让我们再回到银行转账项目
-- 转账
UPDATE user set money money - 100 WHERE name a;-- 到账
UPDATE user set money money 100 WHERE name b;SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 900 |
| 2 | b | 1100 |
-----------------这时假设在转账时发生了意外就可以使用 ROLLBACK 回滚到最后一次提交的状态
-- 假设转账发生了意外需要回滚。
ROLLBACK;SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
| 2 | b | 1000 |
-----------------这时我们又回到了发生意外之前的状态也就是说事务给我们提供了一个可以反悔的机会。假设数据没有发生意外这时可以手动将数据真正提交到数据表中COMMIT 。
手动开启事务 - BEGIN / START TRANSACTION
事务的默认提交被开启 ( AUTOCOMMIT 1 ) 后此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件使其可以发生回滚
-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money money - 100 WHERE name a;
UPDATE user set money money 100 WHERE name b;-- 由于手动开启的事务没有开启自动提交
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 900 |
| 2 | b | 1100 |
------------------- 测试回滚
ROLLBACK;SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 1000 |
| 2 | b | 1000 |
-----------------仍然使用 COMMIT 提交数据提交后无法再发生本次事务的回滚。
BEGIN;
UPDATE user set money money - 100 WHERE name a;
UPDATE user set money money 100 WHERE name b;SELECT * FROM user;
-----------------
| id | name | money |
-----------------
| 1 | a | 900 |
| 2 | b | 1100 |
------------------- 提交数据
COMMIT;-- 测试回滚无效因为表的数据已经被提交
ROLLBACK;事务的 ACID 特征与使用
事务的四大特征
A 原子性事务是最小的单位不可以再分割C 一致性要求同一事务中的 SQL 语句必须保证同时成功或者失败I 隔离性事务1 和 事务2 之间是具有隔离性的D 持久性事务一旦结束 ( COMMIT ) 就不可以再返回了 ( ROLLBACK ) 。
事务的隔离性
事务的隔离性可分为四种 ( 性能从低到高 ) READ UNCOMMITTED ( 读取未提交 ) 如果有多个事务那么任意事务都可以看见其他事务的未提交数据。 READ COMMITTED ( 读取已提交 ) 只能读取到其他事务已经提交的数据。 REPEATABLE READ ( 可被重复读 ) 如果有多个连接都开启了事务那么事务之间不能共享数据记录否则只能共享已提交的记录。 SERIALIZABLE ( 串行化 ) 所有的事务都会按照固定顺序执行执行完一个事务后再继续执行下一个事务的写入操作。
查看当前数据库的默认隔离级别
-- MySQL 8.x, GLOBAL 表示系统级别不加表示会话级别。
SELECT GLOBAL.TRANSACTION_ISOLATION;
SELECT TRANSACTION_ISOLATION;
--------------------------------
| GLOBAL.TRANSACTION_ISOLATION |
--------------------------------
| REPEATABLE-READ | -- MySQL的默认隔离级别可以重复读。
---------------------------------- MySQL 5.x
SELECT GLOBAL.TX_ISOLATION;
SELECT TX_ISOLATION;修改隔离级别
-- 设置系统隔离级别LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 查询系统隔离级别发现已经被修改。
SELECT GLOBAL.TRANSACTION_ISOLATION;
--------------------------------
| GLOBAL.TRANSACTION_ISOLATION |
--------------------------------
| READ-UNCOMMITTED |
--------------------------------脏读
测试 READ UNCOMMITTED ( 读取未提交 ) 的隔离性
INSERT INTO user VALUES (3, 小明, 1000);
INSERT INTO user VALUES (4, 淘宝店, 1000);SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
------------------------ 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子
START TRANSACTION;
UPDATE user SET money money - 800 WHERE name 小明;
UPDATE user SET money money 800 WHERE name 淘宝店;-- 然后淘宝店在另一方查询结果发现钱已到账。
SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
----------------------由于小明的转账是在新开启的事务上进行操作的而该操作的结果是可以被其他事务另一方的淘宝店看见的因此淘宝店的查询结果是正确的淘宝店确认到账。但就在这时如果小明在它所处的事务上又执行了 ROLLBACK 命令会发生什么
-- 小明所处的事务
ROLLBACK;-- 此时无论对方是谁如果再去查询结果就会发现
SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
----------------------这就是所谓的脏读一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。
读取已提交
把隔离级别设置为 READ COMMITTED
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT GLOBAL.TRANSACTION_ISOLATION;
--------------------------------
| GLOBAL.TRANSACTION_ISOLATION |
--------------------------------
| READ-COMMITTED |
--------------------------------这样再有新的事务连接进来时它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说它们看到的还是未提交的数据例如
-- 正在操作数据事务当前事务
START TRANSACTION;
UPDATE user SET money money - 800 WHERE name 小明;
UPDATE user SET money money 800 WHERE name 淘宝店;-- 虽然隔离级别被设置为了 READ COMMITTED但在当前事务中
-- 它看到的仍然是数据表中临时改变数据而不是真正提交过的数据。
SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
------------------------ 假设此时在远程开启了一个新事务连接到数据库。
$ mysql -u root -p12345612-- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
----------------------但是这样还有问题那就是假设一个事务在操作数据时其他事务干扰了这个事务的数据。例如
-- 小张在查询数据的时候发现
SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
------------------------ 在小张求表的 money 平均值之前小王做了一个操作
START TRANSACTION;
INSERT INTO user VALUES (5, c, 100);
COMMIT;-- 此时表的真实数据是
SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
------------------------ 这时小张再求平均值的时候就会出现计算不相符合的情况
SELECT AVG(money) FROM user;
------------
| AVG(money) |
------------
| 820.0000 |
------------虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据但还是会出现问题就是在读取同一个表的数据时可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED ) 。
幻读
将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT GLOBAL.TRANSACTION_ISOLATION;
--------------------------------
| GLOBAL.TRANSACTION_ISOLATION |
--------------------------------
| REPEATABLE-READ |
--------------------------------测试 REPEATABLE READ 假设在两个不同的连接上分别执行 START TRANSACTION :
-- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, d, 1000);-- 小王 - 北京
START TRANSACTION;-- 小张 - 成都
COMMIT;当前事务开启后没提交之前查询不到提交后可以被查询到。但是在提交之前其他事务被开启了那么在这条事务线上就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。
无论小张是否执行过 COMMIT 在小王这边都不会查询到小张的事务记录而是只会查询到自己所处事务的记录
SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
----------------------这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) 那么在他的这条新事务的线上跟其他事务是没有联系的也就是说此时如果其他事务正在操作数据它是不知道的。
然而事实是在真实的数据表中小张已经插入了一条数据。但是小王此时并不知道也插入了同一条数据会发生什么呢
INSERT INTO user VALUES (6, d, 1000);
-- ERROR 1062 (23000): Duplicate entry 6 for key PRIMARY报错了操作被告知已存在主键为 6 的字段。这种现象也被称为幻读一个事务提交的数据不能被其他事务读取到。
串行化
顾名思义就是所有事务的写入操作全都是串行化的。什么意思把隔离级别修改成 SERIALIZABLE :
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT GLOBAL.TRANSACTION_ISOLATION;
--------------------------------
| GLOBAL.TRANSACTION_ISOLATION |
--------------------------------
| SERIALIZABLE |
--------------------------------还是拿小张和小王来举例
-- 小张 - 成都
START TRANSACTION;-- 小王 - 北京
START TRANSACTION;-- 开启事务之前先查询表准备操作数据。
SELECT * FROM user;
----------------------
| id | name | money |
----------------------
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
------------------------ 发现没有 7 号王小花于是插入一条数据
INSERT INTO user VALUES (7, 王小花, 1000);此时会发生什么呢由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) 串行化的意思就是假设把所有的事务都放在一个串行的队列中那么所有的事务都会按照固定顺序执行执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。
根据这个解释小王在插入数据时会出现等待状态直到小张执行 COMMIT 结束它所处的事务或者出现等待超时。
