mysql 锁查看

--mysql中默认锁超时为50s
select * 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的sql
select 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:11
trx_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:53
trx_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:43
trx_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: 1
3 rows in set (0.00 sec)

--下面的两个也可以进行锁的查看
 --requesting_trx_id,请求锁的事务ID,blocking_trx_id当前拥有锁的锁ID
mysql> 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/bash
mysql -u root -e "show processlist" | grep -i "Locked" >> locked_log.txt

for line in `cat locked_log.txt | awk '{print $1}'`
do 
   echo "kill $line;" >> kill_thread_id.sql
done