MySQL 锁机制

MySQL锁介绍

相对于其他的数据库而言，MySQL的锁机制比较简单，最显著的特点就是不同的存储引擎支持不同的锁机制。根据不同的存储引擎，MySQL中锁的特性可以大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：介于表级锁和行级锁之间（一般开发中不用）
元数据锁（metadata Lock 简称MDL）

元数据锁主要是面向DML和DDL之间的并发控制，如果对一张表做DML增删改查操作的同时，有一个线程在做DDL操作，不加控制的话，就会出现错误和异常。元数据锁不需要我们显式的加，系统默认会加。

元数据锁的原理

当做DML操作时，会申请一个MDL读锁
当做DDL操作时，会申请一个MDL写锁
读锁之间不互斥，读写和写写之间都互斥。

一、表锁

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

我们在编辑表，或者执行修改表的事情了语句的时候，一般都会给表加上表锁，可以避免一些不同步的事情出现，表锁分为两种，一种是读锁，一种是写锁。

我们可以手动给表加上这两种锁，语句是：

lock table 表名 read/write;

释放所有表的锁：

unlock tables;

查看加锁的表：

show open tables;

如图所示，in_use为1的就是加锁的表。

加读锁（共享锁）：

我们给表加上读锁会有什么效果呢？

我们加读锁的这个进程可以读加读锁的表，但是不能读其他的表。
加读锁的这个进程不能update加读锁的表。
其他进程可以读加读锁的表（因为是共享锁），也可以读其他表
其他进程update加读锁的表会一直处于等待锁的状态，直到锁被释放后才会update成功。

加写锁（独占锁）：

加锁进程可以对加锁的表做任何操作
其他进程则不能操作加锁的表，需等待锁释放

总结：

读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。（特别注意进程）

分析：

show status like 'table%';

输入上述命令，可得：

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1 。
Table_locks_waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况。

例子：

在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁

在执行更新操作（UPDATE,DELETE,INSERT）前，会自动添加写锁

但是方便为了本地的测试，可以使用一下sql模拟

加读锁：

session1 会话

-- 给表添加读锁
lock table t_emp read;
 
-- 读本表 成功
select * from t_emp;
 
-- 写本表、 读写其他表 阻塞
select * from t_dept;
 
-- 释放锁
unlock tables;

session2 会话

-- 写锁定表 阻塞
update t_emp set name = 'qweqwe' where id = 1;

注意：同一个表在sql 语句中出现多次，就要通过sql语句中的别名锁锁定多少次，否则也会出错

LOCK table t_emp read;
-- 查询报错
select t.* from t_emp t; 
 
LOCK table t_emp read , t_emp t read;
-- 查询正确
select t.* from t_emp t;

并发插入

MyISAM 储存引擎有一个系统变量 concurrent_insert , 专门用以控制并发插入的行为，其值分别可以为0、1和2.

设置为0时，不允许并发插入
设置为1，当MyISAM 表中没有空洞（即表中间没有删除的行），MyISAM 允许在一个进程读表的同时，另一个进程表尾插入记录。这也是mysql的默认设置。
设置为2，无论有没有空洞，都允许在表尾并发插入。

-- 表中间没有删除的行
-- session 1 会话
-- local 加在 read 后面才可以执行并发操作
LOCK table t_order read local;

-- session 2 会话
-- insert 语句插入成功
INSERT INTO `test`.`t_order`(`id`, `name`, `age`) VALUES (null, '2', 3);

如果表中间之前删除过，那么需要执行以下语句

OPTIMIZE TABLE t_order;

执行后，再按上述操作，就可以在session2 会话中执行insert语句。

二、行锁特点：

InnoDB行锁是通过给索引上的索引项加锁来实现的， InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行为：

当我们对一行进行更新但是不提交的时候，其他进程也对该行进行更新则需要进行等待，这就是行锁。
如果我们对一行进行更新，其他进程更新别的行是不会受影响的。

加锁的方式：

自动加锁。对于UPDATe、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

共享锁：select * from tableName where … + lock in share more

排他锁：select * from tableName where … + for update

例子

set autocommit=0,
当前session禁用自动提交事物，自此句执行以后，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

1、InnoDB存储引擎的表在不使用索引时使用表锁例子

session1	session2
set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select from tab_no_index where id = 1 ; +——+——+ \| id \| name \| +——+——+ \| 1 \| 1 \| +——+——+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select from tab_no_index where id = 2 ; +——+——+ \| id \| name \| +——+——+ \| 2 \| 2 \| +——+——+ 1 row in set (0.00 sec)
mysql> select from tab_no_index where id = 1 for update; +——+——+ \| id \| name \| +——+——+ \| 1 \| 1 \| +——+——+ 1 row in set (0.00 sec)
	mysql> select from tab_no_index where id = 2 for update; 等待

在上如表中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。

2、有了索引以后，在对索引字段查询时，使用的就是行级锁

添加索引：alter table tab_no_index add index ind_tab_no_index_id (id);

session1	session2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select from tab_no_index where id = 1 ; +——+——+ \| id \| name \| +——+——+ \| 1 \| 1 \| +——+——+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select from tab_no_index where id = 2 ; +——+——+ \| id \| name \| +——+——+ \| 2 \| 2 \| +——+——+ 1 row in set (0.00 sec)
mysql> select from tab_no_index where id = 1 for update; +——+——+ \| id \| name \| +——+——+ \| 1 \| 1 \| +——+——+ 1 row in set (0.00 sec)
	mysql> select from tab_no_index where id = 2 for update; +——+——+ \| id \| name \| +——+——+ \| 2 \| 2 \| +——+——+

从上表可知，由于使用了行级锁，所以对不同行使用排它锁相互不影响。

3、由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
表tab_with_index的id字段有索引，name字段没有索引。

InnoDB存储引擎使用相同索引键的阻塞例子

session1	session2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select from tab_with_index where id = 1 and name = ‘1’ for update; +——+——+ \| id \| name \| +——+——+ \| 1 \| 1 \| +——+——+ 1 row in set (0.00 sec)
	虽然session_2访问的是和session_1不同的记录，但是因为使用了相同的索引，所以需要等待锁： mysql> select from tab_with_index where id = 1 and name = ‘4’ for update; 等待

4、当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
表tab_with_index的id字段有普通索引，name字段有普通索引：
InnoDB存储引擎的表使用不同索引的阻塞例子

session1	session2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select from tab_with_index where id = 1 for update; +——+——+ \| id \| name \| +——+——+ \| 1 \| 1 \| \| 1 \| 4 \| +——+——+ 2 rows in set (0.00 sec)
	Session_2使用name的索引访问记录，因为记录没有被索引，所以可以获得锁： mysql> select from tab_with_index where name = ‘2’ for update; +——+——+ \| id \| name \| +——+——+ \| 2 \| 2 \| +——+——+ 1 row in set (0.00 sec)
	由于访问的记录已经被session_1锁定，所以等待获得锁。： mysql> select * from tab_with_index where name = ‘4’ for update;

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
Select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！
下面例子中，假如emp表中只有101条记录，其empid的值分别是1,2,……,100,101。
InnoDB存储引擎的间隙锁阻塞例子

session1	session1
mysql> select @@tx_isolation; +—————–+ \| @@tx_isolation \| +—————–+ \| REPEATABLE-READ \| +—————–+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +—————–+ \| @@tx_isolation \| +—————–+ \| REPEATABLE-READ \| +—————–+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
当前session对不存在的记录加for update的锁： mysql> select * from emp where empid = 102 for update; Empty set (0.00 sec)
	这时，如果其他session插入empid为102的记录（注意：这条记录并不存在），也会出现锁等待： mysql>insert into emp(empid,…) values(102,…); 阻塞等待
Session_1 执行rollback： mysql> rollback; Query OK, 0 rows affected (13.04 sec)
	由于其他session_1回退后释放了Next-Key锁，当前session可以获得锁并成功插入记录： mysql>insert into emp(empid,…) values(102,…); Query OK, 1 row affected (13.35 sec)

5、即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

三、意向锁的说明

加意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行。

意向锁有两种：

意向共享锁（IS）表示事务意图在表中的单个行上设置共享锁。
意向排他锁（IX）表明事务意图在表中的单个行上设置独占锁。

我们先了解一下意向锁是在什么时候使用的。

在一个事务对一张表的某行添加S锁之前，它必须对该表获取一个IS锁或者优先级更高的锁。
在一个事务对一张表的某行添加X锁之前，它必须对该表获取一个IX锁。

那么为什么要这么做呢？这么做有什么好处呢？

这里就需要介绍一下表锁了。

如果我们用select * from student for update,是会student表加上X锁的。

数据库里面是同时允许锁表，或者锁行的。

如果事务A需要修改某一行数据，则他会给该行加X锁。

这时事务B想申请整个表的X锁做某些操作。他能否申请成功呢？不能，因为申请成功则代表事务B可以对任意行做读写。显然这与事务A冲突了。

那这时数据库应该怎么判断呢？

可行方案就是，逐行判断是否加了X锁，如果都没加，就代表可以锁表，如果其中某一行加上了X锁，那么就不能整表加锁，不过，显然这样效率实在是太低了......

这个时候来看意向锁，就可以看出他发挥了重要的重要：

事务A想对某行上X锁之前，必须要获得到表的IX锁，现在没有其他事务使用IX锁，所以事务A获取成功。

事务A——获得IX锁——对某行上X锁

这个时候事务B想对这个表上X锁，发现IX表已经被拿走了，证明目前有其他事务正在修改该表的某行或者多行，此时事务B被阻塞......

IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突

意向锁之间是相互兼容的：

-	意向共享锁（IS）	意向排他锁（IX）
意向共享锁（IS）	兼容	兼容
意向排他锁（IX）	兼容	兼容

为什么都是兼容呢？

事务A加了表的IX锁，或者IS锁，只代表事务A已锁定一行或者将要锁定一行。事务B当然也可以锁定其他的行，所以事务B肯定也是可以获得表的IS锁或者IX锁的。

-	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	兼容	冲突
排他锁（X）	冲突	冲突

举个例子：

事务A已经获得了IS锁，想要读取某行数据，事务B想要获得表的S锁，可以获得成功，因为读是兼容的
事务B获得了表的IX锁，想要修改某行数据，事务B想要获得表的X锁，但是由于IX锁已被获取走，证明有其他事务正在修改某行数据，所以事务B获得失败，只能被组塞住...

总结：

InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与共享锁 / 排他锁互斥。
IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离的要求

session1	session2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select from tab_no_index where id = 1 ; +——+——+ \| id \| name \| +——+——+ \| 1 \| 1 \| +——+——+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select from tab_no_index where id = 2 ; +——+——+ \| id \| name \| +——+——+ \| 2 \| 2 \| +——+——+ 1 row in set (0.00 sec)
mysql> select from tab_no_index where id = 1 for update; +——+——+ \| id \| name \| +——+——+ \| 1 \| 1 \| +——+——+ 1 row in set (0.00 sec)
	mysql> select from tab_no_index where id = 2 for update; +——+——+ \| id \| name \| +——+——+ \| 2 \| 2 \| +——+——+

MySQL 锁机制

Java相关栏目本月热门文章