mysql锁机制学习笔记行锁表锁定位

wzf0072

229

10页

3次

2022-09-23

免费下载

https://zhuanlan.zhihu.com/p/75595737

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 : 不能立即获取锁，需要等待锁的次数

mysql> show status like '%table%';

+-----------------------------------------+---------+

| Variable_name | Value |

+-----------------------------------------+---------+

| Com_alter_table | 0 |

| Com_alter_tablespace | 0 |

| Com_create_table | 0 |

| Com_drop_table | 0 |

| Com_lock_tables | 0 |

| Com_rename_table | 0 |

| Com_show_create_table | 0 |

| Com_show_open_tables | 0 |

| Com_show_table_status | 0 |

| Com_show_tables | 0 |

| Com_unlock_tables | 0 |

| Created_tmp_disk_tables | 0 |

| Created_tmp_tables | 0 |

| Innodb_undo_tablespaces_total | 2 |

| Innodb_undo_tablespaces_implicit | 2 |

| Innodb_undo_tablespaces_explicit | 0 |

| Innodb_undo_tablespaces_active | 2 |

| Open_table_definitions | 617 |

| Open_tables | 5860 |

| Opened_table_definitions | 0 |

| Opened_tables | 0 |

| Performance_schema_table_handles_lost | 0 |

| Performance_schema_table_instances_lost | 0 |

| Performance_schema_table_lock_stat_lost | 0 |

| Replica_open_temp_tables | 0 |

| Slave_open_temp_tables | 0 |

| Table_locks_immediate | 4099671 |

| Table_locks_waited | 0 |

| Table_open_cache_hits | 0 |

| Table_open_cache_misses | 0 |

| Table_open_cache_overflows | 0 |

+-----------------------------------------+---------+

31 rows in set (3.13 sec)

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

这时,如果不先给这两个表加锁,就可能产生错误的结果;

因为第一条语句执行过程中,order_detail 表可能已经发生了改变.

因此,正确写法应该如下

2.2.2 注意点

上面的例子在 LOCK TABLES 时加了‘local’选项，其作用就是在满足 MyISAM 表并发插入条件的情况下，允

许其他用户在表尾插入记录

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及表的锁，并且 MySQL 支持锁升级; 也就是说，

在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那

么只能执行查询操作，而不能执行更新操作其实，在自动加锁的情况下也基本如此，MySQL 会一次获得 SQL

语句所需要的全部锁.这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因

| session1 | session2 |

|:----|:----|

| 获得表 film_text 的读锁 lock table film_text read; | | | 可 select * from

film_text | 可 select * from film_text |

| 不能查询没有锁定的表 :select * from film | 可查询/更新未锁定的表: select * from film

| 插入或更新锁定表会提示错误 update...from film_text | 更新锁定表会等待 update...from

film_text |

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 便暂时将写请求的优先级降低，给读进程一定获得锁的机会

of 10

免费下载

mysql锁

相关文档

评论