深入浅出 MySQL - MyISAM 有趣的那些“锁”事儿
| 写锁（表） | 冲突 | 冲突 |
读锁：对 MyISAM 表的读（SELECT）操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
写锁：对 MyISAM 表的写操作，会阻塞其他用户对同一表的
读和写操作；
对于 MyISAM 引擎，读读操作是可并行的；读写操作以及写写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程对该表的读、写操作都会进入等待，直到写锁被释放为止。
======================================================================
显示加锁方式：
加锁：lock tables … read/write;
-- 给 T1 加读锁
lock tables T1 read;
-- 给 T1 加写锁
lock tables T1 write;
-- 给 T1 加读锁、T2 加写锁；
lock tables T1 read, T2 write;
释放锁：unlock tables;
unlock tables;
与 全局锁 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。
全局锁：Flush tables with read lock (FTWRL)
??这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
??使用场景：全库逻辑备份。
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行 DML 操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁，可以根据具体业务场景修改其加锁配置。
=============================================================================
在使用 MyISAM 存储引擎前，我们要确认选择该引擎的原因，比如该表并发读较多，写操作较少（如用户表、日志表等），如果是由于 DML（增删改）操作都较多造成并发低，建议直接改用 Innodb 引擎。
表锁在实现的过程中比行锁定或者页锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少，毕竟是直接锁表。但由于锁定的颗粒度大，因此造成锁定资源的争用情况也会比其他的锁定级别都要多，在较大程度上会降低并发处理能力。
所以，当优化 MyISAM 存储引擎锁定问题时，重点还要放在提升单事务并发速度上。由于表级别锁是不可能改变的了，因此我们要着眼于尽可能让锁定的时间变短，事务间能尽快释放锁，从而提升并发。可以通过show status like 'table%'命令来排查表锁并发情况
mysql> show status like 'table%;
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Table_locks_immediate | 1000 |
| Tabl
e_locks_waited | 80 |
+----------------------------+---------+
参数讲解：
Table_locks_immediate：产生表锁的次数；
Table_locks_waited：出现表锁争用而发生等待的次数；
两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加 1。如果排查时发现这里的 Table_locks_waited 状态值较高，那么说明系统中表级锁定争用现象比较严重，就要着手于如何减少表锁等待次数了。优化方式又来到我们强项了：SQl 优化、分库分表、减少复杂 SQL、缩印利用率等（干货太多找不到？推荐收藏《MySQL江湖路 | 专栏目录》）。
另外 MyISAM 还有两个有趣且有用的知识点：
福音！当我第一次发现这个策略配置时，心情十分激动，脑海中各种腹黑操作接踵而至~~
我有一个同事小田，经常看我文章的朋友可能会有印象。我俩关系可不一般，怎么形容呢？每天早上见到他，脑子里闪过的场景都是：“叶问一巴掌呼倒日本武士”、“钢铁侠一拳干飞金刚狼”、“腕豪大招把大虫子抱进泉水” 那种酣畅淋漓的场面。
实际场景是这样的：每天上午 9 点我和他都会有脚本对某张 APP 应用大表的不同字段数据进行 UPDATE 操作和部分 INSERT 操作。我发现如果只有我的脚本运行 10 分钟就跑完了，但是和他的脚本一起跑就需要半小时！
我凑！赶紧看看如何能把我的chenhh用户操作优先级提到最高，把tiantian这个垃圾用户优先级调到 0！干 tm 的！
结果却是令人失望的。。
配置优先级只有 MyISAM 引擎可以，我们的表是 Innodb；
MyISAM 只能自定义配置读、写操作的相对优先级，无法配置不同用户间的优先级。。。
MySQL 连这功能都没有吗？这 TM 得优化啊！唉，腹黑的复仇计划再次泡汤，新的认知层次并不能改变我的现状。。或许，还是我太过天真，像孩子一样无助？？卧槽什么歌来着？
言归正传，MyISAM 当读写操作同时出现时，MySQL默认优先执行写操作。那如果一直写，那些读线程不就完蛋了？MySQL 针对这类问题增加了变量max_write_lock_count控制最大写锁数量上限，同学们可以自己根据实际环境配置这个最大锁等待值，达到峰值后，MySQL会自动降低写操作优先级，等这个数量的写操作执行完后，会先把等待读（等待写锁释放）的请求队列中的事务优先处理掉，然后再继续写。
手动控制方法：
- 通过系统变量配置
系统变量配置：通过SET LOW_PRIORITY_UPDATES=ON命令，降低写操作优先级低于读
mysql> show variables like '%LOW_PRIORITY%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| low_priority_updates | OFF |
+----------------------+-------+
1 row in set (0.01 sec)
mysql> SET LOW_PRIORITY_UPDATES=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%LOW_PRIORITY%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| low_priority_updates | ON |
+----------------------+-------+
1 row in set (0.00 sec)
- 在 SQL 语句中配置
在 SQL 语句中临时配置：只对该 SQL 有效
提高优先级操作关键字： HIGH_PRIORITY，HIGH_PRIORITY 可以使用在 SELECT 和 INSERT 操作中，让 MYSQL 知道，这个读操作优先进行。
SELECT HIGH_PRIORITY * FROM T;
降低优先级操作关键字： LOW_PRIORITY，LOW_PRIORITY 可以使用在 INSERT、UPDATE、REPLACE、DELETE 以及 LOAD DATA 等操作中，让 mysql 知道，这个操作优先级较低。
update LOW_PRIORITY T set money +=10000000 where name = '陈哈哈' ;
需要注意的是，如果耗时很长的慢查询（读事务）较多，也会把写进程“饿死”，因此在我们涉及到配置 SQL 执行优先级时，一定要控制好读（SELECT）进程的执行效率。针对一些（复杂度高或查询量大）且难以优化的 SELECT 语句，俗称“硬伤”，针对这些硬伤我建议对业务进行一定的拆分，降低复杂度后处理，或者如果是非必要精确的统计数据，可以加个 EVENT 事件，比如每 10 分钟更新一下结果集存到一个表中，然后使用时直接取，这个我们叫数据报表。
一提到 MyISAM 的表锁，我们立即反应过来的就是串行化，但是我们可以思考一下，如果写操作是一堆 insert 语句的话，是否还必须串行化？
这里我们要提到一个 MySQL 特性：concurrent_insert（并发插入）
MyISAM 存储引擎有一个控制是否打开 concurrent_insert 功能的参数选项：concurrent_insert，可以设置为 0、1、2：
concurrent_insert=
2，无论 MyISAM 表中有没有空洞，都允许在表尾并发插入记录；concurrent_insert=
1，如果 MyISAM 表中没有空洞（即表的中间没有被删除的行），MyISAM 允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是 MySQL 的默认设置；concurrent_insert=
0，不允许并发插入，串行。
concurrent_insert=1（默认）：
Mysql 5.5.2前显示为1；从5.5.3版本开始concurrent_insert=1参数用枚举值默认为AUTO，concurrent_insert=2 为ALWAYS。
mysql> show variables like '%Concurrent%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | AUTO |
+-------------------+-------+
评论