mysql unique 锁_mysql 行锁排查

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: 0 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 | 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_query FROM 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', NULL mysql> 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. 请求事务的ID REQUESTED_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_ID BLOCKING_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_ID MySQL 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 通过事务执行的SQL TRX_OPERATION_STATE 事务的当前操作,或者为NULL TRX_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 被启用或者关闭 对于当前的事务, 它们可以在一个批量数据加载时关闭