三分钟了解Mysql的表级锁——《深究Mysql锁》

痛定思痛。 2022-05-15 02:04 198阅读 0赞

延伸阅读：  
[五分钟了解Mysql的行级锁][Mysql]  
[一分钟深入Mysql的意向锁][Mysql 1]  
[mysql锁相关讲解及其应用——《深究mysql锁》][mysql_mysql]了解锁前，一定要先看这篇，了解什么是MVCC，如果我们学习锁，没有MVCC的知识，理解起来会总觉得不明朗。本来我的这个只是个记录，并不是专门的讲给别人看的，后发现有不少人看，我还是专门加上这篇文章的链接。

我们首先需要知道的一个大前提是：mysql的锁是由具体的存储引擎实现的。所以像Mysql的默认引擎MyISAM和第三方插件引擎 InnoDB的锁实现机制是有区别的。可根据不同的场景选用不同的锁定机制。  
Mysql有三种级别的锁定：表级锁定、页级锁定、行级锁定

# 一、定义 #

--------------------

*  每次锁定的是一张表的锁机制就是表级别锁定(table-level)。它是MySQL各存储引擎中粒度最大的锁定机制。

# 二、优缺点 #

--------------------

**1. 优点**

*  实现逻辑简单，开销小。
 *  获取锁和释放锁的速度快。
 *  由于表级锁一次会将整个表锁定，所以能很好的避免死锁问题。

**2. 缺点**

*  由于锁粒度最大，因此出现争用被锁定资源的概率也会最高，致使并发度十分低下。

# 三、支持存储引擎 #

--------------------

*  使用表级锁定的主要有MyISAM，MEMORY，CSV等一些非事务性存储引擎。

# 四、表级锁类型 #

--------------------

MySQL的表级锁有两种类型：**表共享读锁**（Table Read Lock）和**表独占写锁**（Table Write Lock）。

锁模式的兼容性：

*  对MyISAM表的**读操作**，不会阻塞其他用户对同一表的读请求，但会**阻塞**对同一表的**写操作**；
 *  对MyISAM表的**写操作**，则会**阻塞**其他用户对同一表的**读和写操作**；

MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

# 五、如何加表锁 #

--------------------

*  在执行查询语句（select）前，会自动给涉及的所有表加读锁
 *  在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁。这个过程并不需要用户干预，因此不需要直接用lock table命令给MyISAM表显式加锁。

显示加写锁：

// 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。
    // 其他线程的读、写操作都会等待，直到锁被释放为止。
    // test表将会被锁住，另一个线程执行select * from test where id = 3;将会一直等待，直到test表解锁
    LOCK TABLE test WRITE;

显示加读锁

// test表将会被锁住，另一个线程执行select * from test where id = 3;不会等待
    // 执行UPDATE test set name='peter' WHERE id = 4;将会一直等侍，直到test表解锁
    LOCK table test READ;

显示释放锁：

UNLOCK TABLES;

需要**注意**的是，在`同一个SQL session`里，如果已经获取了一个表的锁定，则对没有锁的表不能进行任何操作，否则会报错。

// 锁定test表
    LOCK table test WRITE;
    
    // 操作锁定表没问题
    SELECT * from test where id = 4;
    
    // 操作没有锁的表会报错
    SELECT * from bas_farm where id =1356

报错：`[Err] 1100 - Table 'bas_farm' was not locked with LOCK TABLES`。这是因为MyISAM希望一次获得sql语句所需要的全部锁。这也正是myisam表不会出现死锁的原因。

当然，你也不必担心，MyISAM引擎的默认方式是会给同一个session里的所有表都加上锁的，不会麻烦你自己显示操作的。

# 六、查看表级锁争用情况 #

--------------------

执行：show status like ‘table%’;

mysql> show status like 'table%';
    +----------------------------+-----------+
    | Variable_name              | Value     |
    +----------------------------+-----------+
    | Table_locks_immediate      | 20708     |
    | Table_locks_waited         | 0         |
    +----------------------------+-----------+

Table\_locks\_immediate：产生表级锁定的次数；  
Table\_locks\_waited：出现表级锁定争用而发生等待的次数；  
如果Table\_locks\_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

# 七、优化表级锁定 #

--------------------

优化表级锁时的最大问题是：提高并发度  
\#\#\#1. 通过减少查询时间缩短锁定时间  
缩短锁定时间的总体原则是：让Query执行时间尽可能的短。

*  尽量减少大的、复杂的Query，将复杂Query分拆成几个小的Query分步执行；
 *  尽可能的建立足够高效的索引，让数据检索更迅速；
 *  尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；
 *  利用合适的机会优化MyISAM表数据文件。  
    \#\#\#2. 设置可并发插入：concurrent\_insert=2  
    MyISAM的表锁虽是读写互相阻塞的，但依然能够实现并行操作。MyISAM存储引擎有一个控制是否打开Concurrent Insert（并发插入）功能的参数选项：**concurrent\_insert**，取值范围为0，1，2。
 *  concurrent\_insert=0，不允许并发插入。
 *  concurrent\_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这是MySQL的默认设置；
 *  concurrent\_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；

所以，我们可通过设置concurrent\_insert=2，同时定期在系统空闲时段执行`optimize table tableName`语句来整理空间碎片，收回因删除记录而没有真正释放的空间，从而提高并发。optimize参考：[mysql中OPTIMIZE TABLE的作用及使用][mysql_OPTIMIZE TABLE]  
\#\#\#3. 合理设置读写优先级  
MyISAM存储引擎默认是**写优先级大于读优先级**。即使是写请求后到，写锁也会插到读锁请求之前。

但是，有时像修改文章点击数 操作是不那么重要的，我们希望的是读更快，此时我们可以这样：

UPDATE  LOW_PRIORITY  article SET click_num=134 WHERE id = 823

LOW\_PRIORITY使得系统认为update操作优化级比读操作低，如果同时出现读操作和上面的更新操作，则优先执行读操作。

**MySQL提供了几个语句调节符，允许你修改它的调度策略**：

*  **LOW\_PRIORITY**关键字应用于：DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。
 *  **HIGH\_PRIORITY**关键字应用于：SELECT、INSERT语句。
 *  **delayed**(延迟)关键字应用于：INSERT、REPLACE语句。

如果你希望所有支持LOW\_PRIORITY选项的语句都默认地按照低优先级来处理，那么可能使用\*\*`low-priority-updates`\*\*选项来启动服务器。然后可通过使用`insert HIGH_PRIORITY table.....`来把个别我们希望的INSERT语句提高到正常的写入优先级。

[Mysql]: https://blog.csdn.net/zcl_love_wx/article/details/81983267
[Mysql 1]: https://blog.csdn.net/zcl_love_wx/article/details/82015281
[mysql_mysql]: https://blog.csdn.net/zcl_love_wx/article/details/83305645
[mysql_OPTIMIZE TABLE]: https://www.cnblogs.com/jimmy-muyuan/p/5874410.html