MySQL 锁表与解锁步骤

-- 1.使用大于0表
SHOW OPEN TABLES WHERE `Database` = 'test' AND In_use > 0 ;
-- 2.request_trx_id请求锁 与 blocking_trx_id产生锁原因
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

SELECT trx_id,trx_mysql_thread_id  FROM information_schema.innodb_trx ;

-- 3.被锁语句id
    SELECT 
      NOW(),
      (
        UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)
      ) diff_sec,
      b.id,-- 被锁语句id
      b.user,
      b.host,
      b.db,
      c.lock_type,
      c.lock_table,
      c.lock_index 
    FROM
      information_schema.innodb_trx a 
      INNER JOIN information_schema.PROCESSLIST b 
        ON a.TRX_MYSQL_THREAD_ID = b.id 
      INNER JOIN information_schema.INNODB_LOCKS c 
        ON a.trx_requested_lock_id = c.lock_id ;
        
-- 4.查看正在锁的事务,表名，锁状态
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
-- 5.查看id对应的SQL
SHOW FULL PROCESSLIST;

SELECT b.processlist_id,a.thread_id,a.sql_text FROM 
performance_schema.events_statements_current a, performance_schema.threads b 
WHERE a.thread_id=b.thread_id

-- 6.杀掉进程
KILL ??


-- 一步到位

SELECT * FROM sys.`innodb_lock_waits`;


-- 以下语句适用5.6（含有performance_schema.events_statements_current）以上版本
SELECT 
  b.`trx_mysql_thread_id` 被锁id,
  (SELECT 
    a.sql_text 
  FROM
    performance_schema.events_statements_current a,
    performance_schema.threads b 
  WHERE a.thread_id = b.thread_id 
    AND b.processlist_id = b.`trx_mysql_thread_id`) 被锁SQL,
  d.`lock_table` 被锁表,
  c.`trx_mysql_thread_id` 锁表id, -- 杀掉 kill ??
  (SELECT 
    a.sql_text 
  FROM
    performance_schema.events_statements_current a,
    performance_schema.threads b 
  WHERE a.thread_id = b.thread_id 
    AND b.processlist_id = c.`trx_mysql_thread_id`) 锁表SQL,
    e.`lock_table` 锁表
FROM
  INFORMATION_SCHEMA.INNODB_LOCK_WAITS a 
  LEFT JOIN information_schema.innodb_trx b 
    ON a.`requesting_trx_id` = b.`trx_id` 
  LEFT JOIN information_schema.innodb_trx c 
    ON a.`blocking_trx_id` = c.`trx_id` 
  LEFT JOIN information_schema.INNODB_LOCKS d 
    ON a.`requesting_trx_id` = d.`lock_trx_id` 
  LEFT JOIN information_schema.INNODB_LOCKS e 
    ON a.`blocking_trx_id` = e.`lock_trx_id`  ;

--适用于5.7及以上版本
select t2.PROCESSLIST_ID,from_unixtime(unix_timestamp(now())-t4.time) START_TIME,t1.*,t3.sql_text from 
(
SELECT
	OBJECT_SCHEMA,OBJECT_NAME,LOCK_STATUS,OWNER_THREAD_ID 
FROM
	`performance_schema`.metadata_locks
  WHERE
  OWNER_THREAD_ID != sys.ps_thread_id (CONNECTION_id()) 
and OBJECT_SCHEMA='test'
) t1 
left join `performance_schema`.threads t2 on t1.OWNER_THREAD_ID=t2.THREAD_ID
left join performance_schema.events_statements_current t3 on t1.OWNER_THREAD_ID=t3.THREAD_ID
left join information_schema.`PROCESSLIST` t4 on t4.ID=t2.PROCESSLIST_ID
order by t1.OBJECT_NAME

MySQL 5.7版本
锁状态LOCK_STATUS:PENDING
全局读锁、MDL锁、表级锁
select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connetion_id());
--语句
select * from performance_schema.events_statements_current where thread_id=?;

MySQL 8.0版本
锁状态LOCK_STATUS:WAITING
行级锁
select * from performance_schema.data_locks