数据库安全性问题

数据库安全性问题

准备数据

创建一张账户表，并添加数据

create table account(
    id int primary key auto_increment,
    name varchar(20),
    money double
);

insert into account values (null,'zs',1000);
insert into account values (null,'ls',1000);
insert into account values (null,'ww',1000);

演示脏读

一个事物里面读到了另外一个事物没有提交的数据：read uncommitted(读未提交)

• 开启A，B命令行窗口，连接登录MySQL

• 分别查询A，B窗口的隔离级别

  select @@tx_isolation;
  

  

• 设置A窗口的隔离级别为：read uncommitted(读未提交)

  set session transaction isolation level read uncommitted;
  

  

• A，B窗口都开启事物

  start transaction;
  

  

• 在B窗口中操作zs向ls转账100，事物不提交

  -- 操作前先查询表中数据
  select * from account;
  -- zs向ls转账100，即zs账户减少100，而ls账户增加100
  update account set money = money - 100 where name = 'zs';
  update account set money = money + 100 where name = 'ls';
  

• 在A窗口中查询账户

  select * from account;
  

  

  可以看到在B窗口中转账操作完成后并没有提交事务，但是在A窗口中查询到了B窗口没有提交的事务，即一个事物里面读到了另外一个事物没有提交的数据，叫做脏读

---

演示不可重复读并解决脏读

在一个事物里面，同一条语句，两次查询的结果不一致：Read committed(读已提交)

• 开启A，B命令行窗口，连接登录MySQL

• 分别查询A，B窗口的隔离级别

  select @@tx_isolation;
  

  

• 设置A窗口的隔离级别为：Read committed(读已提交)

  set session transaction isolation level Read committed;
  

  

• A，B窗口都开启事物

  start transaction;
  

  

• 在B窗口中操作zs向ls转账100，事物不提交

  -- 操作前先查询表中数据
  select * from account;
  -- zs向ls转账100，即zs账户减少100，而ls账户增加100
  update account set money = money - 100 where name = 'zs';
  update account set money = money + 100 where name = 'ls';
  

  

• 在A窗口中查询账户【可以看到此时已经避免了脏读发生】

  select * from account;
  

  

• 在B窗口中提交事物

  commit;
  

• 在A窗口中查询账户【两次查询的结果不一致，不可重复读发生】

  select * from account;
  

  

  可以看到在B窗口提交事务后查询出的数据和在提交事务前查询出的数据不一致，即发生了不可重复读

---

演示避免不可重复读

• 开启A，B命令行窗口，连接登录MySQL

• 分别查询A，B窗口的隔离级别

  select @@tx_isolation;
  

  

• 设置A窗口的隔离级别为：Repeatable read(可重复读)

  set session transaction isolation level Repeatable read;
  

  

• A，B窗口都开启事物

  start transaction;
  

  

• 在B窗口中操作zs向ls转账100，事物不提交

  -- 操作前先查询表中数据
  select * from account;
  -- zs向ls转账100，即zs账户减少100，而ls账户增加100
  update account set money = money - 100 where name = 'zs';
  update account set money = money + 100 where name = 'ls';
  

  

• 在A窗口中查询账户【没有发生脏读】

  select * from account;
  

  

• 在B窗口中提交事物

  commit;
  

• 在A窗口中查询账户【没有发生不可重复读】

  select * from account;
  

  

  可以看到在B窗口提交事务后查询出的数据和在提交事务前查询出的数据一致，即没有发生不可重复读

• A窗口中结束事物，再重新查询

  commit;
  select * from account;
  

  

  在A窗口提交事务之后再次查询，此时A窗口的事务已经结束，查询到了B窗口中提交的事务，可以看出两个事务并没有相互影响，而是保持了互相隔离，即没有发生不可重复读

  MySQL数据库默认隔离级别：Repeatable read(可重复读)

---

演示隔离级别Serializable(串行化)

通过强制事务排序，让事务必须以顺序的方式执行，使之不可能相互冲突，在前一个事务提交之前后面的事务无法进行提交，从而解决了幻读的问题。

它在每个读的数据行上面加上了共享锁，但是可能会导致超时和锁竞争(这种隔离级别太极端，实际生产基本不使用)，而且不能并发操作，导致效率低下。

• 开启A，B命令行窗口，连接登录MySQL

• 分别查询A，B窗口的隔离级别

  select @@tx_isolation;
  

  

• 设置A窗口的隔离级别为：Serializable(串行化)

  set session transaction isolation level Serializable;
  

  

• A，B窗口都开启事物

  start transaction;
  

  

• 在B窗口中向account账户插入一条数据，事务不提交

  -- 操作前先查询表中数据
  select * from account;
  -- 在account表中插入一条数据
  insert into account values (null,'zl',1000);
  

  

• 在A窗口中结束事物

  commit;
  

  

  当在B窗口中执行插入数据操作后，会发现任务进程卡住，这是因为A窗口下隔离级别为 Serializable(串行化) ，在这种情况下，只有当A窗口中的事务结束后，B窗口的事务操作才能继续进行

• 在A窗口中查询账户

  select * from account;
  

  

• 在B窗口中结束事物

  commit;
  

• 在A窗口中查询

  select * from account;
  

  

  注意：

  • 隔离级别为 Read committed(读已提交) 、 read uncommitted(读未提交) 、Repeatable read(可重复读) 时，都可能会发生幻读，即一个事务中两次读取的数据的数量不一致，这是由于insert或delete时引发的问题；而不可重复读是因为一个事务中两次读取的数据的内容不一致，这是由于update时引发的问题。
  • 设置隔离级别为 Serializable(串行化) 可以解决幻读的问题。

---