Mysql_innodb行锁检测

 

Mysql_innodb行锁检测手段

--常用命令：
mysql> show processlist\G

mysql> show engine innodb status\G

mysql> select * from innodb_locks\G

mysql> select * from innodb_lock_waits\G

mysql> select * from innodb_trx\G




行锁： 测试

--测试表
create table t(
a int(10) not null auto_increment,
primary key(a)
);

insert into t values();
insert into t select null from t;
insert into t select null from t;
insert into t select null from t;
insert into t select null from t;
insert into t select null from t;


--测试数据
mysql> select * from t;
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
|  7 |
|  8 |
|  9 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 28 |
| 29 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
| 37 |
| 38 |
| 39 |
| 40 |
| 41 |
| 42 |
| 43 |
+----+
32 rows in set (0.00 sec)




----------------------测试行锁
--窗口1  休眠600秒
mysql>  update t set a=sleep(600) where a=36;

--窗口2  更新相同的行 产生行锁
mysql> update t set a=666 where a=36;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

--窗口3  查看进程   进程1是阻塞的进程   进程2是挂起的进程 有updating等待
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: flydb
Command: Query
   Time: 322
  State: User sleep
   Info: update t set a=sleep(600) where a=36
*************************** 2. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: flydb
Command: Query
   Time: 4
  State: updating
   Info: update t set a=666 where a=36
*************************** 3. row ***************************
     Id: 3
   User: root
   Host: localhost
     db: information_schema
Command: Query
   Time: 0
  State: init
   Info: show processlist
3 rows in set (0.00 sec)



--查看状态 红字是锁信息    目前进程2挂起 是修改的表中 有x排它锁导致的
mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-08-10 16:16:08 7f1750ad1700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 7340 srv_idle
srv_master_thread log flush and writes: 7355
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 32
OS WAIT ARRAY INFO: signal count 32
Mutex spin waits 21, rounds 630, OS waits 15
RW-shared spins 20, rounds 577, OS waits 17
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 28.85 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 4415
Purge done for trx's n:o < 4412 undo n:o < 0 state: running but idle
History list length 15
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7f1750ad1700, query id 128 localhost root init
show engine innodb status
---TRANSACTION 4414, ACTIVE 48 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x7f1750b12700, query id 124 localhost root updating
update t set a=666 where a=36
------- TRX HAS BEEN WAITING 48 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 3 n bits 104 index `PRIMARY` of table `flydb`.`t` trx id 4414 lock_mode X locks rec but not gap waiting
Record lock, heap no 26 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000024; asc    $;;
1: len 6; hex 00000000112d; asc      -;;
2: len 7; hex a2000001540170; asc     T p;;

------------------
---TRANSACTION 4413, ACTIVE 57 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1, OS thread handle 0x7f1750b53700, query id 123 localhost root User sleep
update t set a=sleep(600) where a=36
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
169 OS file reads, 196 OS file writes, 101 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 207589, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1680113
Log flushed up to   1680113
Pages flushed up to 1680113
Last checkpoint at  1680113
0 pending log writes, 0 pending chkp writes
54 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 107315200; in additional pool allocated 0
Dictionary memory allocated 52162
Buffer pool size   6399
Free buffers       6206
Database pages     192
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 156, created 40, written 129
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 192, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 1755, id 139738067031808, state: sleeping
Number of rows inserted 288, updated 1, deleted 0, read 625
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)




--用innodb_lock%  查看锁信息
mysql> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 4414:7:3:26
lock_trx_id: 4414
  lock_mode: X
  lock_type: RECORD
lock_table: `flydb`.`t`
lock_index: PRIMARY
lock_space: 7
  lock_page: 3
   lock_rec: 26
  lock_data: 36
*************************** 2. row ***************************
    lock_id: 4413:7:3:26
lock_trx_id: 4413
  lock_mode: X
  lock_type: RECORD
lock_table: `flydb`.`t`
lock_index: PRIMARY
lock_space: 7
  lock_page: 3
   lock_rec: 26
  lock_data: 36
2 rows in set (0.00 sec)



--查看阻塞和挂起的进程，requesting%为挂起的进程，  blocking%为阻塞的进程
mysql> select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
requesting_trx_id: 4414
requested_lock_id: 4414:7:3:26
  blocking_trx_id: 4413
blocking_lock_id: 4413:7:3:26
1 row in set (0.00 sec)


--根据以上的trx_id 来对应以下的trx_id来找trx_mysql_thread_id  即为线程id  干掉即可
--查看被锁进程 trx_mysql_thread_id 为2 的进程 lock wait了，执行的语句为 update t set a=666 where a=36
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 4414
                 trx_state: LOCK WAIT
               trx_started: 2018-08-10 16:15:20
     trx_requested_lock_id: 4414:7:3:26
          trx_wait_started: 2018-08-10 16:15:20
                trx_weight: 2
       trx_mysql_thread_id: 2
                 trx_query: update t set a=666 where a=36
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         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: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 4413
                 trx_state: RUNNING
               trx_started: 2018-08-10 16:15:11
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 1
                 trx_query: update t set a=sleep(600) where a=36
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         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: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)




--杀掉阻塞的进程解决问题 或者等阻塞进程跑完；
--窗口1  杀掉mysql的进程号1
mysql> kill 1;
Query OK, 0 rows affected (0.00 sec)


--窗口2 挂起的进程2完成了
mysql> update t set a=666 where a=36;
Query OK, 1 row affected (10.65 sec)
Rows matched: 1  Changed: 1  Warnings: 0




--注：超过一定时间 挂起的进程会报错 如下挂起的进程2
mysql> update t set a=666 where a=36;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

--涉及参数 默认50秒，挂起等待超过该时间就报错
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)