Mysql被锁查询

为什么80%的码农都做不了架构师？>>>   

原文：http://imysql.com/2015/03/25/mysql-faq-how-to-fetch-latest-trxid.shtml

 

1. 执行 SHOW ENGINE INNODB STATUS ，查看事务相关信息

   =====================================
   150303 17:16:11 INNODB MONITOR OUTPUT
   =====================================
   Per second averages calculated from the last 15 seconds
   ...
   ------------
   TRANSACTIONS
   Trx id counter 3359877657 -- 当前最大事务ID
   Purge done for trx's n:o < 3359877468 undo n:o < 0 state: running
   History list length 324
   LIST OF TRANSACTIONS FOR EACH SESSION:
   ---TRANSACTION 0, not started -- 该会话中执行SHOW ENGINE INNODB STATUS，不会产生事务，所以事务ID为0
   MySQL thread id 4692367, OS thread handle 0x51103940, query id 677284426 xx.173ops.com 10.x.x.x yejr init
   SHOW /*!50000 ENGINE*/ INNODB STATUS
   ---TRANSACTION 3359877640, not started --非活跃事务，还未开始
   mysql tables in use 1, locked 0
   MySQL thread id 4678384, OS thread handle 0x41a57940, query id 677284427 xx.173ops.com 10.x.x.x yejr System lock
   select polinfo0_.Fid as Fid39_0_, ...
   
   ---TRANSACTION 3359877652, not started
   MySQL thread id 4678383, OS thread handle 0x50866940, query id 677284420 xx.173ops.com 10.x.x.x yejr cleaning up
   
   ---TRANSACTION 3359877635, ACTIVE 1358 sec, thread declared inside InnoDB 5000 --活跃长事务，运行了1358秒还未结束，要引起注意，可能会导致大量锁等待发生
   mysql tables in use 1, locked 1
   1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
   MySQL thread id 3120717, OS thread handle 0x529b4940, query id 677284351 xx.173ops.com 10.x.x.x yejr query end
   insert into t_live_room ...

    

2. 查看INFORMATION_SCHEMA.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 三个表，通过这些信息能快速发现哪些事务在阻塞其他事务
   1. 先查询 INNODB_TRX 表，看看都有哪些事务

      mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
      *************************** 1. row ***************************
       trx_id: 17778 -- 当前事务ID
       trx_state: LOCK WAIT -- 处于锁等待状态，也就是等待其他会话释放锁资源
       trx_started: 2015-03-04 10:40:26
       trx_requested_lock_id: 17778:82:3:6 -- 欲请求的锁
       trx_wait_started: 2015-03-04 10:40:26
       trx_weight: 2 -- 大意是该锁影响了2行记录
       trx_mysql_thread_id: 657 -- processlist中的线程ID
       trx_query: update trx_fee set fee=rand()*1000 where id= 4
       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: 17773
        trx_state: RUNNING
       trx_started: 2015-03-04 10:40:23
       trx_requested_lock_id: NULL
       trx_wait_started: NULL
       trx_weight: 10
       trx_mysql_thread_id: 656
       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: 9
       trx_rows_modified: 8
       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): kill trx_mysql_thread_id;  2):  use information_schema;  select * from processlist where id in ( trx_mysql_thread_id );  3):  show engine innodb status\G

   2. 再看 INNODB_LOCKS 表，看看都有什么锁

      mysql> select * from information_schema.INNODB_LOCKS\G
      *************************** 1. row ***************************
      lock_id: 17778:82:3:6 --当前锁ID
      lock_trx_id: 17778 --该锁对应的事务ID
      lock_mode: X -- 锁类型，排它锁X
      lock_type: RECORD --锁范围，记录锁：record lock，其他锁范围：间隙锁：gap lock，或者next-key lock（记录锁+间隙锁）
      lock_table: `test`.`trx_fee`
      lock_index: PRIMARY --加载在哪个索引上的锁
      lock_space: 82
      lock_page: 3
      lock_rec: 6
      lock_data: 4
      *************************** 2. row ***************************
      lock_id: 17773:82:3:6
      lock_trx_id: 17773
      lock_mode: X
      lock_type: RECORD
      lock_table: `test`.`trx_fee`
      lock_index: PRIMARY
      lock_space: 82
      lock_page: 3
      lock_rec: 6
      lock_data: 4

       

   3. 最后看 INNODB_LOCK_WAITS 表，看看当前都有哪些锁等待

      mysql> select * from information_schema.INNODB_LOCK_WAITS\G
      *************************** 1. row ***************************
      requesting_trx_id: 17778 --请求锁的事务ID（等待方）
      requested_lock_id: 17778:82:3:6 -- 请求锁ID
      blocking_trx_id: 17773 -- 阻塞该锁的事务ID（当前持有方，待释放）
      blocking_lock_id: 17773:82:3:6 -- 持有的锁ID

       

转载于:https://my.oschina.net/hebad/blog/1525421