MySQL中锁的机制

2019-12-12  本文已影响0人  就这些吗

先放结论:

1.如果没有用到索引,所有的增删改查都会造成表锁。
2.查询有两种方式(这里我们假设查询都用到了索引),一种是普通(快照)读,利用了mvcc机制不需要加锁,一种是加条件的查询,在后面加LOCK IN SHARE MODE或者FOR UPDATE,前者给这一行加了共享锁,后者加了排它锁。
3.增删改都会导致行锁,而且是排它锁。(这里我们假设都用到了索引)
4.事务中获取锁的时间是方法执行的时候,而释放锁的时间不是方法执行完毕,而是整个事务执行完毕。
5.行锁的本质是锁住索引,主键是一定会锁的(因为其他索引会再去查主键索引,而后才能得到要查的所有数据),如果查询中用到了其他的索引,也会锁住,但是不是同时锁住的,会有时间差,这也是导致死锁的原因。

1 共享锁(Share Lock)

共享锁又称读锁,是读取操作创建的锁。其他用户可以并发读取数据,但任何事务都不能对数据进行修改(获取数据上的排他锁),直到已释放所有共享锁。

如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加排他锁。获准共享锁的事务只能读数据,不能修改数据。

用法

SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE,MySQL 就会对查询结果中的每行都加共享锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请共享锁,否则会被阻塞。其他线程也可以读取使用了共享锁的表,而且这些线程读取的是同一个版本的数据。

2 排他锁(Exclusive Lock)

排他锁又称写锁、独占锁,如果事务T对数据A加上排他锁后,则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据,又能修改数据。
拿MySql的InnoDB引擎来说,对于insert、update、delete等操作。会自动给涉及的数据加排他锁;

查询语句的用法

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE,MySQL 就会对查询结果中的每行都加排他锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请排他锁,否则会被阻塞。

3.行锁中死锁的形成:

行锁的概念:

行级锁并不是直接锁记录,而是锁索引,
如果一条SQL语句用到了主键索引,mysql会锁住主键索引;
如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。
注意是有先后过程
当然如果查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁。

死锁的概念:

死锁产生的根本原因是两个以上的进程都要求对方释放资源,以至于进程都一直等待。在代码上是因为两个或者以上的事务都要求另一个释放资源。
死锁产生的四个必要条件:互斥、循环等待、请求保持、不可剥夺,缺一不可,相对应的只要破坏其中一种条件死锁就不会产生。

举个栗子:

id 主键索引
name index 索引
age 普通字段

update mk_user set name ='1' where name='idis12';

update mk_user set name='12' where id=12;

例如上面两条语句 第一条语句会优先使用name索引,因为name不是主键索引,还会用到主键索引
  第二条语句是首先使用主键索引,再使用name索引 如果两条语句同时执行,第一条语句执行了name索引等待第二条释放主键索引,第二条执行了主键索引等待第一条的name索引,这样就造成了死锁。

解决方法:改造第一条语句 使其根据主键值进行更新(因为普通的select不会加锁)

//改造后

update mk_user set name='1' where id=(select id from mk_user where name='idis12' );

2020.3.3补充:间隙所
幻读是什么,幻读有什么问题

一分钟了解Mysql的间隙锁——《深究Mysql锁》
间隙锁的出现是为了防止幻读。
如果查询行没有设置唯一的索引,或者直接没加索引,都会导致间隙锁

image.png
比如A<C<B,间隙锁会锁住[A,B]的行

间隙锁是在可重复读隔离级别下才会生效的。所以,你如果把隔离级别设置为读提交的话,就没有间隙锁了。但同时,你要解决可能出现的数据和日志不一致问题,需要把 binlog 格式设置为 row。这,也是现在不少公司使用的配置组合。

select * from t where c=5 for update,
回来老师的问题:
在 Read Committed 隔离级别下,会锁上聚簇索引中的所有记录;
在 Repeatable Read 隔离级别下,会锁上聚簇索引中的所有记录,并且会锁上聚簇索引内的所有 GAP;
在上面两个隔离级别的情况下,如果设置了 innodb_locks_unsafe_for_binlog 开启 semi-consistent read 的话,对于不满足查询条件的记录,MySQL 会提前放锁,不过加锁的过程是不可避免的。

跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

上一篇下一篇

猜你喜欢

热点阅读