MySQL锁机制

概述

  作为一名程序员(我是java开发)，锁是一个绕不开的话题。有读锁、写锁、排他锁、共享锁、红锁、可重入锁、自旋锁、公平锁、乐观锁、分段锁、偏向锁等等（其实有些是一个意思）。今天这里要说的是Mysql的锁机制(主要是innodb)，涉及到的主要是读锁、写锁、意向锁、自增锁。

表锁和行锁

1、介绍

  在对锁模式进行说明之前，先说明一下这个表锁和行锁。表锁是对整张数据表来进行加锁，一般是 DDL 处理时使用，由Mysql server提供；而行锁则是锁定某一行或者某几行，或者行与行之间的间隙，主要是由存储引擎来提供。

2、引擎说明

  mysql中常用的InnoDB引擎支持行锁和表锁，而 MyISAM 则只能使用 MySQL Server 提供的表锁。

• MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
• BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁
• InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

3、不同粒度的比较

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。最大程度的支持并发，同时也带来了最大的锁开销。在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。并且只在存储引擎层实现，而Mysql服务器层没有实现。行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。页级锁定主要是BerkeleyDB 存储引擎。

锁的说明

1、读锁

读锁，又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

2、写锁

写锁，又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。

排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

3、自增锁

AUTOINC锁又叫自增锁（一般简写成AI锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加AUTOINC表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTOINC 锁具有如下特点：

• AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增值一旦分配了就会+1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

4、意向锁

由于表锁和行锁虽然锁定范围不同，但是会相互冲突。所以当你要加表锁时，势必要先遍历该表的所有记录，判断是否加有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL 引入了意向锁，来检测表锁和行锁的冲突。

意向锁也是表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。当事务要在记录上加上读锁或写锁时，要首先在表上加上意向锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。

意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

InnoDB加锁

-- 创建表
create table user
(
    id   int not null auto_increment primary key,
    name varchar(255),
    age  int
);
INSERT INTO test.user (id, name, age) VALUES (1, 'A', 1);

意向锁

• 是 InnoDB 自动加的， 不需用户干预。
• 对于 UPDATE、 DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通 SELECT 语句，InnoDB不会加任何锁。事务可以通过以下语句显式给记录集加共享锁或排他锁：

-- 如果你这个地方不提交，那么这条数据在数据表还是旧数据，而且无法修改。【排他锁】（这里为了测试所开启使用了事务）
START TRANSACTION; -- 开始事务
update user set name='BB' where id=1;

COMMIT;-- 提交事务

-- 未提交之前，通过这个可以查看意向锁（mysql 8之后是这个语句，8之前的请自行百度）
SELECT * FROM performance_schema.data_locks;

共享锁（S）：

SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。

-- 如果你这个地方不提交，你可以用其他方式读取，但是无法修改。【共享锁】（这里为了测试所开启使用了事务）
START TRANSACTION; -- 开始事务
select * from user where id=1 LOCK IN SHARE MODE;

COMMIT;-- 提交事务

-- 重启线程执行共享查询操作是没有问题的

-- 但是如果你在上面未提交执行，重启线程执行会产生排他锁的语句，会导致锁等待。【如果你在同一个线程事务下操作，会导致死锁】
update user set name='FF' where id=1;

-- 通过这个可以查看到锁等待的数据（mysql 8之后是这个语句，8之前的请自行百度）
SELECT * FROM performance_schema.data_lock_waits;

排他锁（X)：

SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

-- 如果你这个地方不提交，事务下的锁内操作无法读取，并且其他语句无法修改。【排他锁】（这里为了测试所开启使用了事务）
START TRANSACTION; -- 开始事务
select * from user where id=1 FOR UPDATE ;

COMMIT;-- 提交事务

-- 重启一个线程执行，如果上面没有提交，这个共享锁无法添加查询到任何数据
select * from user where id=1 LOCK IN SHARE MODE;

-- 通过这个可以查看到锁等待的数据（mysql 8之后是这个语句，8之前的请自行百度）
SELECT * FROM performance_schema.data_lock_waits;

间隙锁：

1. 防止幻读，以满足相关隔离级别的要求；
2. 满足恢复和复制的需要。

死锁

-- 这个的执行会产生死锁
START TRANSACTION; -- 开始事务
select * from user where id=1 LOCK IN SHARE MODE;
select * from user where id=1 FOR UPDATE ;
COMMIT ;-- 提交事务

-- 查看最近一次死锁信息（mysql 8）
SHOW ENGINE INNODB STATUS; 

死锁产生：

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。

锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：

数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：

死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：

死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

InnoDB避免死锁：

为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。

在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。

改变事务隔离级别

如果出现死锁，可以用 SHOW ENGINE INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。