mysql事务隔离级别和锁

2021年7月1日 11点热度 0条评论 来源: 白露非霜

1.数据库的锁

从性能上分为乐观锁和悲观锁:乐观锁是利用版本号,比如数据字段新增一个版本号字段,操作的时候进行版本的比对,需要开发者自己实现;悲观锁就是在操作数据时,认为此操作会出现数据冲突,所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作,这点跟java中的synchronized很相似,因此悲观锁需要耗费较多的时间。悲观锁是由数据库自己实现了的,执行CRUD操作都会涉及到。

从对对数据库的操作类型上面可以分为读锁和写锁,都属于悲观锁:读锁也叫共享锁,针对同一份数据,多个读操作可以同时进行,但是写操作不允许。写锁也叫排它锁,当写操作完成时,读写都不允许

从数据库的操作粒度上可以分为表锁和行锁InnoDB支持行锁,myISAM不支持行锁。

表锁每次操作锁住整张表。开销小,加锁快(直接锁住整张表);不会出现死锁;锁定粒度大,发生锁冲 突的概率最高,并发度最低;

行锁每次操作锁住一行数据。开销大,加锁慢(需要定位到那条数据);会出现死锁(两个事务分别操作AB两条数据,事务一先操作B,再操作A,事务二先操作A,再操作B,这个时候就可能出现死锁);锁定粒度最小,发生锁冲突的概率最低,并发度最高。

 

加表锁:lock table 1 read(write), 2 read/write;

查看表锁: show open tables;  结果中In_use字段为1,表示加了表锁

 

释放当前会话加的表锁unlock tables;

 

MyISAM在执行查询语句(SELECT),会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁InnoDB支持事务,因此它的锁和事务的隔离级别有一定关系。

 

2.数据库事务和隔离级别

2.1 事务

InnoDB支持事务而MyISAM是不支持事务的。

我们都知道事务具有四大属性——ACID

原子性Atomicity:事务看做是一个原子操作,因此其对数据的修改,要么全都执行成功,要么都不成功。

一致性Consistent:事务开始和结束时的数据都是一致的 。

隔离性Isolation:事务处理中的数据状态对外部是不可见的,反之也无法获取到其他事务处理中的数据状态。

持久性Durable:事务提交之后,对数据的修改时永久性的。

 

2.2并发事务带来的问题

更新丢失:当多个事务更新同一行数据时,因为隔离性的存在,彼此都不知道其他的事务的存在,就会导致最后一个提交的事务,覆盖了其他事务提交的数据。

脏读:事务A正在对一条记录修改,事务B读了A正在修改的数据。但是此时事务A并未提交。后续因为问题事务A可能回滚,那么事务B读到的数据就是无效的脏数据。同时也破坏了事务的一致性的要求。

不可能重复读:在一次事务中,多次执行同样的查询条件,获取到的结果不一致,也就是读到了其他事务的修改的数据。不符合事务的隔离性

幻读:和不可重复读不一样的是幻读是读取到了新增的数据。

正因为存在以上问题,所以就就需要数据库提供一定的机制来解决这些问题。

 

2.3事务隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交 可能 可能 可能
读已提交 不可能 可能 可能
可重复读 不可能 不可能 可能
串行化 不可能 不可能 不可能

 

 

 

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。 同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用 对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力,所以大多数时候我们都会采用的可重复读的事务隔离级别。

 

3.InnoDB的行锁

 

前面有说MyISAM会根据执行的操作对数据加表锁(读锁/写锁)。InnoDB有所不同,在非串行化的隔离级别下面select语句不会加锁,但是updateinsertdelete会根据条件加行锁。且行锁是加在索引上面的,如果这些语句没有走索引,那么也会加表锁。

如果条件是范围,那么该范围内的所有行,包括每行记录所在的间隙区间都会被加上锁,就算该行数据还未被插入也会被加锁,这就是所谓的间隙锁,间隙锁在可重复读隔离级别下面才会生效。

比如说A表现有的id1,2,3,4,5,10,20;那么间隙区间就有5-10,10-20,20-正无穷三个区间。我们执行update A set XXX= 'XXX' where id > 6 and id <16; 首先[6,16]这个范围的数据会被加锁,然6是落在5-10这个间隙区间的,这个区间的数据也会加锁,16是落在10-20这个间隙区间,这个区间的记录也会被加锁,所以执行上述sql(5,16]左开又闭,这个区间的数据都会被加锁。如果执行的update A set XXX= 'XXX' where id > 6 and id <21;那么(5,正无穷) 都会被加锁,所以这个点还需要注意一下。

因此我们应该:

1.尽可能让所有数据查找,修改都通过索引来完成,避免无索引行锁升级为表锁

2.尽可能减少条件范围,缩小锁的范围,尽量避免间隙锁

3.尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行

 

    原文作者:白露非霜
    原文地址: https://www.cnblogs.com/nijunyang/p/14956399.html
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。