mysql 锁优化

20.1 概述

mysql锁分类

1. 表级锁：开销小，枷锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2. 行级锁：开销大，枷锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发性度最高。
3. 页面锁：开销和加锁时间节约表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发性一般；
   存储引擎对锁的支持
   **锁冲突：**例如说事务A将某几行上锁后，事务B又对其上锁，锁不能共存否则会出现锁冲突。（但是共享锁可以共存，共享锁和排它锁不能共存，排它锁和排他锁也不可以）

死锁： 例如说两个事务，事务A锁住了1_{5行，同时事务B锁住了6}10行，此时事务A请求锁住6_{10行，就会阻塞直到事务B施放6}10行的锁，而随后事务B又请求锁住1_{5行，事务B也阻塞直到事务A释放1}5行的锁。死锁发生时，会产生Deadlock错误。

|锁类型|开销|加锁速度|死锁情况|锁定粒度|锁冲突概率|并发性|
|—|---|—|
|行级锁|大|慢|会|小|大|高|
|表级锁|小|快|不会|大|小|低|

mysql存储引擎对锁的支持

存储引擎	表级锁	行级锁	页面锁
MyISAM	YES
InnoDB	YES	YES
MEMORY	YES
BDB	YES		YES

20.2 MyISAM表级锁

###20.2.1 查询上锁语句

use sakila
show status like 'table%'
----------------------------
查询结果
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 100   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 40    |
| Table_open_cache_misses    | 18    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.01 sec)

Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况。

###20.2.2 MySQL表级锁模式

1. 共享锁：同一表可以会话同时对表进行读操作，但是加锁期间，不允许对表进行写操作。
2. 独占锁：加锁期间，禁止其他会话对表进行读写操作；

###20.2.3 加锁语句
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM表显式加锁。
给 MyISAM 表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的
一致性读取。

/*加锁*/
lock table table_name read|write [local]

/*解锁*/
unlock tables;

要特别说明以下两点内容。

1. 并发插入情况 上面的例子在 LOCK TABLES 时加了“local”选项，其作用就是在满足 MyISAM 表并发插入条件的情况下，允许其他用户在表尾并发插入记录，有关 MyISAM 表的并发插入问题，在后面的章节中还会进一步介绍。
2. .MyISAM为什么能够避免死锁 在用 LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且 MySQL 不支持锁升级。也就是说，在执行 LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁。这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

###*20.2.4并发插入情况
MyISAM 表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

1. 当concurrent_insert设置为0时，不允许并发插入。
2. 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
3. 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

###20.2.5MyISAM的锁调度
MyISAM读写锁默认调度
MyISAM 存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为 MySQL认为写请求一般比读请求要重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节 MyISAM 的调度行为。

MyISAM读写锁调度配置

1. 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
2. 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
3. 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
4. MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

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

##20.3InnoDB锁问题
###20.3.1背景知识
1. 事务
事务的四属性（ACID）

1. 原子性（Atomicity）: 事物是一个原子操作单元，其对数据修改，要么全部执行，要么全不执行。
2. 一致性（consistent）: 在事物开始和结束时候，数据必须保持一致。这意味着所有相关的数据规则都必须应用于事物的修改，以保持事物的完整性；事物结束时，所有内部数据结构（如B树索引或双向链表）也都必须正确的。
3. 隔离性（Isolation）: 保证数据在不受外部并发操作影响的独立环境执行，这意味这事物处理过程中中间状态对外部是不可见的，反之亦然。
4. 持久性（Durable）: 事物完成之后，他对数据的修改是永久的，使系统出现故障也能保持。

###20.3.2获取InnoDB行锁竞争情况

show status like 'innodb_row_lock%'

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 45287 |
| Innodb_row_lock_time_avg      | 22643 |
| Innodb_row_lock_time_max      | 30062 |
| Innodb_row_lock_waits         | 2     |
+-------------------------------+-------+

innodb_row_lock_waits 和 innodb_row_time_avg 的值比较高，说明锁竞争严重。可通过设置InnoDB Monitors来进一步观察发生锁冲突的表，数据行等，并分析锁的竞争原因。

###20.3.3InnoDB的行锁模式及加锁方法

1. 共享锁（S）：允许一个事物去读一行，组织其他事物获取相同数据的排它锁。
2. 排它锁（X）：允许获得排它锁的事物更新数据，阻止其他事物获取相同数据的共享锁和排它锁。

另外InnoDB还提供两种内部使用的意向表锁。

1. 意向共享锁（IS）：事物打算将数据行加行共享锁，事物在给一个数据行加行行共享锁前，必须取得该表的IS锁。
2. 意向排他锁（IX）：事物打算给数据行加行排它锁，事物在给一个行加排他锁之前必须获得该表的IX锁。

加锁方式
共享锁（S） select * from table_name where … lock in share mode;
排他锁（X）select * from table_name where … for update;

注意
InnoDB 行锁是通过给索引上的索引项加锁实现的，因此只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表级锁。