1 MySQL锁
1.1 表锁
- 开销小，加锁快
- 不会出现死锁
- 锁定粒度大，发生锁冲突的概率最高，并发度最低
1.2 行锁
- 开销大，加锁慢
- 会出现死锁
- 锁定粒度小，发生锁冲突的概率最低，并发度最高
1.3 页锁
- 开销和加锁时间介于表锁和行锁之间
- 锁定粒度介于表锁和行锁之间，并发度一般
1.4 引擎与锁
-
MyISAM
支持表锁
- BDB支持页锁,也支持表锁
-
Innodb
1.5 查询表锁争用情况
检查
table_locks_waited
和
table_locks_immediate
状态变量分析
- table_locks_immediate : 可以立即获取锁的次数
- table_locks_waited : 不能立即获取锁，需要等待锁的次数
-
MySQL的行锁、表锁、间隙锁详解（上）1 MySQL锁2 MyISAM的表锁3 MyISAM的并发锁 -
MySQL的行锁、表锁、间隙锁详解（上）1 MySQL锁2 MyISAM的表锁3 MyISAM的并发锁 - table_locks_waited 的值越高，则说明存在严重的表级锁的争用情况
2 MyISAM的表锁
ＭySQL的表锁有两种模式
- 表共享读锁（Table Read Lock）
- 表独占写锁（Table Write Lock）
2.1 表锁兼容性
锁模式的兼容：
| 是否兼容 | 请求none | 请求读锁 | 请求写锁 |
| 当前处于读锁 | 是 | 否 | |
| 当前处于写锁 |
Ｍ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便暂时将写请求的优先级降低，给读进程一定获得锁的机会