1 MySQL锁

1.1 表锁

• 开销小，加锁快
• 不会出现死锁
• 锁定粒度大，发生锁冲突的概率最高，并发度最低

1.2 行锁

• 开销大，加锁慢
• 会出现死锁
• 锁定粒度小，发生锁冲突的概率最低，并发度最高

1.3 页锁

• 开销和加锁时间介于表锁和行锁之间
• 会出现死锁
• 锁定粒度介于表锁和行锁之间，并发度一般

1.4 引擎与锁

• MyISAM和MEMORY支持表锁
• BDB支持页锁,也支持表锁
• Innodb既支持行锁，也支持表锁，默认行锁

1.5 查询表锁争用情况

检查table_locks_waited和table_locks_immediate状态变量分析

• table_locks_immediate : 可以立即获取锁的次数
• table_locks_waited : 不能立即获取锁，需要等待锁的次数
• 
• 
• table_locks_waited 的值越高，则说明存在严重的表级锁的争用情况

2 MyISAM的表锁

ＭySQL的表锁有两种模式

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

2.1 表锁兼容性

锁模式的兼容：

ＭyISAM表的读操作不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求。

ＭyISAM表的写操作会阻塞其他用户对同一表的读和写请求。

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

2.2 如何加表锁

对于 MyISAM 引擎

• 执行select前，会自动给涉及的所有表加 读
• 执行更新（update,delete,insert)会自动给涉及到的表加 写
• 无需直接显式用lock table命令。

对于给MyISAM显式加锁，一般是为了模拟事务操作，实现对某一个时间点多个表一致性读取

2.2.1 实例

• 订单表 orders
  记录各订单的总金额total
  
• 订单明细表 order_detail
  记录各订单每一产品的金额小计subtotal
  

假设需检查这两个表的金额合计是否相符，可能执行如下SQL

Select sum(total) from orders;
Select sum(subtotal) from order_detail;

如果不先给这两个表加锁，就可能产生错误的结果：因为第一条语句执行过程中，order_detail表可能已发生改变：

因此,正确写法应该如下

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

在Lock tables时加的 local 选项，以满足MyISAM表并发插入时，允许其他用户在表尾插入记录。

在用Lock tables给表显式加表锁时，必须同时取得所有涉及表的锁，并且MySQL不支持锁升级：即在执行Lock tables后，只能访问显式加锁的这些表，不能访问未加锁的表；

同时，如果加的是读锁，那么只能执行查询，而不能执行更新。在自动加锁情况下也这样，MySQL会一次获得SQL语句所需要的全部锁，这也正是MyISAM表不会死锁的原因。

2.3 tips

当使用lock tables时,不仅需要一次锁定用到的所有表

且同一表在SQL语句中出现多少次,就要通过与SQL语句中别名锁多少次

lock table actor read

会提示错误

select a.first_name.....

需要对别名分别锁定

lock table actor as a read,actor as b read;

3 MyISAM的并发锁

在一定条件下，MyISAM也支持并发插入和读取

3.1 系统变量 : concurrent_insert

控制其并发插入的行为,其值分别可以为

• 0 不允许并发插入,所有插入对表加互斥锁
• 1 只要表中无空洞,就允许并发插入.
  MyISAM允许在一个读表的同时，另一个进程从表尾插入记录(MySQL的默认设置)
• 2 无论MyISAM表中有无空洞,都强制在表尾并发插入记录
  若无读线程,新行插入空洞中
• 可以利用MyISAM的并发插入特性，来解决应用中对同表查询和插入的锁争用

例如,将concurrent_insert系统变量设为2，总是允许并发插入;

同时,通过定期在系统空闲时段执行OPTIONMIZE TABLE语句来整理空间碎片，收到因删除记录而产生的中间空洞

删除操作不会重整整个表,只是把 行 标记为删除,在表中留下空洞

MyISAM倾向于在可能时填满这些空洞,插入时就会重用这些空间,无空洞则把新行插到表尾

3.2 MyISAM的锁调度

MyISAM的读和写锁互斥,读操作串行的

一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同表的写锁，MySQL如何处理呢？

写进程先获得锁!!!

不仅如此,即使读进程先请求先到锁等待队列,写请求后到,写锁也会插到读请求之前!!!

这是因为MySQL认为写请求一般比读请求重要

这也正是MyISAM表不适合有大量更新 / 查询操作应用的原因

大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞

幸好,我们可以通过一些设置来调节MyISAM的调度行为

指定启动参数low-priority-updates

使MyISAM引擎默认给予读请求以优先权利

执行命令SET LOW_PRIORITY_UPDATES=1

使该连接发出的更新请求优先级降低

指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性

降低该语句的优先级

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

另外，MySQL也提供了一种折中的办法来调节读写冲突;

即给系统参数max_write_lock_count设置一个合适的值;

当一个表的读锁达到这个值后，MySQL便暂时将写请求的优先级降低，给读进程一定获得锁的机会