思考并回答以下问题：

锁是数据库实现数据并发访问最为重要的资源。不合适的锁粒度，不适当的加锁时机、解锁时机，不恰当的锁作用范围等都会导致锁资源的争用现象，继而出现锁等待问题，甚至是死锁问题。

表级锁资源竞争

对于表级锁而言，读锁与写锁是相互排斥的锁，可以简单地理解为：使用表级锁时，对同一个表的查询操作与对同一个表的更新操作是完全串行化的，不能并发地执行，否则将出现锁等待现象。数据库管理员可以使用MySQL命令“show status like ‘table_locks％’;”，通过查看表级锁状态变量的值，分析当前MySQL服务实例上表级锁资源竞争的情况，如图1所示。这两个状态变量的解释如下。

图1 表级锁资源竞争

Table_locks_immediate：使用表级锁后立即释放表级锁的次数，从MySQL服务启动后开始记录。
Table_locks_waited：出现表级锁争用而发生的锁等待次数，从MySQL服务启动后开始记录。如果 Table_locks_waited 的值较大，说明表级锁争用严重，存在严重的并发瓶颈（表级锁不存在死锁问题）。

表级锁资源竞争的解决方案

表级锁允许多个线程同时从一个表中进行读取操作，但如果一个线程想要对表进行更新操作，它必须首先获得独占访问。更新期间，所有其他想要访问该表的线程必须等待直到更新完成。通常认为表更新比表检索更重要，因此通常给更新操作赋予更高的优先级，确保了更新操作不被“饿死”，即使该表有很繁重的检索操作。

然而这种设计会导致读操作被“饿死”，假设MySQL客户机A针对MyISAM表执行长时间的select语句A，期间，MySQL客户机B对该表进行更新操作B，MySQL客户机B必须等待直到select语句A完成；MySQL客户机C对该表执行另一个select语句C，由于更新操作B比select语句C优先级高，select语句C等待更新操作B完成，并且等待select语句A完成。
MySQL允许改变语句调度的优先级，使得多个MySQL客户机的查询更好地协作，确保特定类型的查询被更早地处理。

● 使用“-low-priority-updates”选项启动mysqld。此时所有更新操作的优先级比select语句的优先级低。在这种情况下，select语句C将在更新操作B前执行，而不需要等候select语句A完成。
● 使用MySQL命令“set low_priority_updates=1;”指定某一个会话中的更新操作使用低优先级。
● 向特定的insert、delete、update、replace或者load data语句添加low_priority选项，降低这些操作的优先级。
● 向特定的select语句添加high_priority选项，提高检索操作的优先级。
● 为max_write_lock_count系统变量指定一个低值启动mysqld，强制MySQL在具体数量的插入完成后临时提高所有等待执行的select语句的优先级。这样允许在一定数量的写锁后存在读锁。
● 使用延迟插入。
● 对SELECT语句使用sql_buffer_result可以使表锁定时间变短。
● 对同一个表混合使用select和delete语句出现问题时，在delete语句中添加limit选项指定删除的记录行数。
● 启用并发插入。

MyISAM表上有一个读锁时，原则上其他MySQL客户机不能对该表进行插入操作。但启用并发插入后，MyISAM表支持查询和插入的并发操作，可以在一定程度上缓解表级锁争用问题。全局系统变量concurrent_insert专门用以控制并发插入的行为。该参数值可以使用MySQL命令“show variables like ‘concurrent％’;”进行获取，如图14-84 所示。

图2 concurrent_insert系统变量

● 当concurrent_insert设置为0时，不允许并发插入。
● 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），在一个MySQL客户机读表的同时，MyISAM允许另一个MySQL客户机从表尾插入记录。这也是MySQL的默认设置。
● 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
注意：并发插入对隐式表级锁有效，对显式表级锁无效。使用InnoDB替换MyISAM存储引擎是表级锁资源竞争的最有效解决方案。

行级锁的资源竞争

数据库管理员可以使用MySQL命令“show status like ‘innodb_row_lock％’;”，通过检查行级锁状态变量的值，分析当前MySQL服务实例上行级锁资源竞争的情况，如图14-85所示。这5个状态变量的解释如下。

▲图14-85 行级锁的资源竞争

● Innodb_row_lock_current_waits：当前锁等待的数量，从MySQL服务启动后开始记录。
● Innodb_row_lock_time：锁等待的总时间，从 MySQL 服务启动后开始记录。
● Innodb_row_lock_time_avg：锁等待的平均时间，从MySQL服务启动后开始记录。该值较大时，说明锁冲突大。
● Innodb_row_lock_time_max：最长的一次锁等待时间，从MySQL服务启动后开始记录。
● Innodb_row_lock_waits：锁等待的次数，从MySQL服务启动后开始记录。
如果Innodb_row_lock_waits以及Innodb_row_lock_time_avg的值较大，说明行级锁争用严重，影响了其他MySQL线程的正常处理，存在严重的并发瓶颈，需要查找出原因并解决。

行级锁资源竞争的解决方案

如果行级锁争用严重，可以采取如下方案解决行级锁的并发瓶颈。

● 尽量缩短锁的生命周期。例如在事务中避免使用长事务，可以将长事务拆分成若干个短事务。在事务中避免使用循环语句。
● InnoDB 默认的事务隔离级别是 repeatable read，而 repeatable read 隔离级别使用间隙锁实现InnoDB的行级锁。优化索引，优化SQL语句，避免行级锁升级为表级锁。
● 优化表结构，优化SQL语句，尽量缩小锁的作用范围。例如可以将大表拆分成小表，从而缩小锁的作用范围。
● 优化表结构，优化SQL语句，尽量缩小锁的作用范围。例如可以将大表拆分成小表，从而缩小锁的作用范围。
● 对于InnoDB行级锁而言，设置锁等待超时参数为合理范围，编写锁等待超时异常处理程序，解决发生的锁等待问题（甚至死锁）。
● 为避免死锁，一个事务对多条记录进行更新操作时，获得所有记录的排他锁后，再进行更新操作。
● 为避免死锁，一个事务对多个表进行更新操作时，获得所有表的排他锁后，再进行更新操作。
● 为避免死锁，确保所有关联事务均以相同的顺序访问表和记录。
● 必要时，使用表级锁来避免死锁。
● 必要时，可以开启全局系统变量innodb_print_all_deadlocks。死锁发生后，死锁所有的相关信息将记录到MySQL错误日志中。

禁用InnoDB间隙锁

在事务与锁机制章节曾经提到：利用间隙锁的特点，对查询结果集施加共享锁（lock in share mode）或者排他锁（for update）可以避免幻读现象。然而有时事务中如果存在太多的间隙锁，反而会影响事务之间的并发性能。可以使用下面两种办法禁用InnoDB间隙锁。
方法一：把事务隔离级别修改为READ COMMITTED。
方法二：将全局变量innodb_locks_unsafe_for_binlog设置为ON（或者1），禁用间隙锁。

事务监控与锁监控

新版本的InnoDB存储引擎中，在information_schema数据库中添加了INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS三张表。通过这三张表，可以更简单地新版本的 InnoDB 存储引擎中，在 information_schema 数据库中添加了 INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS三张表。通过这三张表，可以更简单地监控当前的事务并分析可能存在的锁的问题。例如读者在演示间隙锁与死锁章节的代码时，可以通过查看这三张表的记录信息，监控当前的事务并分析可能存在的锁问题。

INNODB_TRX表包含了当前InnoDB执行的每一条事务信息，其中包括事务的开始时间、事务正在执行的SQL语句等信息，这些内容存储在INNODB_TRX表的24个字段中，限于篇幅这里仅介绍常用的几个字段，如表14-4所示。

表14-4 INNODB_TRX表的常用字段INNODB_LOCKS表包含了当前InnoDB的锁信息（但不包括元数据锁信息，有关元数据锁的相关知识稍后进行介绍），其中包括锁住的事务ID、锁的模式等信息。这些内容存储在INNODB_LOCKS表的10个字段中，如表14-5所示。

INNODB_LOCK_WAITS表包含了当前InnoDB的锁资源信息及被锁住的事务信息，该表由4个字段组成，如表14-6所示。

表14-6 INNODB_LOCK_WAITS表的常用字段

元数据锁metadata locks

从MySQL 5.5.3 开始，任何已经开始的事务将一直持有InnoDB表的元数据锁，直到事务提交。除此之外，使用alter table语句对InnoDB表的表结构进行修改时，alter table语句也会持有InnoDB表的元数据锁，直到alter table语句执行结束。下面的操作步骤意在说明元数据锁的存在。

步骤1：打开MySQL客户机A，输入如下命令，将account表的存储引擎修改为InnoDB。
alter table account engine=InnoDB;

步骤2：在MySQL客户机A中输入如下命令，首先开启事务，然后查询account表中的所有记录，执行结果如图14-86所示。

1 2	start transaction; select * from account;

步骤3：打开MySQL客户机B，输入如下命令，首先开启事务，然后查询account表中的所有记录，执行结果如图14-87所示。

1 2	start transaction; select * from account;

步骤4：打开MySQL客户机C，输入如下alter table语句，修改account表结构，向该表增加address字段（默认值为beijing），alter table语句被阻塞，执行结果如图14-88 所示。

1	alter table account add column address char(32) default 'beijing';