MySQL中的锁

1，查看元数据锁
3306>[(none)]>select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS,OWNER_THREAD_ID,OWNER_EVENT_ID  from performance_schema.metadata_locks;
+-------------+--------------------+----------------+--------------+---------------+-------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE    | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+--------------+---------------+-------------+-----------------+----------------+
| TABLE       | world              | stu            | SHARED_WRITE | TRANSACTION   | GRANTED     |           17615 |            101 |
| TABLE       | performance_schema | metadata_locks | SHARED_READ  | TRANSACTION   | GRANTED     |           18835 |             14 |
| TABLE       | world              | stu            | SHARED_READ  | TRANSACTION   | GRANTED     |           17615 |            100 |
+-------------+--------------------+----------------+--------------+---------------+-------------+-----------------+----------------+
3 rows in set (0.00 sec)

2，查看意向锁和行锁

3306>[world]>select THREAD_ID,ENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|     17615 | world         | stu         | NULL       | TABLE     | IX            | GRANTED     | NULL      |
|     17615 | world         | stu         | idx_age    | RECORD    | X             | GRANTED     | 11, 1     |
|     17615 | world         | stu         | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
|     17615 | world         | stu         | idx_age    | RECORD    | X,GAP         | GRANTED     | 22, 2     |
+-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+

 3，查看持有元数据锁的连接ID

3306>[world]>select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS,OWNER_THREAD_ID,OWNER_EVENT_ID  from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+---------------+-------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+---------------+-------------+-----------------+----------------+
| TABLE       | world              | stu            | SHARED_READ | TRANSACTION   | GRANTED     |              54 |            154 |
| TABLE       | performance_schema | metadata_locks | SHARED_READ | TRANSACTION   | GRANTED     |              56 |             40 |
+-------------+--------------------+----------------+-------------+---------------+-------------+-----------------+----------------+
2 rows in set (0.00 sec)

3306>[world]>select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PARENT_THREAD_ID from performance_schema.threads where THREAD_ID=54;
+-----------+----------------+------------------+------------------+
| THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PARENT_THREAD_ID |
+-----------+----------------+------------------+------------------+
|        54 |             16 | root             |             NULL |
+-----------+----------------+------------------+------------------+
1 row in set (0.00 sec)

 

4.排查元数据锁

SELECT
locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_ID AS granted_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT
thread_id,
group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE
t1.granted_thread_id = t2.thread_id \G

*************************** 1. row ***************************
locked_schema: t_100w
locked_table: t100w
locked_type: Metadata Lock
waiting_processlist_id: 2055
waiting_age: 571
waiting_query: alter table t100w add c4 int ,ALGORITHM=COPY
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 2128
blocking_age: 5
blocking_query: select * from t100w where id=100;select * from t100w where id=100;select * from t100w where id=101;select * from t100w where id=99;select * from t100w where id=99
sql_kill_blocking_connection: KILL 2128
1 row in set, 1 warning (0.00 sec)