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_ID ID of the requesting transaction.   请求事务的IDREQUESTED_LOCK_ID ID 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_ID ID of the blocking transaction.blocking 事务的 ID 持有者BLOCKING_LOCK_ID ID 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_STARTED Transaction 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 被启用或者关闭 对于当前的事务, 它们可以在一个批量数据加载时关闭