mysql查询和修改事务隔离级别与for update的作用测试以及事务的测试

一.查询数据库事务隔离级别

mysql数据库，当且仅当引擎是InnoDB，才支持事务；

查询事务隔离级别使用：select @@tx_isolation;

查询了官方文档，在8.0+就已经抛弃了这样的查询方法，https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

在MySQL 8.0.3 中，该变量已经被 transaction_isolation 替换了。

最新的查询方法有多种，下面提供2种：

1、select @@transaction_isolation;

2.show variables like 'transaction_isolation';

查询全局事务隔离级别的方法：

1.select @@global.transaction_isolation；

二.修改数据库事务隔离级别

事务的隔离级别分为：未提交读(read uncommitted)、已提交读(read committed)、可重复读(repeatable read)、串行化(serializable)。

//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;

以上方法是将MySQL中的事务隔离级别改变成：各个级别，范围是当前session，即一个cmd窗口的范围。也就是会话的事务隔离级别。

注意：mysql数据库的事务隔离级别分为会话和全局两个范围

这里修改事务权限的语句是：set [ global | session ] transaction isolation level Read uncommitted | Read committed | Repeatable | Serializable;

如果选择global，意思是此语句将应用于之后的所有session，而当前已经存在的session不受影响。

如果选择session，意思是此语句将应用于当前session内之后的所有事务。

如果什么都不写，意思是此语句将应用于当前session内的下一个还未开始的事务。

三.测试for update的作用以及事务的一些测试

开始之前先了解一些东西

MySQL默认操作模式就是autocommit自动提交模式。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。

MySQL的默认数据库事务隔离级别是可重复读(repeatable read)；

开始测试本地一张student表只有一条数据

用naviacte创建两个会话并且通过set autocommit = 0; 将事务设置为手动提交

一.测试 for update的查询

事务A 执行sql

begin;

select * from student where id=1 for update；

此时没有commit；

事务B执行sql 

begin;

select * from student where id=1；

此时事务A可以查询出数据事务b也可以查询出数据

但是如果将事务B中select * from student where id=1；换成select * from student where id=1 for update；事务B后执行的话会不能查出数据并且等待直到事务A执行commit；事务B才能查到数据。

结论：两个事务同时用for update查询一条数据的话有互斥作用 即排他锁。

二.测试 for update的修改

事务A执行

begin;

select * from student where id=1 for update;
UPDATE student set name='zhangsan' where id=1;

但是不提交

B事务执行

begin;

select * from student where id=1;//查出的name值还是haha 并且这里没有for update
UPDATE student set name='lisi' where id=1;//这里执行的时候数据库会等待直到事务A提交事务

然后A和B按先后顺序提交事务 最终name值是lisi

结论：两个事务对一个数据进行操作的时候 如果只有一个事务中使用了for update进行锁行  那么数据存在一定的问题。即事务B读取的是事务A提交之前的数据 数据A修改的数据直接被事务B覆盖了 （在代码中存着这样一种状况在事务A的代码中使用forupdate查询student对象年龄为19，并修改年龄+1并且修改student但是不提交，然后事务B的代码中查询student对象查询出来的年龄为19 并且修改年龄+1 这时候事务b的修改无法执行在等待A的提交 然后 B也提交 最终的年龄是20  导致年龄少1；所以要将事务A和B都用for update查询或者 都放在serializable事务中） 。

那么如何避免这种情况呢 有两种方式。

1.事务A和B在事务开始都对这个数据进行for update查询则事务AB会串行

2.修改事务隔离级别为serializable。

补充：one.事务A和B都是串行的时候 事务A查询a数据 不提交 事务B查询a数据不提交

（这时候任何一个事务修改a数据的话都要等另外一个事务提交后 才能修改数据并且提交。）

假如此时A事务要修改a  则B事务要提交查询事务之后A才能修改进内存并且提交事务，至此a修改成功。

假如此时A事务 要修改a B事务也修改a 则先修改的事务可以将值修改进内存，后修改的事务会报错Deadlock found when trying to get lock; try restarting transaction。 然后事务先修改的事务在提交之后数据修改生效。

two.事务A和B都是串行的时候 事务A查询a数据 并且修改a 不提交 事务B查询a数据不提交

    此时事务B是无法查到数据a的 知道事务A提交才能查询到。

补充:

年龄初始为19

事务A执行

begin;

select * from student where id=1 for update;
UPDATE student set age=age+1 where id=1;
不提交

事务B执行

begin;

UPDATE student set age=(select age from student where id=1)+1 where id=1;//不要问我为什么不使用UPDATE student set age=age+1 where id=1；我是模拟代码的流程 如果直接用age=age+1 则结果是21, 为什么这种形式就可以呢下面讨论

然后事务AB先后提交最终结果是20  但是预期结果是21.

 

 

UPDATE student set age=age+1 where id=1

1. 本句本身具有原子性

2. 当前读（包含update等写入操作）锁定数据，直到事务提交

参考：https://blog.csdn.net/silyvin/article/details/79294508?tdsourcetag=s_pctim_aiomsg