mysql查看事务和锁情况

MySQL 5.5 – innodb_lock_wait 锁 等待

在5.5中,information_schema 库中增加了三个关于锁的表(MEMORY引擎)

  • innodb_trx ## 当前运行的所有事务
  • innodb_locks ## 当前出现的锁
  • nnodb_lock_waits ## 锁等待的对应关系
mysql> select * from information_schema.innodb_trx \G;
*************************** 1. row ***************************
                    trx_id: 466712
                 trx_state: RUNNING
               trx_started: 2016-08-11 16:25:00
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 5
       trx_mysql_thread_id: 4295
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 3
     trx_lock_memory_bytes: 360
           trx_rows_locked: 1
         trx_rows_modified: 2
   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
1 row in set (0.00 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB = "fr_f_disconf";
+------+------+-------------------------+--------------+---------+------+-----------+------------------------------------------------------------------------+
| ID   | USER | HOST                    | DB           | COMMAND | TIME | STATE     | INFO                                                                   |
+------+------+-------------------------+--------------+---------+------+-----------+------------------------------------------------------------------------+
| 4267 | root | localhost               | fr_f_disconf | Query   |    0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB = "fr_f_disconf" |
| 4288 | root | VM_200_212_centos:42437 | fr_f_disconf | Sleep   |  872 |           | NULL                                                                   |
| 4291 | root | VM_200_212_centos:42440 | fr_f_disconf | Sleep   |  872 |           | NULL                                                                   |
| 4293 | root | VM_200_212_centos:42442 | fr_f_disconf | Sleep   |  872 |           | NULL                                                                   |
| 4290 | root | VM_200_212_centos:42439 | fr_f_disconf | Sleep   |  222 |           | NULL                                                                   |
| 4292 | root | VM_200_212_centos:42441 | fr_f_disconf | Sleep   |  872 |           | NULL                                                                   |
| 4294 | root | VM_200_212_centos:42443 | fr_f_disconf | Sleep   |  872 |           | NULL                                                                   |
| 4295 | root | VM_200_212_centos:42444 | fr_f_disconf | Sleep   |   72 |           | NULL                                                                   |
| 4289 | root | VM_200_212_centos:42438 | fr_f_disconf | Sleep   |  872 |           | NULL                                                                   |
+------+------+-------------------------+--------------+---------+------+-----------+------------------------------------------------------------------------+
9 rows in set (0.00 sec)



mysql> SHOW ENGINE INNODB STATUS \G;

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;

mysql> show variables like "%timeout%";

mysql> show processlist;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值