mysql INNODB_TRX 事务表
来源：互联网 发布：多功能圣经软件下载 编辑：程序博客网 时间：2024/05/19 20:41
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 161Server version: 5.6.22-log Source distributionCopyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitByedemo:/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_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 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:事务的当前操作 如果有的话 否则为NULLTRX_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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0trx_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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0trx_autocommit_non_locking: 02 rows in set (0.00 sec)注意:使用这个表帮助诊断性能问题 在有严重并发负载的时间段使用DESCRIBE or SHOW COLUMNS 来查看额外的信息关于表的列 包含数据类型和默认值
0 0
- mysql INNODB_TRX 事务表
- 查看事务锁：innodb_trx + innodb_locks + innodb_lock_waits
- 查看事务锁：innodb_trx + innodb_locks + innodb_lock_waits
- INFORMATION_SCHEMA.INNODB_TRX 详解
- mysql事务是事务
- mysql 事务
- mysql 事务
- mysql事务
- Mysql 事务
- mysql事务
- mysql事务
- mysql 事务
- mysql事务
- mysql 事务
- mysql事务
- MySQL事务
- mysql---事务
- MySQL事务
- 互联网广告系统综述四定向
- 安卓listview使用的一种方法
- 单独引用bootstrap源码的carousel.js无法实现轮播效果的解决
- 单片机==ds1302_时间日期周几（30）
- WDF驱动模型
- mysql INNODB_TRX 事务表
- 字符串操作函数
- Python3学习二之基本数据类型
- java 引用
- vector效率低怎么破？
- C++“准”标准库Boost学习指南(2)：Boost.Conversion
- 【Primes on Interval】
- 导航栏效果
- WSASocket()与Socket()的区别
