SQL 事务隔离级别

「爱情、让人受尽委屈。」 2022-06-05 04:43 160阅读 0赞

mysql b innerdb 测试  
查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation; 
    SELECT @@session.tx_isolation; 
    SELECT @@tx_isolation;
    
    set @@tx_isolation= REPEATABLE-READ set session tx_isolation='READ-COMMITTED';
    set session tx_isolation=’READ-UNCOMMITTED’;

clientA

begin ;
    select * from info where money>1500;
    
    
    update info set money=5000 where id =1 commit ;

clientB

update info set money=1000 where id =1 update info set money=4000 where id =1 select * from info

默认级别TransactionRepeatableRead 的事务中测试  
首先先查询下数据

select * from info +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  1000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    begin;
    mysql> select * from info where money>1500; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    2 rows in set (0.00 sec)

在CB中执行

update  info set money=4000 where id =1
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  4000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)

切换回CA

mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  1000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)

通过这个现象说明，在可重复读的隔离级别下，一个事务开启过程查询的数据没受到其他事务的修改影响。

继续操作CA

mysql> update  info set money=5000 where id =1 ;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  5000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  5000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)

事务提交之后的值会覆盖掉CB的修改；

--------------------

在继续看下一段的执行sql  
CA

mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info where money>1500; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    2 rows in set (0.00 sec)
    
    mysql> update  info set money=5000 where id =1  and money=1000;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0  Changed: 0  Warnings: 0
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  1000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

mysql> update  info set money=4000 where id =1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  4000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  4000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)

跟第一次的区别是事务的更新中添加了money=$\{查询的值\}  
CB的更新先与CA，这个时候update没有修改这条ID=1的数据。

--------------------

第三种情况  
CA

mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  1000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> update  info set money=5000 where id =1  and money=1000;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  5000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  4000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)

mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  4000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> update  info set money=4000 where id =1;
    Query OK, 1 row affected (7.57 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from info; +----+--------+-------+
    | id | name | money | +----+--------+-------+
    |  1 | 张三   |  4000 |
    |  2 | 马五   |  3000 |
    | 3 | 李四 | 2000 | +----+--------+-------+
    3 rows in set (0.00 sec)

这里值得说明的是  
CA的update先与CB执行，这个时候CB执行update时会发生等待。CAcommit之后CB立刻执行成功。  
这个是因为中间发生了锁的争夺；

再开启一个客户端执行如下代码即可发现了。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; +----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
    | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
    | 734524:163:3:2 | 734524      | X         | RECORD    | `project`.`info` | PRIMARY    |        163 |         3 |        2 | 1         |
    | 734521:163:3:2 | 734521 | X | RECORD | `project`.`info` | PRIMARY | 163 | 3 | 2 | 1 | +----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
    2 rows in set, 1 warning (0.00 sec)

**注**  
如果事务过程中获取到了排它锁的时候，其他事务就会发生锁等待的现象。

--------------------

mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info where name like '张%'; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    | 1 | 张小二 | 4000 | +----+-----------+-------+
    1 row in set (0.00 sec)
    
    mysql> select * from info where name like '张%'; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    | 1 | 张小二 | 4000 | +----+-----------+-------+
    1 row in set (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info where name like '张%'; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  4000 |
    | 2 | 张小三 | 3000 | +----+-----------+-------+
    2 rows in set (0.00 sec)

update info set name = '张小三' where id =2;

CB的更新发生在CA两次查询直接，但是对CA并没有产生任何的影响

--------------------

mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info ; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  4000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    | 4 | test | 10000 | +----+-----------+-------+
    4 rows in set (0.00 sec)
    
    mysql> select * from info ; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  4000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    | 4 | test | 10000 | +----+-----------+-------+
    4 rows in set (0.00 sec)
    
    mysql> insert into info(id,name,money) values(5,'test',10000);
    ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info ; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  4000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    |  4 | test      | 10000 |
    | 5 | test | 10000 | +----+-----------+-------+
    5 rows in set (0.00 sec)
    
    mysql>

在CA事务中CB插入一条记录然后CA插入CB那条记录的指定ID出现异常

**当前事务MVCC版本发生变化的时候出现幻读**

mysql> select * from info; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  5000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    | 11 | name      | 10000 |
    | 12 | name      | 10000 |
    | 13 | name      | 10000 |
    | 14 | 333 | 555 | +----+-----------+-------+
    7 rows in set (0.00 sec)
    
    mysql> select * from info for update; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  5000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    | 11 | name      | 10000 |
    | 12 | name      | 10000 |
    | 13 | name      | 10000 |
    | 14 | 333       |   555 |
    | 15 | name | 10000 | +----+-----------+-------+
    8 rows in set (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info ; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  5000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    | 11 | name      | 10000 |
    | 12 | name      | 10000 |
    | 13 | name      | 10000 |
    | 14 | 333       |   555 |
    | 15 | name | 10000 | +----+-----------+-------+
    8 rows in set (0.00 sec)
    
    mysql> select * from info LOCK IN SHARE MODE; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  5000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    | 11 | name      | 10000 |
    | 12 | name      | 10000 |
    | 13 | name      | 10000 |
    | 14 | 333       |   555 |
    | 15 | name      | 10000 |
    | 16 | name | 10000 | +----+-----------+-------+
    9 rows in set (0.00 sec)

--------------------

看下别的隔离级别

READ-COMMITTED

mysql> set session tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info ; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  4000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    |  4 | test      | 10000 |
    | 5 | test | 10000 | +----+-----------+-------+
    5 rows in set (0.00 sec)
    
    mysql> select * from info ; +----+-----------+---------+
    | id | name | money | +----+-----------+---------+
    |  1 | 张小二    | 1000000 |
    |  2 | 张小三    |    3000 |
    |  3 | 李四      |    2000 |
    |  4 | test      |   10000 |
    | 5 | test | 10000 | +----+-----------+---------+
    5 rows in set (0.00 sec)
    
    mysql>

其他的数据提交以后就直接可以读取到了  
但是事务没有提交的数据是查询不到的。这个隔离级别每次获取的数据都是最新的值。

--------------------

set session tx\_isolation=’READ-UNCOMMITTED’;  
CA

mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from info ; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |  5000 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    |  4 | test      | 10000 |
    | 5 | test | 10000 | +----+-----------+-------+
    5 rows in set (0.00 sec)
    
    mysql> select * from info ; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |     1 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    |  4 | test      | 10000 |
    | 5 | test | 10000 | +----+-----------+-------+
    5 rows in set (0.00 sec)

mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update info set money=1 where id=1;
    Query OK, 1 row affected (12.61 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from info; +----+-----------+-------+
    | id | name | money | +----+-----------+-------+
    |  1 | 张小二    |     1 |
    |  2 | 张小三    |  3000 |
    |  3 | 李四      |  2000 |
    |  4 | test      | 10000 |
    | 5 | test | 10000 | +----+-----------+-------+
    5 rows in set (0.00 sec)

CB事务还没有提交但是数据已经可以在CA事务中查询到了