213_mysql_innodb_6_lock2

相关命令

5.7 例子

事务1
SELECT MSISDN FROM t50 FOR UPDATE;
SELECT SLEEP(1000);

事务2
SELECT IMEI FROM t50 FOR UPDATE;

事务3
SELECT IMSI FROM t50 FOR UPDATE;

SQL命令

show processlist;

mysql> select * from information_schema.INNODB_LOCK_WAITS;

查看锁的信息

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query, 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;

select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_mysql_thread_id,trx_query from information_schema.INNODB_TRX;

8.0 版本

在performance_schema下新增了data_locks表和data_lock_waits表 (代替了 5.7 information_schema的INNODB_LOCK_WAITS)

Show tables like “%data_lock%”

Data_locks

ENGINE：持有或请求锁定的存储引擎
ENGINE_LOCK_ID：存储引擎持有或请求的锁的ID，锁ID格式是内部的，随时可能更改。
ENGINE_TRANSACTION_ID：请求锁定的事务存储引擎内部ID，可以将其视为锁的所有者
THREAD_ID：对应事务的线程ID，如果需要获取更详细的信息，需要关联threads表的THREAD_ID
EVENT_ID：指明造成锁的EVENT_ID，THREAD_ID+EVENT_ID对应parent EVENT，可以在以下几张表内获得信息
 events_waits_xx表查看等待事件
 events_stages_xxx查看到了哪个阶段
 events_statements_xx表查看对应的SQL语句
 events_transactions_current对应查看事务信息
OBJECT_SCHEMA：对应锁表的schema名称
OBJECT_NAME：对应锁的表名
PARTITION_NAME：对应锁的分区名
SUBPARTITION_NAME：对应锁的子分区名
INDEX_NAME：锁对应的索引名称，InnoDB表不会为NULL
OBJECT_INSTANCE_BEGIN：锁对应的内存地址
LOCK_TYPE：对应的锁类型，对InnoDB而言，可为表锁或者行锁
LOCK_MODE：锁模式，对应值可能为S[,GAP], X[, GAP], IS[,GAP], IX[,GAP], AUTO_INC和UNKNOWN
LOCK_STATUS：锁状态，可能为GRANTED或者WAITING
LOCK_DATA：锁对应的数据，例如如果锁定的是主键，那么该列对应的就是加锁的主键值

data_lock_waits

ENGINE：请求的锁的引擎
REQUESTING_ENGINE_LOCK_ID：请求的锁在存储引擎中的锁ID
REQUESTING_ENGINE_TRANSACTION_ID：请求锁的事务对应的事务ID
REQUESTING_THREAD_ID：请求锁的线程ID
REQUESTING_EVENT_ID：请求锁的EVENT ID
REQUESTING_OBJECT_INSTANCE_BEGIN：请求的锁的内存地址
BLOCKING_ENGINE_LOCK_ID：阻塞的锁的ID，对应data_locks表的ENGINE_LOCK_ID列
BLOCKING_ENGINE_TRANSACTION_ID：锁阻塞的事务ID
BLOCKING_THREAD_ID：锁阻塞的线程ID
BLOCKING_EVENT_ID：锁阻塞的EVENT ID
BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞的锁内存地址

使用 show engine  innodb status 获取锁信息

TRANSACTIONS
------------
Trx id counter 1869
Purge done for trx's n:o < 1834 undo n:o < 0 state: running but idle
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283780402735480, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283780402734704, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 1868, ACTIVE 3225 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 45, OS thread handle 9368, query id 2645 localhost 127.0.0.1 root updating
update t1 SET `varchar`= 'sully33' where id=1
Trx read view will not see trx with id >= 1863, sees < 1863
------- TRX HAS BEEN WAITING 3173 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `nmg`.`t1` trx id 1868 lock_mode X locks rec but not gap waiting
Record lock, heap no 7
---------------------TRANSACTION 1863, ACTIVE 3188 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 47, OS thread handle 6320, query id 2641 localhost 127.0.0.1 root User sleep
SELECT sleep(100000000)

8.0命令

事务1
show variables like "%autocommit%"
show VARIABLES like "%lock_wait_timeout%"
set autocommit=off
set innodb_lock_wait_timeout=500000
begin 
select * from t1;
#insert into t1 VALUE (5, "bob5")
DELETE from t1 WHERE id =1;
SELECT sleep(100000000);
commit ;

事务2
set AUTOCOMMIT=off;
set innodb_lock_wait_timeout=500000

show VARIABLES like "AUTOCOMMIT";
show VARIABLES like "%lock_wait_timeout%"

begin
SELECT * from t1;
update t1 SET `varchar`= 'sully33' where id=1
#update t1 SET `varchar`= 'sully5' where id=5
SELECT * from t1;

COMMIT;

查询线程thread_id&事务id对应关系

SELECT tx.trx_id,pro.ID,  pro.info   from information_schema.`processlist` pro JOIN information_schema.INNODB_TRX tx  on 
 pro.id = tx.trx_mysql_thread_id 
where pro.COMMAND != 'Sleep';

查询锁占用和等待的情况

SELECT
	r.trx_id waiting_trx_id,    
	r.trx_mysql_thread_id waiting_thread,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	b.trx_query blocking_query 
FROM
	`performance_schema`.data_lock_waits w
	INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
	INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

REQUESTING_ENGINE_TRANSACTION_ID  1868

BLOCKING_ENGINE_TRANSACTION_ID   1863

REQUESTING_ENGINE_TRANSACTION_ID  1868

BLOCKING_ENGINE_TRANSACTION_ID   1863

# 查询 事务ID和持有锁的情况

SELECT * from `performance_schema`.data_locks 

innodb_trx

trx_id：唯一事务id号，只读事务和非锁事务是不会创建id的。
TRX_WEIGHT：事务的高度，代表修改的行数（不一定准确）和被事务锁住的行数。为了解决死锁，innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高，即使改变的行和锁住的行比其他事务低。
TRX_STATE：事务的执行状态，值一般分为：RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
TRX_STARTED：事务的开始时间
TRX_REQUESTED_LOCK_ID如果trx_state是lockwait显示事务当前等待锁的id，不是则为空。想要获取锁的信息，根据该lock_id，以innodb_locks表中lock_id列匹配条件进行查询，获取相关信息。
TRX_WAIT_STARTED：如果trx_state是lockwait,该值代表事务开始等待锁的时间；否则为空。
TRX_MYSQL_THREAD_ID：mysql线程id。想要获取该线程的信息，根据该thread_id，以INFORMATION_SCHEMA.PROCESSLIST表的id列为匹配条件进行查询。
TRX_QUERY：事务正在执行的sql语句。
TRX_OPERATION_STATE：事务当前的操作状态，没有则为空。
TRX_TABLES_IN_USE：事务在处理当前sql语句使用innodb引擎表的数量。
TRX_TABLES_LOCKED：当前sql语句有行锁的innodb表的数量。（因为只是行锁，不是表锁，表仍然可以被多个事务读和写）
TRX_LOCK_STRUCTS：事务保留锁的数量。
TRX_LOCK_MEMORY_BYTES：在内存中事务索结构占得空间大小。
TRX_ROWS_LOCKED：事务行锁最准确的数量。这个值可能包括对于事务在物理上存在，实际不可见的删除标记的行。
TRX_ROWS_MODIFIED：事务修改和插入的行数
TRX_CONCURRENCY_TICKETS：该值代表当前事务在被清掉之前可以多少工作，由 innodb_concurrency_tickets系统变量值指定。
TRX_ISOLATION_LEVEL：事务隔离等级。
TRX_UNIQUE_CHECKS：当前事务唯一性检查启用还是禁用。当批量数据导入时，这个参数是关闭的。
TRX_FOREIGN_KEY_CHECKS：当前事务的外键坚持是启用还是禁用。当批量数据导入时，这个参数是关闭的。
TRX_LAST_FOREIGN_KEY_ERROR：最新一个外键错误信息，没有则为空。
TRX_ADAPTIVE_HASH_LATCHED：自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区，一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制，默认值为8。
TRX_ADAPTIVE_HASH_TIMEOUT：是否为了自适应hash索引立即放弃查询锁，或者通过调用mysql函数保留它。当没有自适应hash索引冲突，该值为0并且语句保持锁直到结束。在冲突过程中，该值被计数为0，每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区（由 innodb_adaptive_hash_index_parts参数控制），值保持为0。
TRX_IS_READ_ONLY：值为1表示事务是read only。
TRX_AUTOCOMMIT_NON_LOCKING：值为1表示事务是一个select语句，该语句没有使用for update或者shared mode锁，并且执行开启了autocommit，因此事务只包含一个语句。当TRX_AUTOCOMMIT_NON_LOCKING和TRX_IS_READ_ONLY同时为1，innodb通过降低事务开销和改变表数据库来优化事务。