共享锁和排他锁都属于行级锁,行级锁是 MySQL 中锁定粒度最细的一种锁,能大大减少数据库操作中的冲突,但加锁的开销也最大,加锁慢;会出现死锁,发生锁冲突的概率最低,并发度也最高。接下来将介绍共享锁和排他锁的相关概念,以及使用中一些需要注意的地方等。

1. 共享锁

共享锁也叫读锁,简称S锁,原理:一个事务获取了一个数据行的共享锁,其他事务能获得该行对应的共享锁,但不能获得排他锁,即一个事务在读取一个数据行的时候,其他事务也可以读,但不能对该数据行进行增删改。
使用语法如下:

1
SELECT .. LOCK IN SHARE IN MODE;

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

2. 排他锁

排他锁也叫写锁,简称X锁,原理:一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁(排他锁或者共享锁),即一个事务在读取一个数据行的时候,其他事务不能对该数据行进行增删改查。
使用语法如下:

1
SELECT .. LOCK FOR UPDATE;

使用排他锁之后,MySQL会对查询结果加上排他锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请排他锁,否则会被阻塞。

注意点:对于insert、update、delete,InnoDB会自动给涉及的数据加排他锁(X);对于一般的 Select 语句,InnoDB 不会加任何锁。

3. 意向锁

意向共享锁,简称IS,表示数据库准备对数据行加共享锁,那么此时 InnoDB 会先找到这张表,对该表加意向共享锁之后,再对数据行添加共享锁。
意向排他锁,简称IX,表示数据库准备对数据行加排他锁,那么此时 InnoDB 会先找到这张表,对该表加意向排他锁之后,再对数据行添加排他锁。

注意点:意向共享锁和意向排他锁都是系统自动添加和自动释放的,整个过程无需人工干预。
意向共享锁和意向排他锁是表级锁。

4. 相关注意事项

4.1 行级锁是基于索引来锁定指定行的,如果查询语句没有用到索引的话,会锁定整个表的,使用表级锁。
4.2 行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条 SQL 语句操作了主键索引,MySQL 就会锁定这条主键索引;如果一条SQL语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。 在UPDATE、DELETE操作时,MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking。
4.3 在 MySQL 中,MyISAM 中是不会产生死锁的,因为 MyISAM 总是一次性获得所需的全部锁,要么全部满足,要么全部等待。而在 InnoDB 中,锁是逐步获得的,就造成了死锁的可能。
4.4 一般出现死锁的情况是:当两个事务同时执行,一个锁定了主键索引,在等待其他相关索引;另一个锁定了非主键索引,在等待主键索引。这样就会发生死锁。发生死锁后,InnoDB 一般都可以检测到,并使一个事务释放锁回退,另一个获取锁完成事务。

5. 避免死锁的方法

5.1 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
5.2 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率。
5.3 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率。

最后更新: 2018年01月06日 19:08

原始链接: http://blog.minhow.com/2016/12/07/database/mysql-innodb-lock/

× 请我吃糖~
打赏二维码