mysql事务锁常用语句

-- 查看当前所有事务
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;

-- 全局只读锁
 Flush tables with read lock

-- 表锁
lock tables users write;
lock tables users read

-- 解表锁
unlock tables;

-- 读取锁lock in share mode
select * from user where id = 1  lock in share mode;
-- 写入锁 for update
select * from test_gap_table where number=13 for update;

基于锁的属性分类：共享锁、排他锁。

基于锁的状态分类：意向共享锁、意向排它锁

根据锁的粒度分类：全局锁、页锁、表级锁、行锁（记录锁、间隙锁、和临键锁），实际上的锁就这些，上面两种分类只是站在不同维度上看这些锁

参考链接：【Mysql】表锁 行锁 记录锁 间隙锁 - CJ-cooper - 博客园