17.MySQL8.0锁的查询与处理

总共执行3个语句，且都没有提交。

模拟DELTE 语句阻塞两个update 语句。
1.delete from test1;
2.update test1 set id=10;
3.update test1 set id=11;

1.查询当前事务。18在跑。17、19在等。
SELECT trx_state,trx_started,trx_mysql_thread_id,trx_rows_locked,trx_tables_locked FROM INFORMATION_SCHEMA.INNODB_TRX where trx_rows_locked>0 and trx_tables_locked >0;
+-----------+---------------------+---------------------+-----------------+-------------------+
| trx_state | trx_started | trx_mysql_thread_id | trx_rows_locked | trx_tables_locked |
+-----------+---------------------+---------------------+-----------------+-------------------+
| LOCK WAIT | 2022-03-18 16:43:33 | 19 | 1 | 1 |
| LOCK WAIT | 2022-03-18 16:43:23 | 17 | 1 | 1 |
| RUNNING | 2022-03-18 16:42:50 | 18 | 43 | 1 |
+-----------+---------------------+---------------------+-----------------+-------------------+

2.看持有锁的语句
可以通过如下方式
##持有锁的语句。导致锁的最初触发者。锁的肇事者。
mysql>
EVENT_ID=select connect_id(); 两个值相等。

SELECT THREAD_ID,EVENT_ID,EVENT_NAME,TIMER_END,TIMER_START,(LOCK_TIME/1000/1000) lock_time,SQL_TEXT,CURRENT_SCHEMA,OBJECT_TYPE ,OBJECT_SCHEMA
FROM performance_schema.events_statements_history WHERE
upper(SQL_TEXT) IS NOT NULL AND upper(SQL_TEXT) NOT LIKE 'SHOW%' AND upper(SQL_TEXT) NOT LIKE 'SELECT%'
AND (
upper(SQL_TEXT) LIKE 'CREATE%' OR upper(SQL_TEXT) LIKE 'ALTER%' OR upper(SQL_TEXT) LIKE 'DROP%'
OR upper(SQL_TEXT) LIKE 'UPDATE%' OR upper(SQL_TEXT) LIKE 'INSERT%' OR upper(SQL_TEXT) LIKE 'DELETE%'
)
AND thread_id IN( SELECT b.`THREAD_ID` FROM sys.`innodb_lock_waits` AS a , performance_schema.threads AS b
WHERE a.`blocking_pid` = b.`PROCESSLIST_ID`) ORDER BY timer_start ASC;

当前锁是因为一个delete语句没有提交。
+-----------+----------+----------------------+-------------------+-------------------+--------------+-------------------+----------------+-------------+---------------+
| THREAD_ID | EVENT_ID | EVENT_NAME | TIMER_END | TIMER_START | lock_time | SQL_TEXT | CURRENT_SCHEMA | OBJECT_TYPE | OBJECT_SCHEMA |
+-----------+----------+----------------------+-------------------+-------------------+--------------+-------------------+----------------+-------------+---------------+
| 78 | 11 | statement/sql/delete | 11666714534807000 | 11666714026282000 | 113.00000000 | delete from test1 | mydb | NULL | NULL |
+-----------+----------+----------------------+-------------------+-------------------+--------------+-------------------+----------------+-------------+---------------+
1 row in set (0.00 sec)

3.查看锁信息,正在被锁的事务。

SELECT LOCK_MODE,ENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,EVENT_ID,LOCK_TYPE,LOCK_STATUS,LOCK_DATA
FROM performance_schema.data_locks WHERE LOCK_STATUS='WAITING';

锁的类型是 X:排他锁。
+-----------+-----------------------+---------------+-------------+----------+-----------+-------------+----------------+
| LOCK_MODE | ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | EVENT_ID | LOCK_TYPE | LOCK_STATUS | LOCK_DATA |
+-----------+-----------------------+---------------+-------------+----------+-----------+-------------+----------------+
| X | 257840 | mydb | test1 | 11 | RECORD | WAITING | 0x000000000200 |
| X | 257835 | mydb | test1 | 12 | RECORD | WAITING | 0x000000000200 |
+-----------+-----------------------+---------------+-------------+----------+-----------+-------------+----------------+
2 rows in set (0.00 sec)

3.查看被锁的事务。
mysql> select * from information_schema.processlist where db is not null and info is not null AND INFO NOT LIKE 'SELECT%';
+----+------+-----------+------+---------+------+----------+------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+------+---------+------+----------+------------------------+
| 17 | root | localhost | mydb | Query | 239 | updating | update test1 set id=10 |
| 19 | root | localhost | mydb | Query | 229 | updating | update test1 set id=11 |
+----+------+-----------+------+---------+------+----------+------------------------+
2 rows in set (0.00 sec)

--
select connection_id();

kill 18;
-----------------------------------------------------------------------------------------------
现在我们杀掉 delete语句。发现update test1 set id=10 提交了。

1.查询当前事务。17在跑，19在等。
SELECT trx_state,trx_started,trx_mysql_thread_id,trx_rows_locked,trx_tables_locked FROM INFORMATION_SCHEMA.INNODB_TRX where trx_rows_locked>0 and trx_tables_locked >0;
+-----------+---------------------+---------------------+-----------------+-------------------+
| trx_state | trx_started | trx_mysql_thread_id | trx_rows_locked | trx_tables_locked |
+-----------+---------------------+---------------------+-----------------+-------------------+
| LOCK WAIT | 2022-03-18 16:43:33 | 19 | 1 | 1 |
| RUNNING | 2022-03-18 16:43:23 | 17 | 43 | 1 |
+-----------+---------------------+---------------------+-----------------+-------------------+
2 rows in set (0.00 sec)

2.引起锁是语句。
SELECT THREAD_ID,EVENT_ID,SQL_TEXT,TIMER_END,TIMER_START,(LOCK_TIME/1000/1000) lock_time,MESSAGE_TEXT,CURRENT_SCHEMA,OBJECT_TYPE ,OBJECT_SCHEMA,
NO_INDEX_USED ,NO_GOOD_INDEX_USED,NESTING_EVENT_ID , NESTING_EVENT_TYPE , NESTING_EVENT_LEVEL,STATEMENT_ID
FROM performance_schema.events_statements_history WHERE
upper(SQL_TEXT) IS NOT NULL AND upper(SQL_TEXT) NOT LIKE 'SHOW%' AND upper(SQL_TEXT) NOT LIKE 'SELECT%'
AND (
upper(SQL_TEXT) LIKE 'CREATE%' OR upper(SQL_TEXT) LIKE 'ALTER%' OR upper(SQL_TEXT) LIKE 'DROP%'
OR upper(SQL_TEXT) LIKE 'UPDATE%' OR upper(SQL_TEXT) LIKE 'INSERT%' OR upper(SQL_TEXT) LIKE 'DELETE%'
) AND MESSAGE_TEXT IS NOT NULL
AND thread_id IN( SELECT b.`THREAD_ID` FROM sys.`innodb_lock_waits` AS a , performance_schema.threads AS b
WHERE a.`blocking_pid` = b.`PROCESSLIST_ID`) ORDER BY timer_start ASC;

--此时锁的持有者变成 ：update;
+-----------+----------+------------------------+-------------------+-------------------+--------------+--------------------------------------------+----------------+-------------+---------------+---------------+--------------------+------------------+--------------------+---------------------+--------------+
| THREAD_ID | EVENT_ID | SQL_TEXT | TIMER_END | TIMER_START | lock_time | MESSAGE_TEXT | CURRENT_SCHEMA | OBJECT_TYPE | OBJECT_SCHEMA | NO_INDEX_USED | NO_GOOD_INDEX_USED | NESTING_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_LEVEL | STATEMENT_ID |
+-----------+----------+------------------------+-------------------+-------------------+--------------+--------------------------------------------+----------------+-------------+---------------+---------------+--------------------+------------------+--------------------+---------------------+--------------+
| 77 | 10 | update test1 set id=10 | 11963995973520000 | 11675154148395000 | 138.00000000 | Rows matched: 42 Changed: 42 Warnings: 0 | mydb | NULL | NULL | 0 | 0 | NULL | NULL | 0 | 1498 |
+-----------+----------+------------------------+-------------------+-------------------+--------------+--------------------------------------------+----------------+-------------+---------------+---------------+--------------------+------------------+--------------------+---------------------+--------------+
1 row in set (0.00 sec)

3.查看锁信息,正在被锁的事务。
--被锁的剩余一个了。
SELECT LOCK_MODE,ENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,EVENT_ID,LOCK_TYPE,LOCK_STATUS,LOCK_DATA FROM performance_schema.data_locks WHERE LOCK_STATUS='WAITING';
--依然是排他锁。
+-----------+-----------------------+---------------+-------------+----------+-----------+-------------+----------------+
| LOCK_MODE | ENGINE_TRANSACTION_ID | OBJECT_SCHEMA | OBJECT_NAME | EVENT_ID | LOCK_TYPE | LOCK_STATUS | LOCK_DATA |
+-----------+-----------------------+---------------+-------------+----------+-----------+-------------+----------------+
| X | 257840 | mydb | test1 | 11 | RECORD | WAITING | 0x000000000200 |
+-----------+-----------------------+---------------+-------------+----------+-----------+-------------+----------------+
1 row in set (0.00 sec)

4.查看被锁的事务。被锁的事务变成第二个UPDATE;

select * from information_schema.processlist where db is not null and info is not null AND INFO NOT LIKE 'SELECT%';

+----+------+-----------+------+---------+------+----------+------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+------+---------+------+----------+------------------------+
| 19 | root | localhost | mydb | Query | 371 | updating | update test1 set id=11 |
+----+------+-----------+------+---------+------+----------+------------------------+
1 row in set (0.00 sec)