mysql 行鎖排查

標籤：

<pre name="code" class="html">mysql 鎖表:隔離等級使用RR:mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;+-----------------------+-----------------+| @@GLOBAL.tx_isolation | @@tx_isolation  |+-----------------------+-----------------+| REPEATABLE-READ       | REPEATABLE-READ SESSION A:mysql> show full processlist;+----+------+-----------+------+---------+------+-------+-----------------------+| Id | User | Host      | db   | Command | Time | State | Info                  |+----+------+-----------+------+---------+------+-------+-----------------------+|  1 | root | localhost | NULL | Query   |    0 | init  | show full processlist |+----+------+-----------+------+---------+------+-------+-----------------------+1 row in set (0.02 sec)mysql> insert into test values(1,'a');Query OK, 1 row affected (0.00 sec)mysql> select * from test;+------+------+| id   | name |+------+------+|    1 | a    |+------+------+1 row in set (0.00 sec)mysql>  show full processlist;+----+------+-----------+--------+---------+------+-------+-----------------------+| Id | User | Host      | db     | Command | Time | State | Info                  |+----+------+-----------+--------+---------+------+-------+-----------------------+|  1 | root | localhost | DEVOPS | Query   |    0 | init  | show full processlist ||  2 | root | localhost | DEVOPS | Sleep   |    8 |       | NULL                  |+----+------+-----------+--------+---------+------+-------+-----------------------+2 rows in set (0.00 sec)mysql> update test set name='b' where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql>  show full processlist;+----+------+-----------+--------+---------+------+-------+-----------------------+| Id | User | Host      | db     | Command | Time | State | Info                  |+----+------+-----------+--------+---------+------+-------+-----------------------+|  1 | root | localhost | DEVOPS | Query   |    0 | init  | show full processlist ||  2 | root | localhost | DEVOPS | Sleep   |   34 |       | NULL                  |+----+------+-----------+--------+---------+------+-------+-----------------------+2 rows in set (0.00 sec)SESSION B:此時HANG：mysql>mysql> update test set name='c100' where id=1;SESSION B的資訊:mysql>  show full processlist;+----+------+-----------+--------+---------+------+----------+----------------------------------------+| Id | User | Host      | db     | Command | Time | State    | Info                                   |+----+------+-----------+--------+---------+------+----------+----------------------------------------+|  1 | root | localhost | DEVOPS | Query   |    0 | init     | show full processlist                  ||  2 | root | localhost | DEVOPS | Query   |    2 | updating | update test set name='c100' where id=1 |+----+------+-----------+--------+---------+------+----------+----------------------------------------+2 rows in set (0.00 sec)此時SESION B也就是Id=2 被堵塞,一直在更新類比 Id=1擷取鎖:mysql>  show full processlist;+----+------+----------------------+------+---------+------+-------+-----------------------+| Id | User | Host                 | db   | Command | Time | State | Info                  |+----+------+----------------------+------+---------+------+-------+-----------------------+|  7 | root | 115.236.160.82:53073 | NULL | Sleep   |  184 |       | NULL                  ||  8 | root | 115.236.160.82:53074 | NULL | Sleep   |  184 |       | NULL                  ||  9 | root | localhost            | NULL | Query   |    0 | init  | show full processlist |+----+------+----------------------+------+---------+------+-------+-----------------------+3 rows in set (0.00 sec)mysql> select * from test;+------+------+| id   | name |+------+------+|    1 | b    ||    2 | b    ||    2 | b    |+------+------+3 rows in set (0.00 sec)SELECT     r.trx_state wating_trx_state,    r.trx_id waiting_trx_id,    r.trx_mysql_thread_Id waiting_thread,    r.trx_query waiting_query,    b.trx_state blocking_trx_state,    b.trx_id blocking_trx_id,    b.trx_mysql_thread_id blocking_thread,    b.trx_query blocking_queryFROM    information_schema.innodb_lock_waits w        INNER JOIN    information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id        INNER JOIN    information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id# wating_trx_state, waiting_trx_id, waiting_thread, waiting_query,                             blocking_trx_state, blocking_trx_id, blocking_thread, blocking_query'LOCK WAIT',               '2332',          '10', 'update test set name=\'aabbcc100\' where id=1', 'RUNNING',          '2331',        '9',            NULLmysql> desc information_schema.innodb_lock_waits    -> ;+-------------------+-------------+------+-----+---------+-------+| Field             | Type        | Null | Key | Default | Extra |+-------------------+-------------+------+-----+---------+-------+| requesting_trx_id | varchar(18) | NO   |     |         |       || requested_lock_id | varchar(81) | NO   |     |         |       || blocking_trx_id   | varchar(18) | NO   |     |         |       || blocking_lock_id  | varchar(81) | NO   |     |         |       |+-------------------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)REQUESTING_TRX_IDID of the requesting transaction.   請求事務的IDREQUESTED_LOCK_IDID of the lock for which a transaction is waiting. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.事務在等待的lockid ,詳細的資訊可以關聯 INNODB_LOCKS on LOCK_IDBLOCKING_TRX_IDID of the blocking transaction.blocking 事務的 ID 持有人BLOCKING_LOCK_IDID of a lock held by a transaction blocking another transaction from proceeding. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.一個事務持有的lock ID 阻塞其他交易處理mysql> desc information_schema.innodb_trx    -> ;+----------------------------+---------------------+------+-----+---------------------+-------+| Field                      | Type                | Null | Key | Default             | Extra |+----------------------------+---------------------+------+-----+---------------------+-------+| trx_id                     | varchar(18)         | NO   |     |                     |       || trx_state                  | varchar(13)         | NO   |     |                     |       || trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       || trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       || trx_wait_started           | datetime            | YES  |     | NULL                |       || trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       || trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       || trx_query                  | varchar(1024)       | YES  |     | NULL                |       || trx_operation_state        | varchar(64)         | YES  |     | NULL                |       || trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       || trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       || trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       || trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       || trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       || trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       || trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       || trx_isolation_level        | varchar(16)         | NO   |     |                     |       || trx_unique_checks          | int(1)              | NO   |     | 0                   |       || trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       || trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       || trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       || trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       || trx_is_read_only           | int(1)              | NO   |     | 0                   |       || trx_autocommit_non_locking | int(1)              | NO   |     | 0                   |       |+----------------------------+---------------------+------+-----+---------------------+-------+24 rows in set (0.00 sec)TRX_ID  唯一的事務ID號, InnoDB內部使用.( 在MySQL 5.6開始,那些IDs不是被建立用於事務的,是唯讀和非堵塞的TRX_WEIGHT  一個事務的權重, 反映(但是不是必須的準確的計數) 被改變的行和被事務鎖定的行。為瞭解決死結, InnoDB 選擇最小權重的事務作為 victim來復原。事務 改變非事務表是被認為比其他權重高, 不管更改的數目和鎖住的行數TRX_STATE   事務執行狀態, RUNNING,LOCK WAIT, 復原或者提交TRX_STARTEDTransaction start time.  事務開始時間TRX_REQUESTED_LOCK_ID  LOCK 事務ID 是當前等待(如果 TRX_STATE 是lock,否則為空白)TRX_WAIT_STARTED  當交易開始等待lock(如果TRX_STATE 是LOCK WAIT,否則NULL)TRX_MYSQL_THREAD_IDMySQL thread ID,可以用於關聯PROCESSLIST 的ID mysql>  show full processlist;+----+------+-----------+--------+---------+------+-------+-----------------------+| Id | User | Host      | db     | Command | Time | State | Info                  |+----+------+-----------+--------+---------+------+-------+-----------------------+|  1 | root | localhost | DEVOPS | Sleep   |    6 |       | NULL                  ||  2 | root | localhost | DEVOPS | Query   |    0 | init  | show full processlist |+----+------+-----------+--------+---------+------+-------+-----------------------+見章節 14.13.2.3.1 ,潛在的不一致的PROCESSLIST資料TRX_QUERY 通過事務執行的SQLTRX_OPERATION_STATE  事務的當前操作,或者為NULLTRX_TABLES_IN_USE    Innodb表當前被使用來處理當前事務的SQL的表的數量TRX_TABLES_LOCKED    InnoDB表當前SQL語句有行鎖的表的數量。因為這些是row locks, 不是表鎖,表仍舊可以被讀取和被多個事務寫,儘管有些行被鎖定。TRX_LOCK_STRUCT        事務保留的鎖的數目TRX_LOCK_MEMORY_BYTES   這個事務的lock 結構在記憶體裡佔用的大小TRX_ROWS_LOCKED 這個事務的鎖定的大概記錄的行數,該值可能包括標記為刪除的記錄 物理上是存在的 但是對於事務不可見TRX_ROWS_MODIFIED  在該事務中修改和插入的數目TRX_CONCURRENCY_TICKETS  這個值表明 當前事務可以做多少事情在被交換出去之前,通過 innodb_concurrency_tickets 選擇指定TRX_ISOLATION_LEVEL   當前事務的隔離等級TRX_UNIQUE_CHECKS是否 unique checks 被啟用或者關閉 對於當前的事務, 它們可以在一個批量資料載入時關閉

mysql 行鎖排查