mysql自动锁表_mysql锁表处理

mysql> select * fromINNODB_LOCKS;+----------------------+-------------+-----------+-----------+-------------------------+-----------------+------------+-----------+----------+-----------+

| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |

+----------------------+-------------+-----------+-----------+-------------------------+-----------------+------------+-----------+----------+-----------+

| 259690955:466101:3:2 | 259690955 | X | RECORD | `mycommunity_cc`.`tmpa` | GEN_CLUST_INDEX | 466101 | 3 | 2 | NULL |

| 259690812:466101:3:2 | 259690812 | X | RECORD | `mycommunity_cc`.`tmpa` | GEN_CLUST_INDEX | 466101 | 3 | 2 | NULL |

+----------------------+-------------+-----------+-----------+-------------------------+-----------------+------------+-----------+----------+-----------+

2 rows in set (0.01sec)

mysql>show processlist;+---------+-------------+----------------------+--------------------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+---------+-------------+----------------------+--------------------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+

| 5256016 | yunshequ | 113.98.201.26:45118 | mycommunity_cc | Query | 4 | updating | update tmpa set id = 1 |

| 5256554 | yunshequ | 113.98.201.26:45120 | mycommunity_cc | Sleep | 264 | | NULL |mysql> select * fromINNODB_TRX ;+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+

| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |

+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+

| 259690955 | LOCK WAIT | 2020-09-07 00:50:34 | 259690955:466101:3:2 | 2020-09-07 00:56:24 | 2 | 5256016 | update tmpa set id = 1 | fetching rows | 1 | 1 | 2 | 1184 | 8 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |

| 259690812 | RUNNING | 2020-09-07 00:50:26 | NULL | NULL | 3 | 5256554 | NULL | NULL | 0 | 0 | 2 | 360 | 1 | 1 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |

| 329414178302056 | RUNNING | 2020-09-06 21:59:19 | NULL | NULL | 0 | 5118894 | NULL | NULL | 0 | 0 | 0 | 360 | 0 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |

| 329411454949480 | RUNNING | 2020-09-06 21:58:26 | NULL | NULL | 0 | 5118227 | NULL | NULL | 0 | 0 | 0 | 360 | 0 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 10000 | 0 | 0 |mysql> SELECT * FROMinformation_schema.innodb_lock_waits;+-------------------+----------------------+-----------------+----------------------+-------------------+-----------------+

| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | requesting_thd_id | blocking_thd_id |

+-------------------+----------------------+-----------------+----------------------+-------------------+-----------------+

| 259690955 | 259690955:466101:3:2 | 259690812 | 259690812:466101:3:2 | 5256016 | 5256554 |

+-------------------+----------------------+-----------------+----------------------+-------------------+-----------------+

SELECT * FROMinformation_schema.innodb_lock_waits; 锁等待的对应关系SELECT * FROMinformation_schema.innodb_locks; 当前出现的锁SELECT * FROMinformation_schema.innodb_trx; 当前运行的所有事务

SHOW ENGINE INNODB STATUS;

https://blog.csdn.net/sanbingyutuoniao123/article/details/77878601