你真的了解MySQL的锁么

前言

   锁是计算机协调多个进程或者线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；

锁冲突也是影响数据库并发访问性能的一一个重要因素。锁是在MySQL在服务器层和存储层的并发控制。

MySQL的锁机制

共享锁和排它锁

• 共享锁（读锁 Table Read Lock）其他的事务可以读，但是不能写
• 怕它锁（写锁 Table Write Lock）其他的事务不能读取，也不能写

锁的粒度

MySQL不同的存储引擎支持不同的锁机制，所有的存储引擎都实现了自己的锁机制。服务器存储层，完全不用了解存储引擎中的实现。

• MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)
• InnoDB 存储引擎支持行锁，（row-level locking）,同事支持表锁
• 页锁（page-level locking ）在一些特殊的引擎也支持，用的场景较少（BDB引擎支持页锁）。

默认的情况下行锁跟表锁都是自动获取的，并不需要显示的命令执行。除非在有些情况下，我们需要明确的进行锁表，或者进行事务控制，以确保整个事务的完整性，这样就需要使用事务控制语句和锁定语句来进行控制。

几种锁对比

• 表锁：开销小，加锁快，不会出现死锁；锁定的粒度大，发生锁冲突的概率高，并发度低
• 行锁：开销大，加锁慢，会出现死锁，锁定的粒度小，发生冲突的概率低，并发度高
• 页锁：开销和加锁介于表锁和行锁之间；会出现死锁；锁定的粒度介于表锁跟行数之间，并发度一般。

一般来说，表锁适合查询为主，少量的更新。 行锁适合具有大量按照索引条件并发更新少量的不同的数据，同时又有并发查询的应用。

MyISAM表锁

MyISAM存储引起只支持表锁，是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务的完整性和并发性的要求不断提高，MySQL才支持基于事务的存储引擎，事实上InnoDB是一个单独的公司，后被Oracle收购。

查看锁的争用情况

table_locks_waited 和 table_locks_immediate

如果table_locks_waited的值，比较高，则说明存在着比较高的表级锁的争用情况。

MyISAM在执行查询语句（select），都会自动的给涉及到表加读锁，只执行更新操作（update，delete，insert）前，会自动的给涉及到表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用lock table

命令来给MyISAM表来显示加锁。在自动加锁的s情况下，MyISAM总是一次获得SQL语句所需要的全部锁，这也正是MyISAM表不会出现死锁的原因。MyISAM存储引擎支持并发插入，以减少表之间的读和写操作之间的争用。

InnoDB行级锁和表级锁

InnoDB锁模式

• 共享锁(S): 允许一个事务读取一行，阻止其他事务获得相同数据的排他锁
• 排他锁(X):允许获得排他锁的事务更新数据，阻止其他的事务获得相同数据集的共享锁和排他写锁。
• 意向共享锁(IS):事务打算给数据行加共享锁，事务给一个数据行加共享锁前必须获得表的IS锁。（这个操作是DBA 无法干预的）
• 意向排他锁(IX): 事务打算给数据行加怕他锁，必须先获得该表的一个意向排他锁,(IX锁)。（这个操作是DBA 无法干预的）

InnoDB 加锁的方法

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

• 对于update delete insert 语句，InnoDB会自动给涉及的数据集加排他锁（X）；

• 对于普通的select 语句，InnoDB不会加任何锁
  事务可以通过一下语句显示的给记录集加共享锁或者排他锁：

  • 共享锁（S）: select * from table_name where … lock in share mode,其他的session仍然可以查询记录，并且也可以对该记录加 share mode的共享锁，但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X）: select * from table_name Where … FOR UPDATE.其他session可以查询该记录，但是不能对该记录加共享锁或拍他锁，而是等待获得锁。

• 隐式锁定：

• InnoDB 在事务的执行过程中，使用两阶段提交协议：随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁。锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

• 显示锁定
  select * from table_name where … lock in share mode – 共享锁
  – 这个的意思就是 我对行数据加了share锁，其他人能够查看，但是不能够进行DML(insert update delete)操作。 使用的场景：自己查询到的数据不允许别人来修改，并且自己也不一定能够修改(有可能其他的session 也使用了 in share mode 的方式加了S锁) –

  select * from table_name Where ... FOR UPDATE --排他锁
  -- 这个锁的意思其实相当于update语句，如果事务没有及时的commit或者rollback会造成其他的事务长时间的等待。从而影响到数据的并发使用效率。
  

InnoDB行锁的实现方式

• InnoDB行锁是通过给索引上的索引项加锁来实现的，Oracle 是通过给数据块中的对应数据行来加锁进行实现。InnoDB这种行锁设计实现意味着：只有通过索引条件检索数据，才能使用行锁，否则就会使用表锁
• 无论是使用主键索引、唯一索引、普通索引，InnoDB都会使用行锁来对数据进行加锁。
• 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录，但是如果是使用相同行的索引键，是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。

InnoDB间隙锁

范围检索的时候,InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内并不存在的记录，  叫做“间隙（GAP）“,InnoDB 也会对这个间隙加锁，这个锁叫做临建锁(Next-key lock).  

显然在范围检索的时候，这种加锁机制会阻塞符合条件范围键值的并发插入，会造成严重的等待。所以在实际的应用开发中，尤其是并发插入的时候，我们应该尽量的优化业务逻辑，使用等值条件来访问更新数据，避免使用范围条件。

间隙锁的作用：

• 防止幻读,满足隔离级别的要求
• 满足恢复和复制的需求。

MySQL通过BINGLOG录入执行成功的insert update delete 等DML语句.并且实现了数据库的主从复制和数据恢复。

• MySQL 的恢复实际上是执行了BINLOG中的SQL语句
• MySQL 的BINGLOG 是按照事务提交的先后顺序记录的，恢复也是按照这个顺序执行的。也就是说：在一个事务未提交前，其他的并发事务不能满足插入满足其锁定条件的任何记录，也就是不允许出现幻读。

Record Lock & Gap Lock & Next-key Lock

• 记录锁(Record Lock)：
  当我们对于唯一性的 索引(包括唯一索引和主键索引)使用等值查询，
  精准匹配到一条记录的时候，这个时候使用的就 是记录锁，比如where id=1

• 间隙锁(Gap Lock)：
  当我们查询的记录不存在，没有命中任何一个 record，无论是用等值 查询还是范围查询的时候，它使用的都是间隙锁。
  (注意：间隙锁主要是阻塞插入 insert。相同的间隙锁之间不冲突，Gap Lock只在RR事务隔离级别才能使用)

• 临键锁(Next-key Lock)：
  当我们使用了范围查询，不仅仅命中了Record记录，还包含了Gap间隙，在这种情况下我们使用的就是临键锁
  (它是MySQL里面默认的行锁算法，相当于记录锁加上间隙锁。)

• 锁的退化：
  1.唯一性索引等值查询匹配到一条记录的时候， 退化成记录锁。
  2.没有匹配到任何记录的时候，退化成间隙锁

• 总结： Record Lock 、Gap Lock 、Next-key Lock 都只是锁的一种算法

不同隔离级别下一致性读跟锁的差异

锁和多版本数据MVCC是InnoDB实现一致性读和ISO/ANSI SQL92隔离级别的手段。因此在不同的隔离级别下，InnoDB处理SQL时候，采用的一致性读策略和加锁的策略是不一样的。

• RU（Read Uncommited）：

  • 不加锁

• RC (Read Commited)：

  • 普通的快照读 select mvcc实现
  • 加锁的 select 都使用记录锁，因为基本不用Gap Lock
  • 外键约束检查以及重复键检查时会使用间隙锁Gap Lock

• RR(Repeatable Read):

  • 普通的select都是快照读，使用MVCC实现
  • 加锁的select以及更新操作等语句使用当前读底层使用记录锁、间隙锁、临键锁

• Serializable：

  • 所有的select语句都会被隐式的转化为in share mode,会和update、delete等操作互斥

• RC跟RR的幻读问题：

  • RC：每次读取，都会创建一个新的read view。这样就能读取到其他事务已经COMMIT的内容
  • RR：ReadView是在first touch read时创建的，也就是执行事务中的第一条SELECT语句的瞬间，后续所有的SELECT都是复用这个
  • read view，所以能保证每次读取的一致性(可重复读的语义)
  • 总结：所以对于InnoDB来说，RR虽然比RC隔离级别高，但是开销反而相对少
  • 因此在我们的应用当中，应该尽量使用较低的隔离级别，减少锁的争用效率，实际上通过优化事务的逻辑，大部分应用使用 RC的隔离级别就足够了。针对确实需要更高隔离级别的事务，可以通过执行程序中执行set session transaction isolation level repeatable read 或者 set session transaction isolation level serializable 动态改变隔离级别的方式满足需求。

• ReadView

  • RU: 直接读取最新版本最新记录
  • RR：每个事务开始的时候会将当前系统中的所有活跃事务拷贝到一个列表生成一个ReadView
  • RC：每个语句开始的时候，会将当前系统中的所有活跃事务拷贝到一个列表生成一个ReadView

InnoDB行锁竞争分析

• Innodb_row_lock_current_waits 当前等待的锁的数量
• Innodb_row_lock_time 从系统启动到现在锁定的总时间长度，单位ms；
• Innodb_row_lock_time_avg每次等待所花平均时间
• Innodb_row_lock_time_max从系统启动到现在等待最长的一次所花的时间；
• Innodb_row_lock_waits从系统启动到现在总共等待的次数。

LOCK TABLES & UNLOCK TABLES

• lock tables 可以锁定用于当前线程的表。如果表被其他的线程锁定，则当前线程会等待，直到可以获取所有的锁定为止。
• unlock tables 可以释放当前线程获得任何锁定，当前线程执行另一个lock tables,或者是与服务器连接被关闭的时候，所有当前线程锁定的表被隐含的解锁。
  Lock tables orders read local, order_detail read local;
  Select sum(total) from orders;
  Select sum(subtotal) from order_detail;
  Unlock tables;
  – 假设我们要统计订单表order 和订单明细order_detail 数据是否相等，可以这样进行操作。加入local的选项，作用是为了当持有表锁的同时，其他的用户可以并发的插入。

什么是死锁，死锁怎么解决

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

InnDB 引擎采用的是 wait for graph 等待图的方法自动检测死锁，如果发现死锁会自动回滚一个事务。

• 不同程序并发存储多个表，尽量约定以相同的顺序访问表，可以大大的降低死锁的机会。
• 在同一个事务中，尽可能做到一次锁定所需要的资源，减少死锁的产生。
• 对于容易产生死锁的业务部门，可以尝试升级锁定的颗粒度，通过表级锁定来减少死锁产生的概率。

其他方案：在业务不好处理的情况下，建议采用分布式锁或者乐观锁来解决。

乐观锁&悲观锁

悲观锁： 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式使用数据库中的锁机制。

Java synchronized 就是属于悲观锁的一种试下，每次线程要修改数据的时候都要先获得锁，保障同一时刻只能有一个线程能够操作数据，其他的线程会被block。

select * from  table for update -- 悲观锁  使用场景，某些支付-账务场景会使用到悲观锁，来严格控制事务

乐观锁：假设不会发生并发冲突，只是在提交操作的时候检查是否违反数据的完整性。update的时候将事务锁起来，通过version的范式进行锁定。实现的方式一般使用版本号或者CAS算法实现。

在Java 中 Atomic 包就是乐观锁的一种实现，AtomicInteger通过CAS(Compare and set) 实现操作线程安全的自增。

update t_stock set stock = stock -1 where stock >0 ;-- rc 级别下就能防止超卖，这种就是乐观锁的实现其实也就是CAS算法

update table set value = newValue ,version = versionValue +1  where version = versionValue; -- 采用版本号来控制 

一些使用锁的优化建议

• 尽量使用较低的隔离级别；
• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
• 选择合理的事务大小，小事务发生锁冲突的几率也更小
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 除非必须，查询时不要显示加锁。 MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能