• MySQL数据库锁（三）

    2017-04-09 作者：SleepSir
    暂无评论

    InnoDB锁阻塞

    因为不同锁之间的兼容性关系，在有些时候一个事务中的锁要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。这里需要明确的是，阻塞不是死锁，并不一定是坏事，它的存在意义是为了确保事务可以并发且正常的运行。

    在InnoDB存储引擎中，参数Innodb_lock_wait_timeout用来控制锁等待的时间。

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-09-%e4%b8%8b%e5%8d%889-36-32

    当一个事务锁等待超过这个时间以后，会产生如下错误：

    当这个错误报出后，当前的这个SQL语句将被回滚（是SQL语句，不是事务），如果想在这种时候让事务也回滚的话可以设置下面这个参数。

    Innodb_rollback_on_timeout用来设定是否在等待超时时对事物进行回滚操作（默认是OFF，不回滚）。

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-09-%e4%b8%8b%e5%8d%889-49-10

    我们来实践一下：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-09-%e4%b8%8b%e5%8d%8810-11-13

    注：我这张图片做的真的好丑啊！！！！

    如上，即使我在插入199时锁等待超时了，但是整个事务并没有回滚，在事务2提交之后，我依旧可以从事务1中读到id为2980的值。也就是说，session在锁等待超时后虽然抛出了异常，但是既没有主动commit 也没有主动rollback。 而这是十分危险的状态，因此用户必须判断是否需要commit或者rollback，之后再做下一步的操作。

    这里可能会有疑惑，MVCC的作用不就是读写不冲突吗 ？ 为什么我在两个事务中的读写还互相阻塞呢 ？

    MVCC 虽然可以做到读写不冲突，但是MVCC中所谓的“读写”中的“读”指的是 快照读 ，“写”指的是当前读。 上面的例子中是当前读和当前读，相当于两个写操作，当然还是冲突的。

    InnoDB 死锁

    死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。 若无外力作用，则事务将无法推进下去。我们随便举两个常见的例子:

    1. 这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况

    session1 session2
    begin begin
    # 这里两边事务都插入数据是为了方便观察一会有死锁后任何一个事务回滚后是否会将这些insert一起回滚，还是只回滚当前SQL 。

    mysql> insert into t5 (name) values  (“11111”),(“22222”),(“33333”) ;
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    		 mysql> insert into t3 (name) values (“puppet”)  ;
    Query OK, 1 row affected (0.00 sec)
    mysql> update t5 set name = “d1” where id = 1 ;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0    		 mysql> update t3 set name = “ansible” where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    # 更新时阻塞住了

    mysql> update t3 set name = “salt” where id = 1;
    # 同时这边的阻塞也通了

    Query OK, 1 row affected (9.41 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    		 # 这边刚执行这条SQL，就直接报死锁发生了，本事务回滚。

    mysql>  update t5 set name = “d2” where id = 1 ;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
    mysql>
    commit commit

    从上面的测试中我们得到如下结果：

    • 死锁发生时，立刻就被MySQL发现并选择其中一个事务回滚了，并没有感觉阻塞的过程。
    • 和阻塞的回滚不一样的是，从t3表中并没有查到事务2的插入，所以是将事务2整个事务回滚掉了。

    2. 相同表记录的行锁冲突，两个job在执行数据批量更新时，session 1 处理的的id先后顺序与 session 2处理的id先后顺序顺序相反相交，就可能造成死锁。

    session1 session2
    begin begin
    mysql> update t3 set name = “000000” where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0    		 mysql> update t3 set name = “99999” where id = 2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    # 阻塞了

    mysql> update t3 set name = “888888” where id = 2;
    # 这边立马就通过了
    Query OK, 1 row affected (9.37 sec)
    Rows matched: 1  Changed: 1  Warnings: 0    		 # 执行后立马报死锁，失败回滚

    mysql> update t3 set name = “77777” where id = 1;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
    mysql>
    mysql> commit ;
    Query OK, 0 rows affected (0.01 sec)

    那看上面这个情况，感觉死锁的场景还是很有可能的啊，那么怎么去避免死锁呢 ？

    可能这时候你会想到，如果只要事务等待了就把它回滚，并且让事务重新开始不就完了吗 ？ 这或许是一种解决办法，但是风险很大，因为在线上环境中，这可能导致并发性能的下降，甚至到某些情况下任何一个事务都不能进行，这样的话造成的后果比死锁还严重，得不偿失了。

    解决死锁问题最简单的一种办法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就可以继续进行了。 在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来设置超时的时间。

    超时机制虽然简单，但是它遵循的FIFO（先入先出）的回滚原则，这时候就可能会有问题，因为倘若先超时的那个事务所占权重比较大，回滚代价也比较高，那么其实如果回滚其他的可能更划算些。

    这点MySQL也想到了，因此，除了超时机制，当前数据库都还普遍采用 wait-for graph（等待图）的方式来进行死锁检测。 较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用这种方式。这也是我们上面的死锁为什么刚发生就立刻回滚的原因。

    wait-for graph 要求数据库保存以下两种信息：

    • 锁的信息链表
    • 事务等待链表

    它根据这两个列表主动的去发现是否存在回环来确定死锁的存在。在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB会选择回滚undo量最小的事务。

    那么到这里，大家可能会有疑问，死锁这么可怕，它发生的概率高吗 ？ 都和哪些因素有关 ？

    在这里我们举个例子：假设当前数据库中共有100（n）个并发线程在执行，而且每个线程都是在执行同样的一个事务，这个事务由10 (r)个操作组成，每个操作从10000 (R )行数据里面随机操作一行数据，并占用该行的锁。每个事务在执行完最后一个操作后释放锁占用的所有锁资源。

    在上述模型下：

    • 事务发生锁等待的概率为： 1 / 2  (二分之一)
    • 一个事务发生死锁的概率为：  1 / 400 (四百分之一)
    • 这个系统中有可能发生死锁的概率为： 1 / 4  (四分之一)

    可能觉得有点难以接受？  我这个系统中竟然有四分之一的概率发生死锁。大兄弟，我们这个案例可是开了一百个并发执行同样的事务哦。

    死锁发生的概率主要与以下几点因素有关：

    • 系统中同一时间运行的事务的数量，数量越多发生死锁的概率越大（废话）
    • 每个事务包含的操作越多，发生死锁的概率就越大（废话）
    • 操作的数据集合越小，发生的死锁概率就越大

    注：当一个事务发生死锁后它会回滚，这和我们之前说的事务中锁等待的情况不一样。总结起来说：如果一个事务中的某个操作锁等待超时了，那么这个事务不会回滚，而如果一个事务死锁了，那么InnoDB存储引擎会回滚整个事务哦。

    InnoDB锁信息查看

    其实，对于我们做数据库运维来说，查看锁信息并依次来定位问题至关重要。 或许我们对锁信息的查看还仅仅局限于通过 show processlist 或者 show engine innodb status 命令。但其实在5.6及以上的版本中我们不必再那样去看了，因为那样看很不直观，而且在高并发的情况下执行结果瞬间挤满屏幕，很不利于我们分析查看。

    在 information_schema 库底下有三张表分别为 innodb_trx 、innodb_locks、innodb_lock_waits 。通过这三张表，我们可以更简单的监控当前事务并分析可能存在的锁问题。

    innodb_trx

    这张表描述了当前数据库系统下所有正在进行的事务的状态：

    名称 属性
    TRX_ID 唯一的事务ID号
    TRX_WEIGHT 一个事务的权重，主要反映改变的记录数和被事务锁定的记录数。 在处理死锁时, InnoDB 会选择一个具有小权重的事务作为"受害者”回滚。
    TRX_STATE 事务执行的状态, 允许的值为 RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
    TRX_STARTED 事务开始时间
    TRX_REQUESTED_LOCK_ID 如果TRX_STATE值为LOCK WAIT，则此值表示的是事务等待的锁ID，否则此值为null，可以使用LOCK_ID和INNODB_LOCKS表关联
    TRX_WAIT_STARTED 如果TRX_STATE值为LOCK WAIT，则此值表示事务从什么时候开始等待的
    TRX_MYSQL_THREAD_ID MySQL thread ID,得到细节关于thread, 使用这个列和NFORMATION_SCHEMA PROCESSLIST table的ID进行关联
    TRX_QUERY 事务正在执行的语句
    TRX_OPERATION_STATE 事务的当前操作（如果有的话）否则为NULL
    TRX_TABLES_IN_USE 此事务涉及的表的数量
    TRX_TABLES_LOCKED 事务加锁的表的数量
    TRX_ISOLATION_LEVEL 事务的隔离级别
    TRX_LOCK_STRUCTS 事务占据的锁的数量
    TRX_LOCK_MEMORY_BYTES 事务的锁消耗的内存
    TRX_ROWS_LOCKED 事务锁定的行数（大约值）

    innodb_locks

    顾名思义，这张表描述的是innodb存储引擎的锁信息：

    名称 属性
    LOCK_ID 全局的锁ID
    LOCK_TRX_ID 锁对应的事务ID，可以与innodb_trx做链接
    LOCK_MODE 锁模式，主要包括： S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, and UNKNOWN. Lock
    LOCK_TYPE 锁类型，主要包括： RECORD for a row-level lock, TABLE for a table-level lock.
    LOCK_TABLE 锁对应的表
    LOCK_INDEX 锁住的索引
    LOCK_SPACE 所对象的space id
    LOCK_PAGE 事务锁住的页的数量，若是表锁，则该值为null
    LOCK_REC 事务锁定行的数量，若是表锁，则该值为null
    LOCK_DATA 事务锁定记录的主键值，若是表锁，则该值为null

    其实，当我们在某些场景下想查看当前数据库系统的锁情况时，可以直接查询这张表即可。下面为一个示例：

    innodb_lock_waits

    顾名思义，这张表描述了锁等待的信息：

    名称 属性
    REQUESTING_TRX_ID 被阻塞的事务ID
    REQUESTED_LOCK_ID 被阻塞的锁ID
    BLOCKING_TRX_ID 阻塞了别人的事务ID
    BLOCKING_LOCK_ID 阻塞了别人的锁ID

    这张表需要和前两张表一起联合查询能起到查看当前事务和锁的状态。

    操作实例

    查看当前数据库系统有哪些事务在等待锁 ？ 处于等待状态的相关SQL是什么？在等待哪些事务完成 ？拥有当前锁的SQL是什么？比如下面这个例子：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-13-%e4%b8%8b%e5%8d%886-05-37

    我们可以这样的方式查询得出想要的结果：

    我们举个实际的例子：

    session1 session2
    begin begin
    mysql> select * from t3 for update ;
    +—-+———–+
    | id | name      |
    +—-+———–+
    |  1 | 000000    |
    |  2 | 888888    |
    |  3 | gggggg    |
    |  4 | 444444    |
    |  5 | ccccccc   |
    |  6 | 666666    |
    |  7 | hehehehe  |
    |  8 | sssssss   |
    |  9 | bbbbbbbbb |
    | 11 | nnnnnn    |
    | 12 | lkjhgfd   |
    | 13 | hhhh      |
    | 15 | pppppp    |
    +—-+———–+
    13 rows in set (0.00 sec)
    mysql> update t3 set name = “cvbnm” where id = 6;

    如上，当我们在事务2中执行update就阻塞了，然后我们看看：

    如上，Id 为 31 是我的第三个session用来查看的 ，Id 为 32 是session 1的连接，Id 为 33 是 session 2 的连接，我们用上面的SQL试一下：

    是不是一目了然呢 ？

    对于死锁来说，在MySQL中可以把死锁信息打印到错误日志里，开启如下变量即可。

    然后死锁发生时错误日志中会出现如下信息，以及对应的SQL语句和死锁信息和事务信息。：

    另外，我们也可以根据全局的status变量来监控和衡量锁的相关情况：

    • Innodb_row_lock_current_waits：当前锁等待的数量
    • Innodb_row_lock_time：自系统启动到现在，锁定的总时间，单位：毫秒 ms。
    • Innodb_row_lock_time_avg：平均锁定的时间，单位：毫秒 ms。
    • Innodb_row_lock_time_max：最大锁定时间，单位：毫秒 ms。
    • Innodb_row_lock_waits：自系统启动到现在，锁等待次数，即锁定的总次数。

     

     

  • MySQL数据库锁（二）

    2017-04-08 作者：SleepSir
    暂无评论

    背景

    在上文中，我们讲述了MyISAM存储引擎的表级别锁。主要是一些基本概念和简单应用，可能比较浅显，甚至有些错漏，后续的实践中再持续改进吧。 今天主要是研究一下InnoDB存储引擎，这个是我们日常运维中涉及和使用最多的，也是最重要的。 InnoDB引擎的锁机制概念非常庞杂，牵扯到很多的知识点，想要彻底搞明白绝非易事。当然业内不乏一些对MySQL源码深入了解的大牛深入剖析，但本人目前明显还不具备这个实力，此博客本着研究学习的态度，去总结研究InnoDB锁相关知识。

    内容

    当我们打开MySQL的官方文档，搜索lock时，会看到MySQL有如下几种锁：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-07-%e4%b8%8b%e5%8d%882-28-04

    额，感觉作为一个存储引擎好累啊，每天要处理和管理那么多锁。 呵呵，幸好他不是人，但由此也可以想到编写数据库的程序猿们还是很厉害的。那么这么多锁，我们该要如何下手呢 ？ 先从最熟悉的，也就是我们经常念叨在嘴边的InnoDB的行锁下手：

    共享锁与排它锁（Shared and Exclusive Locks）

    InnoDB实现了两种标准的行级锁：

    共享锁（S Lock），允许事务读一行数据。                     用法：  SELECT … LOCK IN SHARE MODE ;

    排他锁（X Lock），允许事务删除或更新一条数据。       用法：  SELECT … FOR UPDATE ;

    注：持锁的粒度是事务，即当一个事务A获得某行的共享锁后，另一个事务B也可以获取此行的共享锁。

    InnoDB行级锁遵循普通的共享、排他规则。 举个最简单的例子：

    yy

    意向锁（Intention Locks）

    InnoDB存储引擎支持多粒度的锁定，意向锁是InnoDB引擎支持的表级锁。

    意向共享锁（IS Lock）： 事务想要获得一张表中某几行的共享锁 ， 顾名思义， 事务有意向去给一张表中的几行加S锁。当事务想要获得一张表的某几行记录的S锁时，必须先要获得此表的IS锁。

    意向排他锁（IX Lock）： 事务想要获得一张表中某几行的排他锁， 顾名思义， 事务有意向去给一张表中的几行加X锁。当事务想要获得一张表的某几行记录的X锁时，必须先要获得此表的IX锁。

    他们的兼容性如下：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-07-%e4%b8%8b%e5%8d%883-05-26

    到这里其实我心里有了疑惑：

    1. 意向锁的存在意义是什么 ？ 如果说加意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行。那么，意向锁的作用就是“表明”加锁的意图，可是为什么要表明这个 意图呢？如果仅仅锁定一行仅仅需要加一个锁，那么就直接加锁就好了呗。
    2. 从上面的兼容性矩阵我们发现IX 与 X锁不兼容，那这岂不是反而降低了InnoDB事务的并发性？ 比如：
    • 事务A获取test表的IX表级锁成功
    • 事务B也获取test表的IX表级锁成功（因为IX与IX兼容）
    • 事务A想要给test某行加X锁时，阻塞了 ？ （因为X锁与IX锁不兼容）

    InnoDB肯定不会这么挫吧？ 肯定是我的想法与实际有偏差，经查阅资料后原来是我自己的理解有误。

    我们先来说说意向锁的存在意义，假设有如下场景：

    —— 事务A锁住了表中的一行，让这一行只能读，不能写。

    —— 之后，事务B申请整个表的写锁。

    如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

    那么数据库要怎么判断才能让B事务避免这个冲突呢 ？

    step1：判断表是否已被其他事务用表锁锁表，如果是则调到step2，否则直接加锁成功。
    step2：判断表中的每一行是否已被行锁锁住。

    注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

    那么在意向锁存在的情况下，上面的判断就变成了：

    step1：不变
    step2：发现表上有其他事务的意向共享锁，事务B申请表的写锁直接被阻塞。不必再判断每一行了。

    注：以上解释来自知乎问答（https://www.zhihu.com/question/51513268）

    也就是说，意向锁的存在意义是避免事务需要获取表级别的锁时，总是遍历表的所有行以判断是否被加了行锁。举个实际的例子，比如当我执行（ALTER TABLE, DROP TABLE, LOCK TABLES）等操作时，如果没有意向锁，我需要遍历表的所有行才能知道是否被加了行锁，如果被加了，那么这个操作肯定要先被阻塞。 但如果我有意向锁，我只需要查看这个表是否有意向锁即可，不必再遍历表了。

    其实到这里，我们的第二个问题的答案也呼之欲出了，上面的兼容矩阵里面描述的与IX意向锁不兼容的X锁指的是由类似（ALTER TABLE, DROP TABLE, LOCK TABLES）操作触发的表级X锁，而不是我之前理解的行级X锁。 也就是说：

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

    InnoDB锁的算法

    恩？  怎么跳到锁的算法了？ 讲完InnoDB的行锁和意向锁后不是应该挨着顺序讲那个什么Record Locks 和 Gaps Locks等等吗？ 其实，这些本身就是行锁的一种算法，不是一种独立的锁。

    InnoDB存储引擎有3种行锁的算法：

    Record Locks： 单个索引行记录上的锁

    Gap Locks：间隙锁，锁定一个范围，但不包含记录本身

    Next-Key Lock： Record Locks + Gap Locks ，以上两种锁的结合，锁定一个范围，并且锁定记录本身。

    那么问题来了，为什么要有三种行锁的算法呢？ 他们各有什么特性，孰优孰劣 ？

    其实，他们各有千秋，适用于不同的场景，我们从最基础的Record Lock说起，他是最基本的行锁，必须建立在索引行之上，我们都可以理解，无非就是用于给表中的某一索引行记录加锁。那么有它就够了啊，要间隙锁干毛啊 ？ 还是有用的，我们来看如下这个场景：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-07-%e4%b8%8b%e5%8d%886-13-09

    注：之所以将session1的隔离级别改成RC，是因为RC隔离级别默认没有Gap Lock。 

    如上，session1在一个事务内连续的两次同样的读，得到的结果竟不一样，这不就是传说中的幻读吗 ？ 这不就是传说的RC隔离级别无法避免幻读吗 ？   同样的，我改成RR隔离级别试了一下发现它不存在这个现象了，而RR隔离级别之所以这么屌就是因为它采用了Nect-Key Lock（兼容了Gap Lock的Record Lock）。

    那么间隙锁长啥样呢？ X锁又长啥样呢？ 什么情况下会用到什么样的锁呢 ？我们以实际的例子来看一下，如果我有这样一张表：

    那么当我给一张表执行如下操作的时候，他都会加什么锁呢？

    SQL1： select * from hello where id = 23;                                        # 主键索引

    SQL2:    select * from hello where grade = 555;                               # 唯一索引

    SQL3:    update hello set name = “james” where age  = 98;            # 普通索引

    SQL4:    delete from hello where extr = “for”;                                   # 无索引

    注：MySQL的默认隔离界级别是RR隔离级别的。  

     

     

    SQL1主键索引为检索条件时，对应的加锁如下：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-08-%e4%b8%8b%e5%8d%884-07-59

    注：当我们的索引条件对应的值是主键时，只会在对应的那一行上加上R读锁或者X写锁。 我这里暂时没有考虑意向锁。

    SQL2唯一索引为检索条件时，对应的加锁应该如下：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-08-%e4%b8%8b%e5%8d%884-21-50

    注：InnoDB是基于主键的聚簇索引，当我们以唯一索引为主键时，他会先通过唯一索引定位到主键索引并加锁。

    SQL3普通索引为检索条件时，对应的加锁应该如下：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-08-%e4%b8%8b%e5%8d%884-56-20

    注：终于见到传说中的间隙锁啦，哈哈，这下很容易想明白间隙锁的作用啦，假设如果没有最左上角那个间隙锁，那么另外一个session想要执行如下SQL时，就可以直接执行成功了：

    insert into hello (id, age , name) values (10, 98, “kvbnxs”);  

    如果我本session在insert之前执行where age = 98 的查询和insert之后执行where age = 98 的查询得到的结果可能就不一样了！就产生了我们说的幻读。 

    SQL4检索条件为无索引时，加锁条件应该如下：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-08-%e4%b8%8b%e5%8d%884-53-29

    注：当检索列无索引时，MySQL无可避免的要进行全表扫描，因为它并不知道你的目的行在那里，所以他只能先将所有行都锁住，同时他为了避免幻读，会加上间隙锁。如果我们将隔离级别换做RC或者设置参数

    innodb_locks_unsafe_for_binlog = 1

    那么就没有间隙锁了。

    Next-Key Locks 说白了就是两种锁的结合，RR 隔离级别采用这种加锁方式避免了幻读，这个就不再这里做详细介绍了。

    Insert Intention Locks

    插入意向锁是间隙所的一种，顾名思义，就是在插入数据之前获取一个有插入意向的间隙锁，它的存在意义其实我也没太搞明白，觉得Next-Key Locks就可以搞定了，后续有机会深入再叙述吧，现在没有头绪，没法写这个。

    AUTO-INC Locks

    自增锁是一种表锁，应用于锁上定义了 auto_increment 时，并发插入操作能够以竞态方式保证插入的行是按定义自增的。可以由 innodb_autoinc_lock_mode  参数控制。

     

    今天先讲到这里，关于InnoDB的锁还有很多内容，比如：锁的阻塞、死锁、锁的查看与分析等，后续章节我们再继续。

     

    参考：

    MySQL 加锁处理分析

     

  • MySQL数据库锁（一）

    2017-04-06 作者：SleepSir
    暂无评论

    背景

    众所周知，MySQL一般使用加锁这种方式来保证一致性和隔离性。但同时数据库又是个高并发的应用，同一时间会有大量的并发访问，如果加锁过度，会极大的降低并发处理能力。所以对于加锁的处理，可以说就是数据库对于事务处理的精髓所在。

    数据库锁本就是相对比较复杂的，从原理到应用，其实很少有人能通透理解，本人也是。 但数据库锁却也是至关重要的，作为DBA的话，如果不能有比较好的了解，恐怕自己在处理问题时都会战战兢兢。所以本系列博文致力于探究数据库锁的基本概念，意在清晰的掌握MySQL数据库锁的基本原理和应用。并不会有深度的源码分析（主要是做不到），也不会有大量的实践经验（后续会逐步完善）。

    内容

    MySQL是插件式存储引擎的数据库，所以MySQL不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY 存储引擎采用的是表级锁；BDB存储引擎采用的是页面锁，但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。那么这么多锁，到底哪个比较好呢？我们在实际应用中应该如何选择呢 ？

    我们衡量锁的标准主要是他的开销、是否可能会造成死锁、锁定粒度、锁冲突的概率、并发度。

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

    从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

    而我们这里对存储引擎也分开讲解，主要以目前最常用的MyISAM和InnoDB存储引擎为主。

    MyISAM引擎锁

    MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎。

    MyISAM表级锁查看

    我们可以在数据库中执行如下语句查看当前MySQL Server的表级别锁的情况：

    table_locks_immediate表示可以立即获取锁的请求次数，table_locks_waited表示不能立即获取锁，需要等待的请求次数，比如：

    如上，对于MyISAM引擎表来说，一般的查询都可以看做是可以立即获取表锁的请求，都会导致table_locks_immediate值的增加（同样的试验在InnoDB引擎表中就不会导致这个值增加，因为InnoDB是行锁，而这个值是记录表锁的）。

    那么table_locks_waited 在什么情况下会增加呢 ？ 按照我的理解，应该是在MyISAM表在加了读锁或者写锁之后，有其他连接想进行读写的时候会锁等待，造成此值加1，所以进行如下实验：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-06-%e4%b8%8b%e5%8d%882-57-30

    如上，我发现那个该死的table_locks_waited值并没有增加。但是session2的update操作当时确实被阻塞了啊，这是怎么回事呢？  经过查询我发现了，原来我的update被元数据锁给阻塞了，而不是我用lock命令加的读锁或者写锁。所以此值没有增加。（我在mysql5.1上验证这个值是可以增长的)

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-06-%e4%b8%8b%e5%8d%883-05-20

    那么问题来了，如果是这样的话，只要我是5.5版本之后的MySQL数据库（有了metadata lock）这个table_locks_waited值岂不是永远不会增加了 ？ 那他还有什么意义呢 ？ 

    其实也不尽然，如果在实际应用场景中有N个线程链接同时并发对这个t4表进行update操作，则会使得这个table_locks_waited迅速增加。

    所以，在日常数据库运维中，如果table_locks_waited值比较大的话那么就说明表级别的锁等待比较频繁，要考虑优化扩展数据库的性能了。

    MyISAM表级锁类型

    MyISAM的表级别锁很简单，只有两种，一种读锁，一种写锁。 彼此的兼容性如下：

    表级读锁 表级写锁
    表级读锁 兼容，可以同时获得 不兼容
    表级写锁 不兼容 不兼容

    用一句话概括来说就是：MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

    MyISAM表级别锁添加

    如何手动出发一个MyISAM表的读锁和写锁呢 ？MyISAM本身在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。如果想要测试，那么可以用此命令进行显式加锁：

    示例：

    MyISAM的表级锁有如下几个特性：

    1. 在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；

    2. 如果加的是读锁，那么只能执行查询操作，而不能执行更新操作

    3. 在显式定义读锁时，如果加了local选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。所以，在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

    那么什么样的情况才算是满足并发插入的条件呢？

    MyISAM存储引擎有一个系统变量concurrent_insert，可以控制这个条件。
    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-06-%e4%b8%8b%e5%8d%886-31-26

    •        当concurrent_insert设置为0 | NEVER时，不允许并发插入。

    •        当concurrent_insert设置为1 | AUTO时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

    •        当concurrent_insert设置为2 | ALWAYS时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

    如下，我把concurrent_insert 值设置为ALWAYS后，做的试验：

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-06-%e4%b8%8b%e5%8d%886-49-57

    如上，当session1 获得read local锁时，他自身只能进行读操作，但是session2可以进行表尾的插入，但不能更新。

    MyISAM表级锁的调度

    MyISAM引擎的读锁和写锁是互斥的，读写操作是串行的，那么，两个进程同时请求MyISAM的读锁和写锁，谁具有更高的优先级呢？答案是写锁具有更高的优先级，不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过设置low-priority-updates参数来调节MyISAM 的调度行为。 

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-06-%e4%b8%8b%e5%8d%887-41-54

    这个参数的意思很简单，降低更新操作相对于查询操作的优先级。

     

    主要参考

    http://blog.csdn.net/xifeijian/article/details/20313977