記得以前，當(dāng)出現(xiàn)：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，
要解決是一件麻煩的事情 ；
特別是當(dāng)一個(gè)SQL執(zhí)行完了，但未COMMIT，后面的SQL想要執(zhí)行就是被鎖，超時(shí)結(jié)束；
DBA光從數(shù)據(jù)庫無法著手找出源頭是哪個(gè)SQL鎖住了；
有時(shí)候看看show engine innodb status , 并結(jié)合 show full processlist; 能暫時(shí)解決問題；但一直不能精確定位；
金山ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為成都創(chuàng)新互聯(lián)公司的ssl證書銷售渠道，可以享受市場價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18982081108（備注：SSL證書合作）期待與您的合作！
在5.5中，information_schema 庫中增加了三個(gè)關(guān)于鎖的表（MEMORY引擎）；
innodb_trx ## 當(dāng)前運(yùn)行的所有事務(wù)
innodb_locks ## 當(dāng)前出現(xiàn)的鎖
innodb_lock_waits ## 鎖等待的對應(yīng)關(guān)系
看到這個(gè)就非常激動(dòng) ； 這可是解決了一個(gè)大麻煩，先來看一下表結(jié)構(gòu)
[@more@]
root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |#鎖ID
| lock_trx_id | varchar(18) | NO | | | |#擁有鎖的事務(wù)ID
| lock_mode | varchar(32) | NO | | | |#鎖模式
| lock_type | varchar(32) | NO | | | |#鎖類型
| lock_table | varchar(1024) | NO | | | |#被鎖的表
| lock_index | varchar(1024) | YES | | NULL | |#被鎖的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被鎖的表空間號
| lock_page | bigint(21) unsigned | YES | | NULL | |#被鎖的頁號
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被鎖的記錄號
| lock_data | varchar(8192) | YES | | NULL | |#被鎖的數(shù)據(jù)
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |#請求鎖的事務(wù)ID
| requested_lock_id | varchar(81) | NO | | | |#請求鎖的鎖ID
| blocking_trx_id | varchar(18) | NO | | | |#當(dāng)前擁有鎖的事務(wù)ID
| blocking_lock_id | varchar(81) | NO | | | |#當(dāng)前擁有鎖的鎖ID
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |#事務(wù)ID
| trx_state | varchar(13) | NO | | | |#事務(wù)狀態(tài)：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事務(wù)開始時(shí)間；
| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事務(wù)開始等待的時(shí)間
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_MySQL_thread_id | bigint(21) unsigned | NO | | 0 | |#事務(wù)線程ID
| trx_query | varchar(1024) | YES | | NULL | |#具體SQL語句
| trx_operation_state | varchar(64) | YES | | NULL | |#事務(wù)當(dāng)前操作狀態(tài)
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事務(wù)中有多少個(gè)表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事務(wù)擁有多少個(gè)鎖
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事務(wù)鎖住的內(nèi)存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事務(wù)鎖住的行數(shù)
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事務(wù)更改的行數(shù)
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事務(wù)并發(fā)票數(shù)
| trx_isolation_level | varchar(16) | NO | | | |#事務(wù)隔離級別
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性檢查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外鍵檢查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外鍵錯(cuò)誤
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
+----------------------------+---------------------+------+-----+---------------------+-------+
22 rows in set (0.01 sec)
下面我們來動(dòng)手看看數(shù)據(jù)吧：
##建立測試數(shù)據(jù)：
use test;
create table tx1
(id int primary key ,
c1 varchar(20),
c2 varchar(30))
engine=innodb default charset = utf8 ;
insert into tx1 values
(1,'aaaa','aaaaa2'),
(2,'bbbb','bbbbb2'),
(3,'cccc','ccccc2');
commit;
###產(chǎn)生事務(wù)；
### Session1
start transaction;
update tx1 set c1='heyf',c2='heyf' where id =3 ;
## 產(chǎn)生事務(wù)，在innodb_trx就有數(shù)據(jù) ；
root@127.0.0.1 : information_schema 13:38:21> select * from innodb_trx G
*************************** 1. row ***************************
trx_id: 3669D82
trx_state: RUNNING
trx_started: 2010-12-24 13:38:06
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2344
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 1
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
1 row in set (0.00 sec)
### 由于沒有產(chǎn)生鎖等待，下面兩個(gè)表沒有數(shù)據(jù) ；
root@127.0.0.1 : information_schema 13:38:31> select * from innodb_lock_waits G
Empty set (0.00 sec)
root@127.0.0.1 : information_schema 13:38:57> select * from innodb_locks G
Empty set (0.00 sec)
#### 產(chǎn)生鎖等待
#### session 2
start transaction;
update tx1 set c1='heyfffff',c2='heyffffff' where id =3 ;
root@127.0.0.1 : information_schema 13:39:01> select * from innodb_trx G
*************************** 1. row ***************************
trx_id: 3669D83 ##第2個(gè)事務(wù)
trx_state: LOCK WAIT ## 處于等待狀態(tài)
trx_started: 2010-12-24 13:40:07
trx_requested_lock_id: 3669D83:49:3:4 ##請求的鎖ID
trx_wait_started: 2010-12-24 13:40:07
trx_weight: 2
trx_mysql_thread_id: 2346 ##線程 ID
trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3
trx_operation_state: starting index read
trx_tables_in_use: 1 ##需要用到1個(gè)表
trx_tables_locked: 1 ##有1個(gè)表被鎖
trx_lock_structs: 2
trx_lock_memory_bytes: 376
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
*************************** 2. row ***************************
trx_id: 3669D82 ##第1個(gè)事務(wù)
trx_state: RUNNING
trx_started: 2010-12-24 13:38:06
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2344
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 1
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
2 rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:40:12> select * from innodb_locks G
*************************** 1. row ***************************
lock_id: 3669D83:49:3:4 ## 第2個(gè)事務(wù)需要的鎖
lock_trx_id: 3669D83
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tx1`
lock_index: `PRIMARY`
lock_space: 49
lock_page: 3
lock_rec: 4
lock_data: 3
*************************** 2. row ***************************
lock_id: 3669D82:49:3:4 ## 第1個(gè)事務(wù)需要的鎖
lock_trx_id: 3669D82
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tx1`
lock_index: `PRIMARY`
lock_space: 49
lock_page: 3
lock_rec: 4
lock_data: 3
2 rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:40:15> select * from innodb_lock_waits G
*************************** 1. row ***************************
requesting_trx_id: 3669D83 ## 請求鎖的事務(wù)
requested_lock_id: 3669D83:49:3:4 ## 請求鎖的鎖ID
blocking_trx_id: 3669D82 ## 擁有鎖的事務(wù)
blocking_lock_id: 3669D82:49:3:4 ## 擁有鎖的鎖ID
1 row in set (0.00 sec)
哈哈，有了以上這些信息，以下問題就迎刃而解啦。
當(dāng)前有哪些事務(wù)在等待鎖？ 這些鎖需要鎖哪些表，鎖哪些索引，鎖哪些記錄和值 ？
處于等待狀態(tài)的相關(guān)SQL是什么？
在等待哪些事務(wù)完成 ？
擁有當(dāng)前鎖的SQL是什么？
我想這些SQL對DBA來說不難吧？ 大家自己動(dòng)手吧。
重慶分公司
重慶分公司
