mysql 锁级别说明 (表级锁）二

最新推荐文章于 2024-08-03 15:29:56 发布

Json2011315

最新推荐文章于 2024-08-03 15:29:56 发布

阅读量910

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/yinjinshui/article/details/101676489

版权

mysql 专栏收录该内容

25 篇文章 2 订阅

订阅专栏

表级锁
MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎。

1.MySQL表级锁的锁模式

MySQL的表级锁有两种模式：

表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

锁模式的兼容性：

当前表级锁模式	读锁	写锁
读锁	兼容	不兼容
写锁	不兼容	不兼容

解释说明：

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

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

2.如何加表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

2.1手动加表锁demo:

【客户端1】
共享读锁 [只能执行select操作]
Lock  table  (表名)test  read   【加读锁】
 
 【客户端2】
然后尝试使用update语句操作 ：
update `ceshi2` set test= 'ceshi'
报错如下：
Table 'a' was locked with a READ lock and can't be updated

Unlock tables 【解锁】

截图1：
在这里插入图片描述
截图2加读锁：
截图3解锁：

2.2独占写锁 [无法执行select操作]demo:

【客户端1】
Lock  table  (表名)ceshi2  write   【加写锁】

【客户端2】
[SQL]SELECT * FROM `ceshi2` LIMIT 0, 1000
Waiting for table metadata lock
Unlock tables 【解锁】

截图1：
在这里插入图片描述截图2：
3.MyISAM表锁优化建议

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

1》查询表级锁争用情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like 'Table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 169   |
| Table_locks_waited         | 11    |
| Table_open_cache_hits      | 0     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而发生等待的次数；

两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1。
如当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，具体改善方式必须根据实际场景来判断。

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

尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；
尽可能的建立足够高效的索引，让数据检索更迅速；
尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；
利用合适的机会优化MyISAM表数据文件。

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

mysql> show variables like 'concurrent_insert';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+
1 row in set (0.00 sec)

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如何处理呢？
答案：写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。

原因：SQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。My

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

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

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

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

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

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