--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