mysql 锁查看

--mysql中默认锁超时为50sselect * from information_schema.SESSION_VARIABLES sv where sv.VARIABLE_NAME like '%timeout%';mysql> show variables like '%timeout%';+-----------------------------+----------+| Variable_name               | Value    |+-----------------------------+----------+| connect_timeout             | 10       || deadlock_timeout_long       | 50000000 || deadlock_timeout_short      | 10000    || delayed_insert_timeout      | 300      || innodb_flush_log_at_timeout | 1        || innodb_lock_wait_timeout    | 50       || innodb_rollback_on_timeout  | OFF      || interactive_timeout         | 28800    || lock_wait_timeout           | 31536000 || net_read_timeout            | 30       || net_write_timeout           | 60       || slave_net_timeout           | 3600     || thread_pool_idle_timeout    | 60       || wait_timeout                | 28800    |+-----------------------------+----------+--设置锁超时mysql>  set innodb_lock_wait_timeout=10000000;--在session 1 中开启一个事务mysql> begin;mysql> update t set user_name='rudy test' where id=1;--在session 2 中开启一个事务mysql> begin;mysql> update t set user_name='rudy test' where id=1;--在session 3 中开启一个事务mysql> begin;mysql> update t set user_name='rudy test' where id=1;--trx_state为RUNNING代表其已经执行完成，等待用户操作，lock wait代表其等待获得共享锁，故running的为源始引起lock的sqlselect trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_weight, trx_mysql_thread_id, trx_query, trx_operation_state, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified from information_schema.innodb_trx;*************************** 1. row ***************************               trx_id: 4136            trx_state: LOCK WAIT          trx_started: 2015-10-19 02:00:11trx_requested_lock_id: 4136:217:4:410     trx_wait_started: 2015-10-19 02:03:41           trx_weight: 2  trx_mysql_thread_id: 24            trx_query: update t set user_name='rudy test' where id=1  trx_operation_state: starting index read    trx_tables_in_use: 1    trx_tables_locked: 1     trx_lock_structs: 2      trx_rows_locked: 1    trx_rows_modified: 0*************************** 2. row ***************************               trx_id: 4135            trx_state: LOCK WAIT          trx_started: 2015-10-19 01:59:53trx_requested_lock_id: 4135:217:4:410     trx_wait_started: 2015-10-19 02:03:32           trx_weight: 2  trx_mysql_thread_id: 23            trx_query: update t set user_name='rudy test' where id=1  trx_operation_state: starting index read    trx_tables_in_use: 1    trx_tables_locked: 1     trx_lock_structs: 2      trx_rows_locked: 1    trx_rows_modified: 0*************************** 3. row ***************************               trx_id: 4134            trx_state: RUNNING          trx_started: 2015-10-19 01:59:43trx_requested_lock_id: NULL     trx_wait_started: NULL           trx_weight: 262  trx_mysql_thread_id: 17            trx_query: NULL  trx_operation_state: NULL    trx_tables_in_use: 0    trx_tables_locked: 0     trx_lock_structs: 261      trx_rows_locked: 100260    trx_rows_modified: 13 rows in set (0.00 sec)--下面的两个也可以进行锁的查看 --requesting_trx_id,请求锁的事务ID,blocking_trx_id当前拥有锁的锁IDmysql> select * from information_schema.innodb_lock_waits;                                                                                                                                                                               +-------------------+-------------------+-----------------+------------------+| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |+-------------------+-------------------+-----------------+------------------+| 4136              | 4136:217:4:410    | 4135            | 4135:217:4:410   || 4136              | 4136:217:4:410    | 4134            | 4134:217:4:410   || 4135              | 4135:217:4:410    | 4134            | 4134:217:4:410   |+-------------------+-------------------+-----------------+------------------+3 rows in set (0.00 sec)mysql> select * from information_schema.innodb_locks;     +----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table | lock_index      | lock_space | lock_page | lock_rec | lock_data      |+----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+| 4136:217:4:410 | 4136        | X         | RECORD    | `test`.`t` | GEN_CLUST_INDEX |        217 |         4 |      410 | 0x000000000300 || 4135:217:4:410 | 4135        | X         | RECORD    | `test`.`t` | GEN_CLUST_INDEX |        217 |         4 |      410 | 0x000000000300 || 4134:217:4:410 | 4134        | X         | RECORD    | `test`.`t` | GEN_CLUST_INDEX |        217 |         4 |      410 | 0x000000000300 |+----------------+-------------+-----------+-----------+------------+-----------------+------------+-----------+----------+----------------+--当然可以kill掉某一个线程kill 17;--kill掉所有lock的线程cat kill_thread.sh#!/bin/bashmysql -u root -e "show processlist" | grep -i "Locked" >> locked_log.txtfor line in `cat locked_log.txt | awk '{print $1}'`do    echo "kill $line;" >> kill_thread_id.sqldone