首页 > 系统 > Linux >

mysql INNODB_TRX 事务表

2016-10-21

demo: root mysql -uroot -pkjk7787czcb --socket= data01 mysql mysql sock -e "show processlist " Warning: Using a password on the command line interface can be insecure

demo:/root# mysql -uroot -pkjk7787czcb --socket=/data01/mysql/mysql.sock -e"show processlist" 
Warning: Using a password on the command line interface can be insecure.
+-----+------+----------------------+--------+---------+------+-------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id  | User | Host                 | db     | Command | Time | State                                     | Info                                                                                                 |
+-----+------+----------------------+--------+---------+------+-------------------------------------------+------------------------------------------------------------------------------------------------------+
                                                                                        |
|  29 | root | 192.168.32.33:39009  | formal | Sleep   |   20 |                                           | NULL                                                                                                 |
|  30 | root | 192.168.32.33:39010  | formal | Sleep   |   20 |                                           | NULL                                                                                                 |
|  31 | root | 192.168.33.1:58528   | dev    | Sleep   |  801 |                                           | NULL                                                                                                 |
|  43 | root | 192.168.33.1:61431   | dev    | Sleep   | 2147 |                                           | NULL                                                                                                 |
|  51 | root | 192.168.33.6:59019   | test   | Sleep   | 1874 |                                           | NULL                                                                                                 |
|  58 | root | 192.168.34.141:50510 | dev    | Query   | 1730 | Waiting for stored function metadata lock | DROP FUNCTION IF EXISTS `getDeptmentChildList`                                                       |
|  60 | root | 192.168.34.141:50519 | dev    | Query   | 2031 | closing tables                            | SELECT 
    dep_id
  FROM
    org_department 
  WHERE FIND_IN_SET(
      parent_id,
      getD |
|  62 | root | 192.168.33.6:59022   | test   | Sleep   | 1666 |                                           | NULL                                                                                                 |
|  71 | root | 192.168.33.93:50624  | dev    | Sleep   |   15 |                                           | NULL                                                                                                 |
|  72 | root | 192.168.33.93:50625  | dev    | Sleep   |   15 |                                           | NULL                                                                                                 |
|  73 | root | 192.168.33.93:50623  | dev    | Sleep   |   15 |                                           | NULL                                                                                                 |
|  74 | root | 192.168.33.93:50644  | dev    | Sleep   |   15 |                                           | NULL                                                                                                 |
|  75 | root | 192.168.33.93:50646  | dev    | Sleep   |   15 |                                           | NULL                                                                                                 |
|  76 | root | 192.168.33.93:50645  | dev    | Sleep   |   15 |                                           | NULL                                                                                                 |
|  77 | root | 192.168.33.93:50647  | dev    | Sleep   |   15 |                                           | NULL                                                                                                 |
|  78 | root | 192.168.33.93:50648  | dev    | Sleep   |   15 |                                           | NULL                                                                                                 |
|  79 | root | 192.168.33.2:50907   | NULL   | Sleep   | 1759 |                                           | NULL                                                                                                 |
|  80 | root | 192.168.33.2:50908   | dev    | Sleep   | 1701 |                                           | NULL                                                                                                 |
|  81 | root | 192.168.33.2:50909   | dev    | Sleep   | 1753 |                                           | NULL                                                                                                 |
|  82 | root | 192.168.34.141:50540 | dev    | Query   | 1742 | Waiting for stored function metadata lock | CREATE FUNCTION getDeptmentChildList(rootId varchar(32))
     RETURNS varchar(1000)
     BEGIN
		 |
|  83 | root | 192.168.33.2:50930   | dev    | Sleep   | 1695 |                                           | NULL                                                                                                 |
|  85 | root | 192.168.34.141:50878 | NULL   | Sleep   | 1520 |                                           | NULL                                                                                                 |
|  86 | root | 192.168.34.141:50879 | dev    | Sleep   |  135 |                                           | NULL                                                                                                 |
|  87 | root | 192.168.34.141:50880 | dev    | Sleep   | 1519 |                                           | NULL                                                                                                 |
|  88 | root | 192.168.34.141:50881 | dev    | Sleep   | 1508 |                                           | NULL                                                                                                 |
|  90 | root | 192.168.34.141:50885 | dev    | Sleep   | 1098 |                                           | NULL                                                                                                 |
|  92 | root | 192.168.33.1:65155   | dev    | Sleep   | 1386 |                                           | NULL                                                                                                 |
|  93 | root | 192.168.34.141:50957 | dev    | Sleep   | 1340 |                                           | NULL                                                                                                 |
|  94 | root | 192.168.34.141:50961 | dev    | Query   | 1322 | Waiting for stored function metadata lock | DROP FUNCTION IF EXISTS getDeptmentChildList                                                         |
|  95 | root | 192.168.33.18:61133  | dev    | Sleep   |  956 |                                           | NULL                                                                                                 |
|  96 | root | 192.168.33.18:61143  | NULL   | Sleep   |  995 |                                           | NULL                                                                                                 |
| 105 | root | 192.168.34.141:51436 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 106 | root | 192.168.34.141:51434 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 107 | root | 192.168.34.141:51435 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 108 | root | 192.168.34.141:51474 | dev    | Sleep   |   23 |                                           | NULL                                                                                                 |
| 109 | root | 192.168.34.141:51475 | dev    | Sleep   |   23 |                                           | NULL                                                                                                 |
| 110 | root | 192.168.34.141:51476 | dev    | Sleep   |   23 |                                           | NULL                                                                                                 |
| 113 | root | 192.168.34.141:51724 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 114 | root | 192.168.34.141:51725 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 115 | root | 192.168.34.141:51726 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 116 | root | 192.168.34.141:51727 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 117 | root | 192.168.34.141:51728 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 118 | root | 192.168.34.141:51729 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 119 | root | 192.168.34.141:51730 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
| 120 | root | 192.168.34.141:51731 | dev    | Sleep   |    1 |                                           | NULL                                                                                                 |
                                                                                            |
| 160 | root | localhost            | NULL   | Query   |    0 | init                                      | show processlist                                                                                     |
+-----+------+----------------------+--------+---------+------+-------------------------------------------+------------------------------------------------------------------------------------------------------+
demo:/root# mysql -uroot -pkjk7787czcb --socket=/data01/mysql/mysql.sock 
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 161
Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> kill 60;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
demo:/root# mysql -uroot -pkjk7787czcb --socket=/data01/mysql/mysql.sock -e"show processlist" 



mysql 锁排查:

 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
    
    
    
     SELECT
      ifnull(trx_id, 'null'),
    ifnull(trx_state,'null'),
	  ifnull(trx_started,'null'),
    ifnull(trx_wait_started,'null'),
    ifnull(trx_weight,'null'),
    ifnull(trx_mysql_thread_id,'null'),
    ifnull(trx_query,'null'),
    ifnull(trx_operation_state,'null'),
    ifnull(TRX_TABLES_IN_USE,'null'),
    ifnull(trx_rows_locked,'null'),
    ifnull(trx_rows_modified,'null'),
	ifnull(trx_tables_locked,'null')
FROM
    information_schema.innodb_trx;
	
INNODB_TRX :
 INNODB_TRX table 包含信息关于每个事务(排除只读事务)当前执行的在InnoDB,包含是否事务是等待一个锁,

当事务启动后, SQL语句 事务是正在执行

 including whether the transaction is waiting for a lock
 
 
 INNODB_TRX Columns 相关列信息:

TRX_ID:
唯一的事务ID号, 内部与InnoDB(从MySQL 5.6开始, 那些IDs 能用被创建用于只读和非锁定事务

TRX_WEIGHT:

一个事务的权重,反映(但不一定全是确定的计数) 改变的记录数和被事务锁定的记录数。

为了释放一个死锁, InnoDB 选择一个具有小权重的事务作为"受害者”回滚。

事务改变了非事务表是被认为是严重的相比其他, 无论是被改变的行或者锁定的行

TRX_STATE: 事务执行的状态, 允许的值为 RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.

TRX_STARTED:事务开始时间

TRX_REQUESTED_LOCK_ID: lock 的ID 事务当前等待的,如果TRX_STATE 是lock_wait;

否则就是NULL. 得到信息关于lock,使用LOCK_ID和INNODB_LOCKS表关联
	
TRX_WAIT_STARTED:时间 当事务开始等待锁的时间, 如果TRX_STATE is LOCK WAIT;  否则为空	
	
TRX_MYSQL_THREAD_ID: MySQL thread ID,得到细节关于thread, 使用这个列和NFORMATION_SCHEMA PROCESSLIST table的ID进行关联	

等于
demo:/root# mysql -uroot -p1234567 --socket=/data01/mysql/mysql.sock -e"show processlist"
Warning: Using a password on the command line interface can be insecure.
+-------+------+---------------------+--------+---------+------+----------+---------------------------------------+
| Id    | User | Host                | db     | Command | Time | State    | Info                                  |
+-------+------+---------------------+--------+---------+------+----------+---------------------------------------+
| 63915 | root | 192.168.33.29:56815 | DEVOPS | Sleep   |   66 |          | NULL                                  |
| 63916 | root | 192.168.33.29:56816 | DEVOPS | Sleep   |   70 |          | NULL                                  |
| 64307 | root | 192.168.33.29:60037 | DEVOPS | Sleep   |  413 |          | NULL                                  |
| 64308 | root | 192.168.33.29:60038 | DEVOPS | Sleep   |  413 |          | NULL                                  |
| 64317 | root | localhost           | DEVOPS | Sleep   |  937 |          | NULL                                  |
| 64328 | root | localhost           | DEVOPS | Query   |   13 | updating | delete from aa where username='admin' |
| 64404 | root | localhost           | NULL   | Query   |    0 | init     | show processlist                      |
+-------+------+---------------------+--------+---------+------+----------+---------------------------------------+

TRX_QUERY:事务执行的语句

TRX_OPERATION_STATE:事务的当前操作 如果有的话 否则为NULL

TRX_TABLES_IN_USE:InnoDB表的数据用于当处理当前的SQL语句

TRX_TABLES_LOCKED: InnoDB 表的数量 当前SQL语句有行锁在上面( 因为那些是行锁,不是表锁,表仍旧可以读取和写入通过多个事务m

尽管一些记录被锁定)

TRX_LOCK_STRUCTS:事务保留的锁的数量

TRX_LOCK_MEMORY_BYTES:这个事务在内存中lock结构占据的大小

TRX_ROWS_LOCKED:这个事务锁定的记录,这个值可能包含标记为删除的记录,但是物理存在的 但是对于事务不可见

TRX_ROWS_MODIFIED: 此事务中修改和插入记录的数目

TRX_ISOLATION_LEVEL: 当前事务的隔离级别


mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 197239  
                 trx_state: LOCK WAIT
               trx_started: 2016-10-19 12:21:11
     trx_requested_lock_id: 197239:29:3:2
          trx_wait_started: 2016-10-19 12:21:11
                trx_weight: 2
       trx_mysql_thread_id: 64328
                 trx_query: delete from aa where username='admin'  ###等待锁
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 197228
                 trx_state: RUNNING
               trx_started: 2016-10-19 12:19:52
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 64317
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 360
           trx_rows_locked: 4
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)	
	
	
注意:
使用这个表帮助诊断性能问题 在有严重并发负载的时间段

使用DESCRIBE or SHOW COLUMNS 来查看额外的信息关于表的列 包含数据类型和默认值
相关文章
最新文章
热点推荐