锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。




mysql 删除锁住了 mysql删除加锁_数据


本文是基于InnoDB存储引擎

一、锁的重要性

在数据库中，除传统计算资源（CPU、RAM、IO等）的争抢，数据也是一种供多用户共享的资源。一张图彻底搞懂 MySQL 的锁机制在数据库中，除传统计算资源（CPU、RAM、IO等）的争抢，数据也是一种供多用户共享的资源。

如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。

锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。

缺点：加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否已解除、释放锁等 ，都会增加系统的开销。

二、锁的类型

1、表锁

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

2、行级锁

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

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

3、页面锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

三、行锁

1、共享锁（S）：又称读锁，允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁，共享锁的目的是提高读读并发。

加共享锁的写法： lock in share mode

例如： select * from 表 where 条件 lock in share mode；

select lock in share mode ：in share mode 子句的作用就是将查找到的数据加上一个 share 锁，这个就是表示其他的事务只能对这些数据进行简单的select 操作，并不能够进行 DML 操作。select *** lock in share mode 使用场景：为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。

2、排他锁（X）：又称写锁，允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁，排他锁的目的是为了保证数据的一致性。

对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；

加排它锁的写法： for update

例如： select * from 表 where 条件 for update；

select for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

select lock in share mode 语句是一个给查找的数据上一个共享锁（S 锁）的功能，它允许其他的事务也对该数据上S锁，但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。


mysql 删除锁住了 mysql删除加锁_数据_02


3、意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

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

意向锁是 InnoDB 自动加的， 不需用户干预。

IS、S、IX、X锁之间的兼容性比较：


mysql 删除锁住了 mysql删除加锁_MySQL_03


4、行锁实现方式

  • InnoDB 行锁是通过给索引上的索引项加锁来实现的，InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
  • 不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
  • 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
    别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。
  • 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

5、间隙锁

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

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

InnoDB使用间隙锁的目的：

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

MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制（复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复）有以下特点：

一是 MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。

二是 MySQL 的 Binlog 是按照事务提交的先后顺序记录的， 恢复也是按这个顺序进行的。

由此可见，MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

四、获取 InnoDB 锁争用情况：

1、查看行锁


mysql> show status like 'innodb_row_lock%'; 
+-------------------------------+-------+ 
| Variable_name | Value | 
+-------------------------------+-------+ 
| InnoDB_row_lock_current_waits | 0 | 
| InnoDB_row_lock_time | 0 | 
| InnoDB_row_lock_time_avg | 0 | 
| InnoDB_row_lock_time_max | 0 | 
| InnoDB_row_lock_waits | 0 | 
+-------------------------------+-------+ 
5 rows in set (0.01 sec)

1. innodb_row_lock_current_waits //当前正在等待锁定的数量
2. innodb_row_lock_time //从系统启动到现在锁定总时间长度
3. innodb_row_lock_time_avg //每次等待所花平均时间
4. innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
5. innodb_row_lock_waits //系统启动后到现在总共等待的次数


2、查看表锁


show status like 'table%';


mysql 删除锁住了 mysql删除加锁_MySQL_04


1. table_locks_waited
出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），此值高说明存在着较严重的表级锁争用情况
2. table_locks_immediate
产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加1

优化建议

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能较少检索条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可能低级别事务隔离

3、查看锁


select * from information_schema.INNODB_LOCKS;


mysql 删除锁住了 mysql删除加锁_死锁_05


同一个数据有了 2 个锁其中一个是 X（写锁） ， 另外一个是 S（读锁）

在5.7查看具体锁


select * from sys.innodb_lock_waits


在5.6查看具体锁


SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread
FROM
information_schema.innodb_lock_waits w
INNER JOIN
information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN
information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;


mysql 删除锁住了 mysql删除加锁_死锁_06


五、乐观锁和悲观锁

1、乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。

乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

  • 数据表中的实现

用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。


//step1: 查询出商品信息 
select (quantity,version) from items where id=100; 
//step2: 根据商品信息生成订单 
insert into orders(id,item_id) values(null,100); 
//step3: 修改商品的库存 update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version};


  • 锁的粒度

用版本号每次只能有一个成功，比较影响性能，可以减少锁的粒度


// 仍挑选以库存数作为乐观锁
//step1: 查询出商品信息
select (inventory) from items where id=100;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update items set inventory=inventory-1 where id=100 and inventory-1>0;


2.悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

select...for update是MySQL提供的实现悲观锁的方式，在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

悲观锁

乐观锁

概念

定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

实现机制

锁住记录，其他事务不能查询不能更新

更新数据时候检查时间戳或者版本号是否符合

实现

数据库

开发人

六、死锁

  • 死锁产生：
  • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
  • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
  • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。


mysql 删除锁住了 mysql删除加锁_解除主键锁_07


  • 检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
  • 死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
  • 外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
  • 死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

避免死锁

1. 加锁顺序一致，尽可能一次性锁定所需的数据行

2. 尽量基于primary（主键）或unique key更新数据

3. 单次操作数据量不宜过多，涉及表尽量少

4. 减少表上索引，减少锁定资源

5. 尽量使用较低的隔离级别

6. 尽量使用相同条件访问数据，这样可以避免间隙锁对并发的插入影响

7. 精心设计索引，尽量使用索引访问数据

8. 借助相关工具：pt-deadlock-logger