mysql的锁机制之表锁

雨点打透心脏的1/2处 2022-05-25 13:12 227阅读 0赞

**MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定.**  
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。  
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

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

**本文主要介绍表锁的类型,机制,创建,解锁,优化思路等内容.**

**1.表级锁定（table-level）的定义:**  
表级锁定的定义:表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

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

**2.表锁定的类型**

表级锁定主要分为两种类型，一种是读锁定，另一种是写锁定 .  
意思是假如用户对一个表进行读锁定,其他用户有读的权利,不能写.  
假如用户对一个表进行写锁定,其他用户不能读也不能写.  
用户对表进行读写都是有条件的:  
一个新的客户端请求在申请获取读锁定资源的时候，需要满足两个条件：  
1.资源没有被写锁定  
2.写锁定等待队列中没有更高优先级的写锁定等待  
如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入读队列中，否则会被迫进入等待队列中等待资源的释放。  
一个新的客户端请求在申请获取写锁定资源的时候，被申请资源需要满足三个条件：

1.没被写锁定2.没被写锁定等待3.没被读锁定

**3.两种表锁的管理机制**

上述所说的队列,大概有四种,他们用来维护两种锁定状态.锁类似于一个仓库的大门,而这些队列为门卫.维持仓库的状态和人们的行为.  
在MySQL中，主要通过四个队列来维护这两种锁定：两个存放当前正在锁定中的读和写锁定信息，另外两个存放等待中的读写锁定信息，如下：  
Current read-lock queue (lock->read) 持有读锁的所有线程  
Pending read-lock queue (lock->read\_wait) 等待读锁的所有线程  
Current write-lock queue (lock->write) 持有写锁的所有线程  
Pending write-lock queue (lock->write\_wait) 等待写锁的所有线程  
当前持有读锁的所有线程的相关信息都能够在Currentread-lockqueue中找到，队列中的信息按照获取到锁的时间依序存放。而正在等待锁定资源的信息则存放在Pendingread-lockqueue里面，另外两个线程存放写锁信息的队列也按照上面相同规则来存放信息.  
**4.表锁的创建,查看,解锁.  
(1)**表锁的创建  
lock tables 表名 \[read | write\]  
lock tables 表名1 \[read | write\], 表名2 \[read | write\]......   
上述语句中名词解释  
read为读锁，加了之后当前会话和其他会话都只能读不能写。  
write为写锁，加了之后只有当前会话可以读和写。  
加单表用lock table，加多表用lock tables（table与tables作用完全相同，只  
是语义化而已） .  
(2)表锁的解除  
语法：unlock table | unlock tables:  
说明：释放被当前会话持有的任何锁。   
(3)查看当前的表锁

示例：show open tables where in\_use > 0;

![70][]

说明：

Database：包含该表的数据库

Table：表名

In\_use：表中有表锁或锁请求的数量（处于锁等待中的锁也参与计数）

Name\_locked：表名是否被锁定。名称锁定用于诸如删除或重命名表等操作

(4)表锁争用情况查看

语法：show status like 'table%';

![70 1][]

说明：

Table\_locks\_immediate：已授予表锁请求的次数。

Table\_locks\_waited：处于锁等待中的表锁数量

**5.表锁优化建议**

(1)如上图所示表锁争用的情况.

两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1。如果这里的Table\_locks\_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

（2）缩短锁定时间

虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较大，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。所以，在优化表锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发

如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。

a)尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；

b)尽可能的建立足够高效的索引，让数据检索更迅速；

c)尽量让用到表锁定的表只存放必要的信息，控制字段类型；

d)利用合适的机会优化表数据文件。

（3）分离能并行的操作

说到MyISAM存储引擎的表锁，而且是读写互相阻塞的表锁，可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化，没办法再并行了。大家不要忘记了，MyISAM的存储引擎还有一个非常有用的特性，那就是ConcurrentInsert（并发插入）的特性。

MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent\_insert，可以设置为0，1或者2。三个值的具体说明如下：

concurrent\_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；

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

concurrent\_insert=0，不允许并发插入。

可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent\_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

（4）合理利用读写优先级

MyISAM存储引擎的是读写互相阻塞的，那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？

答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。

这是因为MySQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。

所以，如果我们可以根据各自系统环境的差异决定读与写的优先级：

通过执行命令SET LOW\_PRIORITY\_UPDATES=1，使该连接读比写的优先级高。如果我们的系统是一个以读为主，可以设置此参数，如果以写为主，则不用设置；

通过指定INSERT、UPDATE、DELETE语句的LOW\_PRIORITY属性，降低该语句的优先级。

虽然上面方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。

另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max\_write\_lock\_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”，因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

[70]: /images/20220525/1e9aae5ba8cc4f6291224b09bddce798.png
[70 1]: /images/20220525/fec7798cb1ff497caaf4578f373e52ef.png