<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: 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 被启用或者关闭 对于当前的事务, 它们可以在一个批量数据加载时关闭
