之前遇到的死鎖分析，記錄下

死鎖信息如下：

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

根據(jù)死鎖信息顯示兩個多表鏈接update的事務(wù)，事務(wù)一在等待表keap_cash_transcation表主鍵索引的S鎖河狐，位置在第3頁的176字節(jié)處，事務(wù)二拿到了對應(yīng)位置的鎖慷蠕，而又在等待該位置S鎖据忘，這種鎖等待看起來有點奇怪，明明已經(jīng)拿到該位置的X鎖為什么還要去獲取S鎖羹幸，都知道m(xù)ysql在對唯一索引做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鎖，只有針對行加鎖的情況，再仔細看看兩個事務(wù)的sql發(fā)現(xiàn)等待鎖的表keap_cash_transcation只是作為關(guān)聯(lián)條件并未更新字段屏镊，查看表結(jié)構(gòu)都只有主鍵依疼，transcation_id無索引，建兩個只有主鍵的零時表進行測試：

結(jié)構(gòu)：

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

測試：

看出事務(wù)二的update在獲取t1表的S鎖而芥，但是這條語句只對t1表做查詢匹配操作律罢，兩個事務(wù)執(zhí)行的語句調(diào)個順序看看結(jié)果

事務(wù)二這時是在獲取X鎖，注意死鎖顯示都在獲取同一個位置的鎖棍丐，并且update拿到有X鎖误辑，事務(wù)一的語句顯然是首先從t1表獲取S鎖，再獲取X鎖歌逢，最終獲取S鎖

加上索引再進行測試：

總結(jié)：

通過上面的測試基本已經(jīng)清楚原因巾钉，mysql在關(guān)聯(lián)update時只是作為關(guān)聯(lián)查詢的表，如果沒有對應(yīng)索引會對滿足條件的行進行加鎖操作秘案，在t1表進行數(shù)據(jù)查詢時滿足id=4條件的所有數(shù)據(jù)都會加S鎖砰苍，和t2表關(guān)聯(lián)對數(shù)據(jù)進行判斷并做更新時對應(yīng)的行會請求X鎖，當(dāng)對數(shù)據(jù)更新完成后會釋放X鎖并請求S鎖阱高，整個流程為 S-> X->S,如果未給t1表指明條件又以它作為驅(qū)動表的話就會造成t1表的記錄都會加鎖赚导，在對條件字段id加了索引過后t1表不會產(chǎn)生阻塞，在生產(chǎn)環(huán)境中有這種關(guān)聯(lián)更新的語句需要注意索引的問題讨惩。