mysql 一個死結的分析

標籤：msyql 死結   死結   mysql update join   msyql 死結分析   

死結資訊如下：

*** (1) TRANSACTION:

TRANSACTION 4363766192, ACTIVE 0 sec

mysql tables in use 2, locked 2

LOCK WAIT 9 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 6

MySQL thread id 8822753, OS thread handle 0x7fca3025b700, query id 2302320886 *.*.*.* cashcoupon_oper Sending data

update keap_cash_coup_type a,(select sum(freezed_amount) freezedAmount,cash_coupon_type_id from keap_cash_transcation where transcation_id = 10000001415322882 group by cash_coupon_type_id)b set a.amount = a.amount-b.freezedAmount,a.locked_amount=a.locked_amount+b.freezedAmount where a.cash_coupon_type_id=b.cash_coupon_type_id

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2280 page no 3 n bits 176 index `PRIMARY` of table `keap_ticket_cash`.`keap_cash_transcation` trx id 4363766192 lock mode S locks rec but not gap waiting

*** (2) TRANSACTION:

TRANSACTION 4363766191, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4999

mysql tables in use 2, locked 2

9 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 6

MySQL thread id 8822751, OS thread handle 0x7fc8718a1700, query id 2302320895 *.*.*.* cashcoupon_oper Sending data

update keap_cash_coup_type a,(select sum(freezed_amount) freezedAmount,cash_coupon_type_id from keap_cash_transcation where transcation_id = 10000001415322879 group by cash_coupon_type_id)b set a.amount = a.amount-b.freezedAmount,a.locked_amount=a.locked_amount+b.freezedAmount where a.cash_coupon_type_id=b.cash_coupon_type_id

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 2280 page no 3 n bits 176 index `PRIMARY` of table `keap_ticket_cash`.`keap_cash_transcation` trx id 4363766191 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2280 page no 3 n bits 176 index `PRIMARY` of table `keap_ticket_cash`.`keap_cash_transcation` trx id 4363766191 lock mode S locks rec but not gap waiting

Record lock, heap no 103 PHYSICAL RECORD: n_fields 12; compact format; info bits 0

資訊顯示兩個多錶鏈接update的事務，事務一在等待表keap_cash_transcation表主鍵索引的S鎖，位置在第3頁的176位元組處，事務二拿到了對應位置的鎖，而又在等待該位置S鎖，這種鎖等待看起來有點奇怪，明明已經拿到該位置的X鎖為什麼還要去擷取S鎖，都知道mysql在對唯一索引做update和insert時是會先擷取S鎖再擷取X鎖，這感覺有點像，一步一步排查分析吧

首先查詢隔離等級好判斷加鎖粒度：

mysql> show global variables like "%iso%";

+---------------+----------------+

| Variable_name | Value          |

+---------------+----------------+

| tx_isolation  | READ-COMMITTED |

+---------------+----------------+

1 row in set (0.00 sec)

是RC提交讀隔離等級，知道了無gap鎖，只有針對行加鎖的情況，再仔細看看兩個事務的sql發現等待鎖的表keap_cash_transcation只是作為關聯條件並未更新欄位，查看錶結構都只有主鍵，transcation_id無索引，建兩個只有主鍵的零時表進行測試：

結構：

CREATE TABLE `t1`/`t2` (

 `id` int(11) DEFAULT NULL,

 `name` varchar(10) DEFAULT NULL,

 `id_1` int(11) NOT NULL AUTO_INCREMENT,

 PRIMARY KEY (`id_1`)

) ENGINE=InnoDB AUTO_INCREMENT=7

測試：

session 1:	session 2:
select * from t1 for update;
	update t2 join t1 on t1.id=t2.id set t2.age=10; 發生等待
	mysql> select a.lock_trx_id,b.trx_mysql_thread_id,b.trx_query,a.lock_mode,a.lock_type from INNODB_LOCKs a join INNODB_trx b on a.lock_trx_id=b.trx_id\G; *************************** 1. row *************************** lock_trx_id: 18944085 trx_mysql_thread_id: 33762  trx_query: update t2 join t1 on t1.id=t2.id set t2.age=10  lock_mode: S  lock_type: RECORD  lock_page: 3 lock_table: `test`.`t1` *************************** 2. row *************************** lock_trx_id: 18944084 trx_mysql_thread_id: 33761  trx_query: NULL  lock_mode: X  lock_type: RECORD  lock_page: 3 lock_table: `test`.`t1` 2 rows in set (0.00 sec)

看出事務二的update在擷取t1表的S鎖，但是這條語句只對t1表做查詢匹配操作，兩個事務執行的語句調個順序看看結果

session 1	session 2
update t2 join t1 on t1.id=t2.id set t2.age=10;
	select * from t1 for update; 發生等待
	*************************** 1. row *************************** lock_trx_id: 18944086 trx_mysql_thread_id: 33761  trx_query: select * from t1 for update  lock_mode: X  lock_type: RECORD  lock_page: 3 lock_table: `test`.`t1` *************************** 2. row *************************** lock_trx_id: 18944085 trx_mysql_thread_id: 33762  trx_query: NULL  lock_mode: S  lock_type: RECORD  lock_page: 3 lock_table: `test`.`t1` 2 rows in set (0.01 sec)

事務二這時是在擷取X鎖，注意死結顯示都在擷取同一個位置的鎖，並且update拿到有X鎖，事務一的語句顯然是首先從t1表擷取S鎖，再擷取X鎖，最終擷取S鎖

加上索引再進行測試：

session 1	session 2
update t2 join t1  on (t1.id=t2.id and t1.id=4) set t2.age=10;
	select * from t1 where id=4 for update; 正常無阻塞！

總結：

通過上面的測試基本已經清楚mysql在關聯update時，只是作為關聯查詢的表如果沒有對應索引會對滿足條件的行資料會進行加鎖操作，在t1表進行資料查詢時滿足id=4條件的所有資料都會加S鎖，和t2表關聯對資料進行判斷並做更新時對應的行會請求X鎖，當對資料更新完成後會釋放X鎖並請求S鎖，整個流程為 S-> X->S,如果未給t1表指明條件又以它作為驅動表的話就會造成t1表的記錄都會加鎖，在對條件欄位id加了索引過後t1表不會產生阻塞，在生產環境中有這種關聯更新的語句需要注意索引的問題。

本文出自 “D調de默默” 部落格，請務必保留此出處http://xiaozhong991.blog.51cto.com/2354914/1887014

mysql 一個死結的分析