MySql约束和隔离性问题练习

MySql约束和隔离性问题练习

创建一张表，要求包含：主键约束，非空约束，唯一约束；然后插入数据测试3种约束的特点

create table users(
	uid int primary key,
	uname varchar(20)  unique,
	sex char(1) default "女" not null,
	gold int
);
desc users;

测试

-- 主键不能为空
insert into users (uid,uname,sex,gold) values (null,"爱新觉罗-溥仪","男",50);
-- 主键不能值重复
insert into users (uid,uname,sex,gold) values (1,"爱新觉罗-溥仪","男",50);
insert into users (uid,uname,sex,gold) values (1,"朱由检","男",100);

-- 有非空约束的值不能为空
insert into users (uid,uname,sex,gold) values (2,"朱由检",null,100);

-- 唯一约束 有唯一约束的值不能重复
insert into users (uid,uname,sex,gold) values (2,"爱新觉罗-溥仪","男",200);

1. 主键不能为空
   
2. 主键不能值重复
   
3. 有非空约束的值不能为空
   
4. 有唯一约束的值不能重复
   

使用转账案例，或者其他案例，模拟演示脏读，不可重复读，幻读，并给出解决方案（幻读除外）

脏读

-- 准备数据
insert into users (uid,uname,sex,gold) values (2,"朱由检","男",100);
select * from users;

1. 准备数据
   
2. 设置隔离等级为最低 读取为提交

-- 设置隔离等级为最低 读取为提交
set global transaction isolation level read uncommitted;
-- 查询读取状态
select @@tx_isolation;
-- 开启新的查询窗口 结果相同
select @@tx_isolation;

3. 在查询1开启事务 查询表状态

-- 再查询2开启事务查询表状态
start transaction;
select * from users;

4. 在查询1上对数据进行操作 然后在2上查询 之后回滚1的操作 再次在2上查询

-- 2号转账给1号
update users set gold = gold - 25 where uid = 2;
update users set gold = gold + 25 where uid = 1;
select * from users;
-- 查询2 查询
select * from users;
-- 查询1 数据异常 进行了回滚操作
rollback;
select * from users;
-- 查询2 再次进行查询
select * from users;
-- 结果 ： 查询2 并未进行数据修改 数据却发生改变 脏读发生

查询1 转账

查询2 查询

查询1 回滚后 查询2 查询

解决脏读的方案

set global transaction isolation level read committed;

不可重复读

新建查询 在查询1 查询2 上开启事务 2进行查询 1更改数据 提交事务 2再进行查询

-- 查询 12 都开启事务
start transaction;
-- 查询2 进行首次查询
select * from users;

查询2首次查询：

-- 查询1更新数据提交并查询
update users set gold = gold + 25 where uid = 1;
commit;
select * from users;
-- 查询2 再次进行查询
select * from users;

查询1更新数据提交并查询

查询2查询结果：

查询2 虽然处在事务中 但是并未进行数据修改 查询2的两次查询结果却不同 此时发生了不可重复读
解决方案

-- 将全局的隔离级别进行提升为: repeatable read
set global transaction isolation level repeatable read;

幻读

新建查询 1 查询 2 都开启事务

start transaction;
-- 插入新数据 之前先判断
select * from users
 where uid = 3;

判断结果 结果为空可以插入

查询2 插入 uid 为3的数据 （uid为主键）

insert into users (uid,uname,sex,gold) values (3,"刘协","男",200);

查询1 也插入 uid=3 的数据

 insert into users (uid,uname,sex,gold) values (3,"孛儿只斤-妥懽帖睦尔","男",250);

查询1查询结果可以插入 但是实际插入却失败 发生 幻读