MySQL 解锁与锁表
Mysql三种锁机制
MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);
BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;
InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
查询表级锁争用情况
可以通过table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定情况,使用输入以下命令来获得。
1 | show status like 'table%'; |
- table_locks_waited:表示表级锁争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在较严重的表级锁竞争情况。
- table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1
查询行级锁争用情况
可以通过innodb_row_lock状态变量来分析系统上的行锁竞争情况,使用输入以下命令来获得。
1 | show status like 'innodb_row_lock%'; |
- innodb_row_lock_current_waits:当前正在等待锁定的数量
- innodb_row_lock_time:从系统启动到现在锁定总时间长度
- innodb_row_lock_time_avg:每次等待所花平均时间
- innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
- innodb_row_lock_time_waits:系统启动后到现在总共等待的次数
对于这5个状态变量,比较重要的是:
- innodb_row_lock_time_avg (等待平均时长)
- innodb_row_lock_waits (等待总次数)
- innodb_row_lock_time(等待总时长)
尤其是当等待次数很高,而且每次等待时长也不小的时候
锁表
读锁
锁定数据表,避免在备份过程中,表被更新
1 | lock tables tbl_name read; |
写锁
1 | lock tables tbl_name write; |
解锁
1、找到锁进程,然后kill id
1 | show processlist; |
2、直接解锁
1 | unlock tables; |