這個問題,問的就有問題,你對同一條記錄,同時想將use設(shè)置成1或2,業(yè)務(wù)邏輯就有問題啊。我原來處理過類似的問題,介紹一下我的處理方式,在use表中,增加一個字段b,默認(rèn)值是0,在事物一開始的時候,先將你要處理的那條記錄的b值,設(shè)置成1,再事物全都處理完畢后,在將1更新成0。如果事物一開始發(fā)現(xiàn)這條記錄的b值是1,則直接提示用戶,正在對同一條數(shù)據(jù)進(jìn)行處理,請稍后在試,代碼里直接就return了,不往下進(jìn)行。
網(wǎng)站建設(shè)哪家好,找創(chuàng)新互聯(lián)!專注于網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、微信平臺小程序開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了南山免費(fèi)建站歡迎大家使用!
說白了,就是用一個字段,把一條記錄鎖住,事物一開始先判斷鎖沒鎖,如果鎖了就提示用戶,如果沒鎖,就鎖住,然后向下進(jìn)行,但是,無論是正常處理完,還是回滾,或者是拋出異常,都不要忘了把鎖解開。
我們經(jīng)常會遇到操作一張大表,發(fā)現(xiàn)操作時間過長或影響在線業(yè)務(wù)了,想要回退大表操作的場景。在我們停止大表操作之后,等待回滾是一個很漫長的過程,盡管你可能對知道一些縮短時間的方法,處于對生產(chǎn)環(huán)境數(shù)據(jù)完整性的敬畏,也會選擇不做介入。最終選擇不作為的原因大多源于對操作影響的不確定性。實踐出真知,下面針對兩種主要提升事務(wù)回滾速度的方式進(jìn)行驗證,一種是提升操作可用內(nèi)存空間,一種是通過停實例,禁用 redo 回滾方式進(jìn)行進(jìn)行驗證。
仔細(xì)閱讀過官方手冊的同學(xué),一定留意到了對于提升大事務(wù)回滾效率,官方提供了兩種方法:一是增加 innodb_buffer_pool_size 參數(shù)大小,二是合理利用 innodb_force_recovery=3 參數(shù),跳過事務(wù)回滾過程。第一種方式比較溫和,innodb_buffer_pool_size 參數(shù)是可以動態(tài)調(diào)整的,可行性也較高。第二種方式相較之下較暴力,但效果較好。
兩種方式各有自己的優(yōu)點,第一種方式對線上業(yè)務(wù)系統(tǒng)影響較小,不會中斷在線業(yè)務(wù)。第二種方式效果更顯著,會短暫影響業(yè)務(wù)連續(xù),回滾所有沒有提交的事務(wù)。
這是我見的一個文檔,雖然我看不懂,你看看有沒有幫助
MySQL死鎖問題的相關(guān)知識是本文我們主要要介紹的內(nèi)容,接下來我們就來一一介紹這部分內(nèi)容,希望能夠?qū)δ兴鶐椭?/p>
1、MySQL常用存儲引擎的鎖機(jī)制
MyISAM和MEMORY采用表級鎖(table-level locking)
BDB采用頁面鎖(page-level locking)或表級鎖,默認(rèn)為頁面鎖
InnoDB支持行級鎖(row-level locking)和表級鎖,默認(rèn)為行級鎖
2、各種鎖特點
表級鎖:開銷小,加鎖快;不會出現(xiàn)死鎖;鎖定粒度大,發(fā)生鎖沖突的概率最高,并發(fā)度最低
行級鎖:開銷大,加鎖慢;會出現(xiàn)死鎖;鎖定粒度最小,發(fā)生鎖沖突的概率最低,并發(fā)度也最高
頁面鎖:開銷和加鎖時間界于表鎖和行鎖之間;會出現(xiàn)死鎖;鎖定粒度界于表鎖和行鎖之間,并發(fā)度一般
3、各種鎖的適用場景
表級鎖更適合于以查詢?yōu)橹?,只有少量按索引條件更新數(shù)據(jù)的應(yīng)用,如Web應(yīng)用
行級鎖則更適合于有大量按索引條件并發(fā)更新數(shù)據(jù),同時又有并發(fā)查詢的應(yīng)用,如一些在線事務(wù)處理系統(tǒng)
4、死鎖
是指兩個或兩個以上的進(jìn)程在執(zhí)行過程中,因爭奪資源而造成的一種互相等待的現(xiàn)象,若無外力作用,它們都將無法推進(jìn)下去。
表級鎖不會產(chǎn)生死鎖。所以解決死鎖主要還是針對于最常用的InnoDB。
5、死鎖舉例分析
在MySQL中,行級鎖并不是直接鎖記錄,而是鎖索引。索引分為主鍵索引和非主鍵索引兩種,如果一條sql語句操作了主鍵索引,MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引,MySQL會先鎖定該非主鍵索引,再鎖定相關(guān)的主鍵索引。
在UPDATE、DELETE操作時,MySQL不僅鎖定WHERE條件掃描過的所有索引記錄,而且會鎖定相鄰的鍵值,即所謂的next-key locking。
例如,一個表db。tab_test,結(jié)構(gòu)如下:
id:主鍵;
state:狀態(tài);
time:時間;
索引:idx_1(state,time)
出現(xiàn)死鎖日志如下:
?***(1) TRANSACTION:
?TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read
?mysql tables in use 1, locked 1
?LOCK WAIT 3 lock struct(s), heap size 320
?MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
?update tab_test set state=1064,time=now() where state=1061 and time date_sub(now(), INTERVAL 30 minute) (任務(wù)1的sql語句)
?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任務(wù)1等待的索引記錄)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) TRANSACTION:
?TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
?mysql tables in use 1, locked 1
?3 lock struct(s), heap size 320, undo log entries 1
?MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任務(wù)2的sql語句)
?*** (2) HOLDS THE LOCK(S): (任務(wù)2已獲得的鎖)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任務(wù)2等待的鎖)
?RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting
?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
?0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;
?*** WE ROLL BACK TRANSACTION (1)
?(回滾了任務(wù)1,以解除死鎖)
原因分析:
當(dāng)“update tab_test set state=1064,time=now() where state=1061 and time date_sub(now(), INTERVAL 30 minute)”執(zhí)行時,MySQL會使用idx_1索引,因此首先鎖定相關(guān)的索引記錄,因為idx_1是非主鍵索引,為執(zhí)行該語句,MySQL還會鎖定主鍵索引。
假設(shè)“update tab_test set state=1067,time=now () where id in (9921180)”幾乎同時執(zhí)行時,本語句首先鎖定主鍵索引,由于需要更新state的值,所以還需要鎖定idx_1的某些索引記錄。
這樣第一條語句鎖定了idx_1的記錄,等待主鍵索引,而第二條語句則鎖定了主鍵索引記錄,而等待idx_1的記錄,這樣死鎖就產(chǎn)生了。
6、解決辦法
拆分第一條sql,先查出符合條件的主鍵值,再按照主鍵更新記錄:
?select id from tab_test where state=1061 and time date_sub(now(), INTERVAL 30 minute);
?update tab_test state=1064,time=now() where id in(......);
鎖是需要事務(wù)結(jié)束后才釋放的。
一個是 MVCC,一個是兩階段鎖協(xié)議。
為什么要并發(fā)控制呢?是因為多個用戶同時操作 MySQL 的時候,為了提高并發(fā)性能并且要求如同多個用戶的請求過來之后如同串行執(zhí)行的一樣(為了解決臟讀、不可重復(fù)讀、幻讀)
官方定義:
兩階段鎖協(xié)議是指所有事務(wù)必須分兩個階段對數(shù)據(jù)加鎖和解鎖,在對任何數(shù)據(jù)進(jìn)行讀、寫操作之前,事務(wù)首先要獲得對該數(shù)據(jù)的封鎖;在釋放一個封鎖之后,事務(wù)不再申請和獲得任何其他封鎖。
對應(yīng)到 MySQL 上分為兩個階段:
但是兩階段鎖協(xié)議不要求事務(wù)必須一次將所有需要使用的數(shù)據(jù)加鎖(innodb在需要的索引列數(shù)據(jù)才鎖行),并且在加鎖階段沒有順序要求,所以這種并發(fā)控制方式會形成死鎖。
MySQL有兩種死鎖處理方式:
死鎖檢測 (默認(rèn)開啟)
死鎖檢測的原理是構(gòu)建一個以事務(wù)為頂點、鎖為邊的有向圖,判斷有向圖是否存在環(huán),存在即有死鎖。
回滾
檢測到死鎖之后,選擇插入更新或者刪除的行數(shù)最少的事務(wù)回滾,基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段來判斷。
收集死鎖信息:
減少死鎖:
死鎖解決: