這篇文章主要介紹“MySQL死鎖的原因及解決方法”,在日常操作中,相信很多人在MySQL死鎖的原因及解決方法問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”MySQL死鎖的原因及解決方法”的疑惑有所幫助!接下來,請跟著小編一起來學(xué)習(xí)吧!
讓客戶滿意是我們工作的目標,不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶,將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴,公司提供的服務(wù)項目有:申請域名、網(wǎng)絡(luò)空間、營銷軟件、網(wǎng)站建設(shè)、徐州網(wǎng)站維護、網(wǎng)站推廣。
這是我同事問我的一個問題,在網(wǎng)上看到了如下案例,本案例RC RR都可以出現(xiàn),其實這個死鎖原因也不叫簡單,我們來具體看看:
CREATE database deadlock_test; use deadlock_test; CREATE TABLE `push_token` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `token` varchar(128) NOT NULL COMMENT 'push token', `app_id` varchar(128) DEFAULT NULL COMMENT 'appid', `deleted` tinyint(1) NOT NULL COMMENT '是否已刪除 0:否 1:是', PRIMARY KEY (`id`), UNIQUE KEY `uk_token_appid` (`token`,`app_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3384 DEFAULT CHARSET=utf8 COMMENT='pushtoken表'; insert into push_token (id, token, app_id, deleted) values(1,"token1",1,0);
s1(TRX_ID367661) | s2(TRX_ID367662) | s3(TRX_ID367663) |
---|---|---|
begin; UPDATE push_token SET deleted = 1 WHERE token = ‘token1’ AND app_id = ‘1’; | ||
begin; DELETE FROM push_token WHERE id IN (1); | ||
begin; UPDATE push_token SET deleted = 1 WHERE token = ‘token1’ AND app_id = ‘1’; | ||
commit; | ||
Query OK, 0 rows affected (0.00 sec) | Query OK, 1 row affected (17.32 sec) | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
我使用的分析方法是把整個加鎖的日志打印出來,當然需要用到我自己做了輸出修改的一個版本,如下:
https://github.com/gaopengcarl/percona-server-locks-detail-5.7.22
這個版本我打開了的日志記錄參數(shù)如下:
mysql> show variables like '%gaopeng%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | gaopeng_mdl_detail | OFF | | innodb_gaopeng_row_lock_detail | ON | +--------------------------------+-------+ 2 rows in set (0.01 sec)
這樣大部分的innodb加鎖記錄都會記錄到errlog日志了。好了下面我詳細分析一下日志:
初始化的情況整個表只有1條記錄,本表包含一個主鍵和一個唯一鍵。
begin; UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id = '1';
日志輸出:
2019-08-18T19:10:05.117317+08:00 6 [Note] InnoDB: TRX ID:(367661) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 6; hex 746f6b656e31; asc token1;; 1: len 1; hex 31; asc 1;; 2: len 8; hex 8000000000000001; asc ;; 2019-08-18T19:10:05.117714+08:00 6 [Note] InnoDB: TRX ID:(367661) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000000001; asc ;; 1: len 6; hex 000000059c2c; asc ,;; 2: len 7; hex bf000000420110; asc B ;; 3: len 6; hex 746f6b656e31; asc token1;; 4: len 1; hex 31; asc 1;; 5: len 1; hex 80; asc ;;
我們看到主鍵和唯一鍵都加鎖了如下圖:
begin;DELETE FROM push_token WHERE id IN (1); `
日志輸出:
2019-08-18T19:10:22.751467+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000000001; asc ;; 1: len 6; hex 000000059c2d; asc -;; 2: len 7; hex 400000002a1dc8; asc @ * ;; 3: len 6; hex 746f6b656e31; asc token1;; 4: len 1; hex 31; asc 1;; 5: len 1; hex 81; asc ;; 2019-08-18T19:10:22.752753+08:00 9 [Note] InnoDB: Trx(367662) is blocked!!!!!
這個時候S2需要獲取主鍵上的鎖,因此被堵塞了如下圖:
begin; UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id = '1'; `
日志輸出:
019-08-18T19:10:30.822111+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 6; hex 746f6b656e31; asc token1;; 1: len 1; hex 31; asc 1;; 2: len 8; hex 8000000000000001; asc ;; 2019-08-18T19:10:30.918248+08:00 8 [Note] InnoDB: Trx(367663) is blocked!!!!!
這個時候S3需要獲取唯一鍵上的鎖,因此被堵塞了如下圖:
這一步完成后死鎖出現(xiàn)。
commit;
日志輸出如下:
367663和367662各自獲取需要的鎖 2019-08-18T19:10:36.566733+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 6; hex 746f6b656e31; asc token1;; 1: len 1; hex 31; asc 1;; 2: len 8; hex 8000000000000001; asc ;; 2019-08-18T19:10:36.568711+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000000001; asc ;; 1: len 6; hex 000000059c2d; asc -;; 2: len 7; hex 400000002a1dc8; asc @ * ;; 3: len 6; hex 746f6b656e31; asc token1;; 4: len 1; hex 31; asc 1;; 5: len 1; hex 81; asc ;; 367663獲取主鍵鎖堵塞、367662獲取唯一鍵鎖堵塞,死鎖形成 2019-08-18T19:10:36.570313+08:00 8 [Note] InnoDB: TRX ID:(367663) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 8000000000000001; asc ;; 1: len 6; hex 000000059c2d; asc -;; 2: len 7; hex 400000002a1dc8; asc @ * ;; 3: len 6; hex 746f6b656e31; asc token1;; 4: len 1; hex 31; asc 1;; 5: len 1; hex 81; asc ;; 2019-08-18T19:10:36.571199+08:00 8 [Note] InnoDB: Trx(367663) is blocked!!!!! 2019-08-18T19:10:36.572481+08:00 9 [Note] InnoDB: TRX ID:(367662) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 6; hex 746f6b656e31; asc token1;; 1: len 1; hex 31; asc 1;; 2: len 8; hex 8000000000000001; asc ;; 2019-08-18T19:10:36.573073+08:00 9 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
這個時候我們看到s2和s3先是獲取了各自需要的鎖,s3獲取主鍵鎖堵塞,s2獲取唯一鍵鎖堵塞,死鎖出現(xiàn)。如下圖:
到此,關(guān)于“MySQL死鎖的原因及解決方法”的學(xué)習(xí)就結(jié)束了,希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識,請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
網(wǎng)頁題目:MySQL死鎖的原因及解決方法
標題鏈接:http://bm7419.com/article22/psspjc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供App開發(fā)、建站公司、Google、品牌網(wǎng)站設(shè)計、品牌網(wǎng)站制作、軟件開發(fā)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)