-- 创建db10数据库
CREATE DATABASE db10;
-- 使用db10数据库
USE db10;
-- 创建账户表
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT, -- 账户id
NAME VARCHAR(20), -- 账户名称
money DOUBLE -- 账户余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500
出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';
-- 该场景下,这两条sql语句要么同时成功,要么同时失败。就需要被事务所管理!
-- 标准语法
START TRANSACTION;
-- 标准语法
ROLLBACK;
-- 标准语法
COMMIT;
-- 开启事务
START TRANSACTION;
-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500
-- 出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';
-- 回滚事务(出现问题)
ROLLBACK;
-- 提交事务(没出现问题)
COMMIT;
-- 标准语法
SELECT @@AUTOCOMMIT; -- 1代表自动提交 0代表手动提交
-- 标准语法
SET @@AUTOCOMMIT=数字;
-- 修改为手动提交
SET @@AUTOCOMMIT=0;
-- 查看提交方式
SELECT @@AUTOCOMMIT;
1 | 读未提交 | read uncommitted |
---|---|---|
2 | 读已提交 | read committed |
3 | 可重复读 | repeatable read |
4 | 串行化 | serializable |
问题 | 现象 |
---|---|
脏读 | 是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致 |
不可重复读 | 是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致 |
幻读 | select 某记录是否存在,不存在,准备插入此记录,但执行 insert 时发现此记录已存在,无法插入。或不存在执行delete删除,却发现删除成功 |
-- 标准语法
SELECT @@TX_ISOLATION;
-- 标准语法
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;
-- 修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;
-- 查看隔离级别
SELECT @@TX_ISOLATION; -- 修改后需要断开连接重新开
脏读的问题
-- 查询账户表
select * from account;
-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;
-- 开启事务
start transaction;
-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;
-- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)
-- 窗口2查看转账结果后,执行回滚
rollback;
-- 查询隔离级别
select @@tx_isolation;
-- 开启事务
start transaction;
-- 查询账户表
select * from account;
解决脏读的问题和演示不可重复读的问题
-- 设置隔离级别为read committed
set global transaction isolation level read committed;
-- 开启事务
start transaction;
-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;
-- 窗口2查看转账结果,并没有发生变化(脏读问题被解决了)
-- 执行提交事务。
commit;
-- 窗口2查看转账结果,数据发生了变化(出现了不可重复读的问题,读取到其他事务已提交的数据)
-- 查询隔离级别
select @@tx_isolation;
-- 开启事务
start transaction;
-- 查询账户表
select * from account;
解决不可重复读的问题
-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;
-- 开启事务
start transaction;
-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;
-- 窗口2查看转账结果,并没有发生变化
-- 执行提交事务
commit;
-- 这个时候窗口2只要还在上次事务中,看到的结果都是相同的。只有窗口2结束事务,才能看到变化(不可重复读的问题被解决)
-- 查询隔离级别
select @@tx_isolation;
-- 开启事务
start transaction;
-- 查询账户表
select * from account;
-- 提交事务
commit;
-- 查询账户表
select * from account;
幻读的问题和解决
-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;
-- 开启事务
start transaction;
-- 添加一条记录
INSERT INTO account VALUES (3,'王五',1500);
-- 查询账户表,本窗口可以查看到id为3的结果
SELECT * FROM account;
-- 提交事务
COMMIT;
-- 查询隔离级别
select @@tx_isolation;
-- 开启事务
start transaction;
-- 查询账户表,查询不到新添加的id为3的记录
select * from account;
-- 添加id为3的一条数据,发现添加失败。出现了幻读
INSERT INTO account VALUES (3,'测试',200);
-- 提交事务
COMMIT;
-- 查询账户表,查询到了新添加的id为3的记录
select * from account;
/*
窗口1
*/
-- 设置隔离级别为serializable
set global transaction isolation level serializable;
-- 开启事务
start transaction;
-- 添加一条记录
INSERT INTO account VALUES (4,'赵六',1600);
-- 查询账户表,本窗口可以查看到id为4的结果
SELECT * FROM account;
-- 提交事务
COMMIT;
/*
窗口2
*/
-- 查询隔离级别
select @@tx_isolation;
-- 开启事务
start transaction;
-- 查询账户表,发现查询语句无法执行,数据表被锁住!只有窗口1提交事务后,才可以继续操作
select * from account;
-- 添加id为4的一条数据,发现已经存在了,就不会再添加了!幻读的问题被解决
INSERT INTO account VALUES (4,'测试',200);
-- 提交事务
COMMIT;
隔离级别 | 名称 | 出现脏读 | 出现不可重复读 | 出现幻读 | 数据库默认隔离级别 | |
---|---|---|---|---|---|---|
1 | read uncommitted | 读未提交 | 是 | 是 | 是 | |
2 | read committed | 读已提交 | 否 | 是 | 是 | Oracle / SQL Server |
3 | repeatable read | 可重复读 | 否 | 否 | 是 | MySQL |
4 | **serializable ** | 串行化 | 否 | 否 | 否 |
注意:隔离级别从小到大安全性越来越高,但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.