剑指Offer(sql)——事务隔离级别以及各级别下的并发访问问题
我们已经知道,mysql会利用锁机制创建出来不同的事务隔离级别,此篇文章也讲会依据事务的隔离级别按照从低到高进行讲解。
当事务并发的时候,就必然会产生一系列的问题,接下来就结合这些问题,来说明和隔离级别之间的关系。
第一个问题是更新丢失,即一个事务的更新覆盖了另一个事务的更新。
但其实InnoDB已经避免了这种事情的发生,所以体验的话,可以尝试其他的存储引擎。
第二个问题是脏读,也就是说,读取了另一个更新事务,更新之前的数据,也就是,“读未提交”
READ-COMMITTED即已提交读,事务隔离级别及以上可避免。
我们可以使用select @@tx_isolation
查看mysql的默认事务隔离级别,也就是REPEATABLE-READ
。
为了方便尝试,我们也可以将事务的隔离级别调到最低,也就是读未提交,这样就可以脏读了。
Demo:
第一个事务:
- 先设置隔离级别
set session transaction isolation level read uncommitted
- 开启事务
start transaction
- 写入业务语句
update tableA set columnA = 10-1 where id = 1;
select columnA from tableA where id = 1
这时候我们理应查出来columnA = 9
但是我们不提交事务,仅仅单纯的进行更新。
第二个事务
我们直接使用select columnA from tableA where id = 1
查出来也理应是9,这是肯定没有问题的,但是我们第一个事务并没有对事务进行操作,这也就是所谓的"读未提交"。
如果我们针对第一个事务使用rollback
,那么第二个必定脏读,而如果第一个事务使用commit
,就没事了。
而我们如何避免“读未提交”呢,那就是提升隔离级别为read committed
及以上,就可以了。
第三个问题就是不可重复读——REPEATABLE-READ事务隔离级别及以上可以避免
意思也就是说,事务A多次读取某一个字段,在这个过程中,事务B对这个字段的数值进行了更改,导致读取出现了错误。
而关于这个问题的Demo就很简单了
第一步: 设置隔离级别为最低
set session transaction isolation level read uncommitted
第二步,开启事务
start transaction
第三步,用事务A查询数据
select columnA from tableA where columnA = 1
第四步,更改数据
update tableA set columnA = columnA + 1
然后提交事务2,重新回来事务1去搜索,就会发现数值变了。
而当我们将事务的隔离级别设置成repeatable read的话(set session transaction isolation level repeatable read
)
此时,不管我们事务B如何修改数据,事务A也只会查到原来的数值了。
第四个问题是幻读,可以用SERIALIZABLE的事务隔离级别避免。意思是说,事务A进行了查询全量操作,而事务B进行了删除/插入表操作,这时候A的查询结果就会像产生幻觉一样
现在来讲述一下这个Demo,假设tableA的字段为name,age
第一步,在事务A上一个共享锁,搜索全量数据
select * from tableA lock in share mode
第二步,在事务B插入一行数据
insert into tableA values(“zhangsan”,18)
第三步,开启事务