mysql 验证锁表及处理

mysql 验证锁表及处理

1、新建表

CREATE TABLE t1 (
id int(11) NOT NULL,
val varchar(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-----
INSERT into t1(id,val) VALUES (12,'888');
-----
select * from t1;

2、测试lock加读表

--加读表
lock table t1 READ;
​
-- 然后使用update语句操作 ：
​
update t1 set val= '121' where id=12;
​
-- 解锁
unlock tables;
​
1、查询进程
show full processlist 查询到相对应的进程===然后 kill id
​
2、查询是否锁表（查看正在被锁定的的表）
show OPEN TABLES where In_use > 0;
​
show status like '%lock%';

3、测试lock加写锁

-- 加写锁
LOCK TABLE t1 WRITE;
​
-- 另外开一个线程,尝试运行更新
​
update t1 set val= '1333' where id=12;
​
unlock tables;
​
1、查询进程
show full processlist 查询到相对应的进程===然后 kill id
​
2、查询是否锁表（查看正在被锁定的的表）
show OPEN TABLES where In_use > 0;
​
show status like '%lock%';

4、测试用事务时候锁的表现

-- 事务测试
start transaction;
update t1 set val= '1261' where id=12;
​
-- 另外开一个线程,运行事务
​
start transaction;
update t1 set val= '176' where id=12;
​
-- 提交事务
COMMIT;
​
1、查询进程
show full processlist 查询到相对应的进程===然后 kill id
​
2、查询是否锁表（查看正在被锁定的的表）
show OPEN TABLES where In_use > 0;
​
show status like '%lock%';
​
3、查看被锁的表
select * from INFORMATION_SCHEMA.INNODB_TRX;
​
4、查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
​
5、查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

5、后续进一步分析事务锁

innodb_trx           ## 当前运行的所有事务
innodb_locks         ## 当前出现的锁
innodb_lock_waits    ## 锁等待的对应关系
​
innodb_trx表列信息:
trx_id： 唯一事务id号;
TRX_WEIGHT：事务的高度;
TRX_STATE： 事务的执行状态，值一般分为：RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING
RX_REQUESTED_LOCK_ID:如果trx_state是lockwait,显示事务当前等待锁的id，不是则为空。想要获取锁的信息，
根据该lock_id，以innodb_locks表中lock_id列匹配条件进行查询，获取相关信息。
TRX_WAIT_STARTED：如果trx_state是lockwait,该值代表事务开始等待锁的时间；否则为空。
TRX_MYSQL_THREAD_ID：mysql线程id。想要获取该线程的信息，根据该thread_id，以INFORMATION_SCHEMA.PROCESSLIST表的id列为匹配条件进行查询。
TRX_QUERY：事务正在执行的sql语句。
​
INNODB_LOCKS表列信息:
desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field       | Type                | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id     | varchar(81)         | NO   |     |         |       |#锁ID
| lock_trx_id | varchar(18)         | NO   |     |         |       |#拥有锁的事务ID
| lock_mode   | varchar(32)         | NO   |     |         |       |#锁模式
| lock_type   | varchar(32)         | NO   |     |         |       |#锁类型
| lock_table  | varchar(1024)       | NO   |     |         |       |#被锁的表
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被锁的索引
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的表空间号
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的页号
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的记录号
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被锁的数据
+————-+———————+——+—–+———+——-+
​
innodb_lock_waits 表列信息:
desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field             | Type        | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO   |     |         |       |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO   |     |         |       |#请求锁的锁ID
| blocking_trx_id   | varchar(18) | NO   |     |         |       |#当前拥有锁的事务ID
| blocking_lock_id  | varchar(81) | NO   |     |         |       |#当前拥有锁的锁ID
+——————-+————-+——+—–+———+——-+

6、不开启事务，测试update更新

首先不开启事务，测试update更新
​
mysql> update tx1 set c1='ccc3' where id=3 ;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
​
开启另外一个窗口查看：innodb_trx 发现事务为空
​
mysql> select * from innodb_trx \G 
Empty set (0.01 sec)
​

7、开启事务，测试单事务update更新

开启事务，测试update更新
​
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
​
mysql> update tx1 set c1='ccc4' where id=3 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
​
开启另外一个窗口查看：innodb_trx 事务
​
mysql> select * from innodb_trx \G
*************************** 1. row ***************************
trx_id: 1297
trx_state: RUNNING
trx_started: 2021-12-10 00:03:34
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 5
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
​
innodb_locks 和innodb_lock_waits信息也是空的，说明当前没有锁和锁等待。 
​
之后在第一个会话窗口里提交事务commit后，第二个窗口innodb_trx没运行事务。
​

8、开启事务，测试双线程事务update更新

-- 事务测试
start transaction;
update t1 set val= '1261' where id=12;
​
-- 另外开一个线程,运行事务
start transaction;
update t1 set val= '176' where id=12;
​
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ;
SELECT * FROM information_schema.innodb_locks;
​
--innodb_trx 事务结果
mysql> select * from INFORMATION_SCHEMA.INNODB_TRX \G;
*************************** 1. row ***************************
                    trx_id: 982234
                 trx_state: LOCK WAIT   ###########锁等待状态，被锁了
               trx_started: 2023-01-29 00:15:10
     trx_requested_lock_id: 982234:2563:3:2
          trx_wait_started: 2023-01-29 00:16:29
                trx_weight: 2
       trx_mysql_thread_id: 3
                 trx_query: update t1 set val= '176' where id=12
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 982233
                 trx_state: RUNNING      ##########运行，锁源头
               trx_started: 2023-01-29 00:15:03
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 5
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
​
--可看到锁的情况
mysql> SELECT * FROM information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 982234:2563:3:2 | 982234      | X         | RECORD    | `shbw`.`t1` | PRIMARY    |       2563 |         3 |        2 | 12        |
| 982233:2563:3:2 | 982233      | X         | RECORD    | `shbw`.`t1` | PRIMARY    |       2563 |         3 |        2 | 12        |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
​
​
--可看到锁等待情况
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 982234            | 982234:2563:3:2   | 982233          | 982233:2563:3:2  |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

show full processlist

【补充：】

1、定位到源头锁之后，着急处理的话可以kill 掉会话；
2、不着急的话，定位到源头锁之后，分析源头锁的状态，看看trx_operation_state；
3、重要的重要，一定要结合业务场景去分析锁，捋清楚是谁堵塞了谁，应该去分析谁，处理谁。