乐趣区

了解Mysql的表级锁-深究mysql锁

jiezi

一、定义

  • 每次锁定的是一张表的锁机制就是表级别锁定(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 的作用及使用

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 语句提高到正常的写入优先级。

原文链接 https://blog.csdn.net/zcl_love_wx/article/details/81977447

退出移动版