说明
不太深入细节,mysql的数据库可以粗略划分为global、table、row三种锁
锁的种类
全局锁global lock
如何加锁
全局锁又称为库锁,即锁住整个db instance.
当我想让一个表read-only的时候,可以通过下面命令添加全局锁
Flush tables with read lock (FTWRL)
命令set global readonly = true
全局参数
Update,Create,ALter,Commit,事务等操作都会被锁住。
什么时候使用全局锁
使用mysqldump备份整库
- 为了避免备份过程中新数据写入,加全局锁让库read-only
- gobal locks会杜绝任何更新操作,业务可能因此中断。
myisam的备份只能通过家全居锁来避免数据写入。 对于innodb数据库来说可以添加 --single-transaction参数执行备份,而无需添加全局锁
主从同步时让slave只读
表锁 tablelock
T1,T2代表两个窗口,两个线程,用来模拟多线程操作下表锁的效果
lock table ${tablename} read
- insert、drop、truncate、rename、update 都不能执行
操作 | T1(lock user) | T2 | 说明 |
---|---|---|---|
select | 可以 | 可以 | 可读 |
insert\update\delete | 报错 | 卡死 | 其他线程等待T1释放锁 |
crop\rename\truncate | 报错 | 卡死 | 其他线程等待T1释放锁 |
unlock tables | - | 继续执行 |
mysql> lock table user read;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(username) values('zhangsan1');
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql> drop table user;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql> truncate table user;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql>
新的事物可以解除表锁,如下
mysql> lock table user read;
Query OK, 0 rows affected (0.00 sec)
# 添加读锁后不能插入
mysql> insert into user(username) values('zhangsan3');
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
# 当前线程下启动新的事物
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(username) values('zhangsan3');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 事物结束后表锁已经解除了
mysql> insert into user(username) values('zhangsan4');
Query OK, 1 row affected (0.00 sec)
lock table ${tablename} write
和read相比
- 其他线程只读
- 当前线程可读可写(在读锁模式下,所有线程都是只读的)
表锁总结
- unlock tables;只能释放当前用户session线程下的锁。即T2不能释放T1的锁,必须由T1自己执行unlock tables;
- lock table xxx read,加锁的对象是表,当前线程和其他线程都只能读取表内容,不能执行任何其他修改变更操作。在lock read模式下,其他线程卡死,直到T1释放了表锁,才能执行更新操作。
- lock table xxx write,当前线程可以读,可以写。其他线程只读
- T1开启事务后,锁自动释放
释放表锁的三种方法
- 在加锁的线程上执行 unlock tables;
- 在加锁线程上执行 begin开启新事物
- 退出exit或者断开线程会自动释放锁
show processlit ,kill 线程,不能释放锁,只能用来杀死等待的线程。
元数据锁 meta data lodk (MDL)
MDL是表锁的一种,我们不需要显式的使用,系统会自动添加。主要是为了避免表结构修改的时候能够正常读写数据。
- 增删改查数据的时候,MDL read lock。不互斥,其他线程还是可以同时增删改表数据。
- 改变表结构时候,MLD write lock或read-write lock 。互斥,防止其他线程修改表结构。等到write锁释放的时候才能继续。
- t1中开启一个事务,查询数据库结构。(此时user会加上MDL read lock)
- t2执行alter语句,发现卡死了。发现processlist中T2的alter在等待MDL锁。
- t1执行commit提交事务后。t2的alter语句正常执行
结果分析
- 当t1开启事务后,执行表操作(增删改查)都可以。系统会给user添加MDL read lock。 注意:单纯beign开启事务不加锁)
- 当t1事务提交前,t2可以执行增删改查,但是不能修改表结构。
- t1事务提交以后,user上的MDL释放。
- MDL锁不会超时,只要不提交事务,就会一直所这边表,业务可能因此崩溃。
如何排查死锁
如何解决MDL锁问题
通过上面分析,直到只要提交或者回滚事务就可以了,问题在于show processlist只能看到被阻塞的sql。并不能找到锁住表的事务元凶。
# 查询一下事务开启超过60s的 事务id
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
找到事务idtrx_mysql_thread_id,这里是12.然后show processlist找到这个id。
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
*************************** 1. row ***************************
trx_id: 421546431100768
trx_state: RUNNING
trx_started: 2022-04-23 12:42:44
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 12
trx_query: select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
| 8 | root | localhost | test | Query | 110 | Waiting for table metadata lock | alter table user add locaton3 varchar(20) |
| 12 | root | localhost | test | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
2 rows in set (0.00 sec)
kill 12 杀死这个进程即可。
实际场景中,可能存在多个进程。但最终目的是完结这些进程中的事务,通常只有一个元凶,干掉元凶即可。
如何解决死锁(待详细补充)
show processlist 干掉其中一个
查看锁的方法
show engine innodb status
------------
TRANSACTIONS
------------
Trx id counter 165918
Purge done for trx's n:o < 165917 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421546431100768, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
-- 查看当前所有事务
select * from information_schema.innodb_trx;
-- 查看正在锁的事务
select * from information_schema.innodb_locks;
-- 查看等待锁的事务
select * from information_schema.innodb_lock_waits;
-- 查看表锁
show open tables where In_use>0;
--查看死锁
show engine innodb status\G
--查看元数据锁
SELECT * FROM performance_schema.setup_instruments;
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
SELECT * FROM performance_schema.metadata_locks;