6.mysql 锁机制
概述
定義： 鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或者線程并發(fā)訪問某一資源的機(jī)制
在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（如CPU,RAM,IO等）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā) 訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素。從這個(gè)角度 來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。
實(shí)際場景案例：生活購物
打個(gè)比方，我們在淘寶上搶購一件商品，商品只有一件庫存，這個(gè)時(shí)候如果有很多人想去買，那么如何解決是你買到還是其他人買到的問題？
這里肯定要用到事務(wù)，我們先從庫存表中取出物品數(shù)量，然后插入訂單，付款后插入付款記錄表 然后更新商品數(shù)量。在這個(gè)過程中，使用鎖可以對有限資源進(jìn)行保護(hù)，解決隔離和并發(fā)的矛盾
鎖分類
從數(shù)據(jù)操作的類型（讀、寫）分
讀鎖（共享鎖）：針對同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響 ?
寫鎖（排它鎖）：當(dāng)前寫操作沒有完成前，它會(huì)阻斷其他寫鎖和讀鎖。
從對數(shù)據(jù)操作的顆粒度:表鎖，行鎖
三鎖
開銷、加鎖速度、死鎖、粒度、并發(fā)性能。只能就具體應(yīng)用的特點(diǎn)來說哪種鎖更合適
表鎖（偏讀）
特點(diǎn) ? 偏向MyISAM存儲(chǔ)引擎，開銷小，加鎖快，無死鎖，鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)最低
案例分析 ? 建表SQL ? 加讀鎖 ? 加寫鎖
【表級(jí)鎖分析-建表sql】 create table mylock( id int not null primary key auto_increment, name varchar(20) )engine myisam;insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c'); insert into mylock(name) values('d'); insert into mylock(name) values('e');select * from mylock;【手動(dòng)添加表鎖】 lock table 表名 read(write),表名2 read(write),其他; lock table mylock read,book write;【查看已添加過的鎖】 show open tables;【釋放表鎖】 unlock tables;加讀鎖 1.session1 獲得表mylock的read鎖定 , lock table mylock read;session2連接終端2.session1 可以查詢該表的記錄，select * from mylock; -->當(dāng)前session1不能查詢其他沒有鎖定的表，select * from book; session2等其他終端也可以查詢該表的記錄 -->其他session可以查詢或者更新未鎖定的表，select * from book; update book set card=51 where book_id=1;3. 當(dāng)前session中插入或者更新 鎖定的表 都會(huì)提示錯(cuò)誤； insert into mylock(name) values('ee'); update mylock set name='a1' where id=1; 其他session插入或者更新鎖定的表會(huì)一直等待，直到獲得鎖， insert into mylock(name) values('ee'); update mylock set name='a1' where id=1; 4.釋放鎖后，其他session獲得鎖，對應(yīng)插入或者更新操作完成。加寫鎖 我們?yōu)閙ylock表加寫鎖（myISAM存儲(chǔ)引擎的寫阻塞讀的例子）1. session1 獲得表的write鎖定，unlock tables; lock tables mylock write;待session1開啟寫鎖后，session2在連接終端2.當(dāng)前session 對鎖定的表 查詢+ 更新 + 插入操作都可以執(zhí)行；select * from mylock where id=1; update mylock set name='a11' where id=1; insert into mylock(name) values('ff'); 其他session對鎖定表的查詢被阻塞，需要等待鎖被釋放； select * from mylock where id=1;更新與插入操作，會(huì)等待釋放鎖。update mylock set name='a11' where id=1; insert into mylock(name) values('ff'); 備注：可以嘗試用不同的id進(jìn)行測試，mysql是有緩存的，第二次的條件會(huì)從緩存中取得，影響鎖效果的演示3.釋放鎖 unlock tables; 其他session獲得鎖，返回查詢結(jié)果############################案例結(jié)論###################### myISAM 在執(zhí)行查詢語句（select）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行增刪改操作前，會(huì)自動(dòng)給涉及的表加寫鎖。mysql的表級(jí)鎖有兩種模式：表共享讀鎖（Table read lock）表獨(dú)占寫鎖 ( Table write lock ) 對比====================== 鎖類型 可否兼容 讀鎖 寫鎖 讀鎖 是 是 否 寫鎖 是 否 否結(jié)論===================== 結(jié)合上述對比,所以對myISAM表進(jìn)行操作，會(huì)有以下情況： 1.對MyISAM表的讀操作（加讀鎖），不會(huì)阻塞其他進(jìn)程對同一張表的讀請求，但是會(huì)阻塞對同一張表的寫請求。只有當(dāng)讀鎖被釋放后，才會(huì)執(zhí)行其他進(jìn)程的寫操作。 2.對myISAM表的寫操作（加寫鎖），會(huì)阻塞其他進(jìn)程對同一張表的讀和寫操作，只有當(dāng)寫操作釋放后，才會(huì)執(zhí)行其他進(jìn)程的讀寫操作簡而言之，就是讀鎖會(huì)阻塞寫，但是不會(huì)阻塞讀，而寫鎖則會(huì)把讀和寫都阻塞。###########################表鎖分析#################### 【看看那些表被加鎖了】 show open tables;【如何分析表鎖定】 可以通過檢查table_locks_waited 和table_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定； show status like 'table%';這里有兩個(gè)狀態(tài)變量記錄mysql內(nèi)部表鎖定的情況，兩個(gè)變量說明如下： table_locks_waited：產(chǎn)生表級(jí)鎖定的次數(shù)，表示可以立即獲取的查詢次數(shù)，每立即獲取鎖值加1； table_locks_immediate：出現(xiàn)表級(jí)鎖定爭用而發(fā)生等待的次數(shù)（不能立即獲取鎖的次數(shù)，每等待一次鎖值加1）此值高則說明存在著比較嚴(yán)重的表級(jí)鎖爭用的情況；此外，myisam的讀寫鎖調(diào)度是寫優(yōu)先，這也是mysiam不適合做寫為主表的引擎。因?yàn)閷戞i后，其他線程不能做任何 操作，大量的更新會(huì)使查詢很難得到鎖，從而造成永久阻塞；行鎖（偏寫）：
特點(diǎn)：
偏向InnoDB存儲(chǔ)引擎，開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。 InnoDB與MyISAM的最大不同有兩點(diǎn)：一是支持事務(wù)（TRANSACTION）;二是采用了行級(jí)鎖
由于行鎖支持事務(wù)，復(fù)習(xí)老知識(shí)：
事務(wù)（Transation）及其ACID屬性
事務(wù)transation 及其ACID屬性
事務(wù)是由一組sql組成的邏輯處理單元，事務(wù)具有以下四個(gè)屬性，通常簡稱事務(wù)的ACID屬性。 ?
原子性（Atomicity）: 事務(wù)是一個(gè)原子操作單元，其對數(shù)據(jù)的修改，要么全部執(zhí)行，要么全部不執(zhí)行。 ?
一致性（Consistent）:在事務(wù)開始和完成時(shí)，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持?jǐn)?shù)據(jù)的完整性；事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹索引或者雙向鏈表）也都是必須是正確的。 ?
隔離性（Isolation）:數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的‘獨(dú)立’環(huán)境執(zhí)行。這就意味著事務(wù)處理過程的中間狀態(tài)對外部是不可見的，反之亦然。 ?
持久性（Durable）:事務(wù)完成后，它對數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。
并發(fā)事務(wù)處理帶來的問題 ?
更新丟失（lost update） 當(dāng)兩個(gè)或者多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在（隔離性），就會(huì)發(fā)生丟失更新問題 ? -----最后的更新覆蓋了由其他事務(wù)所做的更新。 例如：兩個(gè)程序員在修改同一個(gè)java文件，每個(gè)程序員獨(dú)立的更改自己的副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其 ? 更改副本的編輯人員覆蓋了前一個(gè)程序員所做的更改。
臟讀（Dirty reads） ? 一個(gè)事務(wù)正在對一條記錄做修改，在這個(gè)事務(wù)完成并提交前，這條記錄的數(shù)據(jù)就處于不一致的狀態(tài)；這時(shí)候，另一個(gè)事務(wù)也來讀取同一條記錄， 這時(shí)候如果不加以控制，第二個(gè)事務(wù)讀取了這些‘臟數(shù)據(jù)’，并據(jù)此做了進(jìn)一步的處理，就會(huì)產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象的叫做‘臟讀’
? ? ? 總結(jié)：事務(wù)A讀取到了事務(wù)B已修改但是未提交的數(shù)據(jù)，并在這個(gè)數(shù)據(jù)的基礎(chǔ)上做了操作。此時(shí)，如果事務(wù)B進(jìn)行了回滾，A讀取的數(shù)據(jù)就無效，違反了一致性的要求
? ? 不可重復(fù)讀（Non-Repeatable Reads）一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間，再次讀取以前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變、或者某些記錄已經(jīng)被刪除了。這種現(xiàn)象叫做‘不可重復(fù)讀’。
總結(jié)：事務(wù)A讀取到了事務(wù)B已經(jīng)提交的修改數(shù)據(jù)，不符合隔離性。
幻讀（Phantom reads） 一個(gè)事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就被稱為‘幻讀’
總結(jié)：事務(wù)A讀取到了事務(wù)B提交的新增數(shù)據(jù)，不符合隔離性
注意：幻讀和臟讀有點(diǎn)類似 ? 臟讀是事務(wù)B里面修改了數(shù)據(jù) ? 幻讀是事務(wù)B里面新增了數(shù)據(jù)
事務(wù)隔離級(jí)別
| 事務(wù)隔離級(jí)別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
| 讀未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重復(fù)讀（read-committed） | 否 | 是 | 是 |
| 可重復(fù)讀（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
總結(jié)：數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用就越小，但是付出的代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行，
######################建表SQL################## create table test_innodb_lock(a int(11),b varchar(16)) engine=innodb;insert into test_innodb_lock values(1,"aaa"); insert into test_innodb_lock values(2,"222"); insert into test_innodb_lock values(3,"3333"); insert into test_innodb_lock values(4,"4000"); insert into test_innodb_lock values(5,"5000"); insert into test_innodb_lock values(6,"6000"); insert into test_innodb_lock values(7,"7000"); insert into test_innodb_lock values(8,"8000"); insert into test_innodb_lock values(9,"9000"); insert into test_innodb_lock values(1,"b1");create index test_innodb_lock_a on test_innodb_lock(a); create index test_innodb_lock_b on test_innodb_lock(b);select * from test_innodb_lock; ######################行鎖定基本演示##################行鎖定基本演示： 1.session1 -> set autocommit=0;session2 ->set autocommit=0;2.session1 -> update test_innodb_lock set b='b1' where a=1; #更新但是不提交，沒有手寫commitsession2 -> update test_innodb_lock set b='b2' where a=1; #session2 被阻塞，只能等待3. session1 -> commit; #提交更新 session2 -> 接觸阻塞，更新正常進(jìn)行 4. session2 執(zhí)行命令commit;注意：若果session1更新會(huì)話a=1,session2更新會(huì)話a=9,互相并不影響。 ######################無索引行鎖升級(jí)為表鎖################## varchar? 不用 ' '? 導(dǎo)致系統(tǒng)自動(dòng)轉(zhuǎn)換類型, 行鎖變表鎖 session1 -> update test_innodb_lock set a=41 where b=4000;session2 -> update test_innodb_lock set b="9000" where a=9; ######################間隙鎖危害################## 間隙鎖的危害 案例：間隙鎖帶來的插入問題 1.session1 -> update test_innodb_lock set b=a*20 where a >1 and a <5;session2 ->insert into test_innodb_lock values(2,'200'); #阻塞產(chǎn)生，暫時(shí)不能插入 2.session1 -> commit;session2 -> 阻塞解除，完成插入【什么是間隙鎖】 當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排它鎖時(shí)候，innoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖； 對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP）” InnoDB 也會(huì)對這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖（next-key鎖）、【危害】 因?yàn)镼uery執(zhí)行過程中通過范圍查找的話，他會(huì)鎖定整個(gè)范圍內(nèi)所有的索引鍵值，即使這個(gè)鍵值不存在。 間隙鎖有同一個(gè)比較致命的弱點(diǎn)，就是當(dāng)鎖定一個(gè)范圍鍵值之后，即使某些不存在的鍵值也會(huì)被無辜的鎖定，而造成在鎖定的時(shí)候 無法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)。在某些場景下這可能會(huì)對性能造成很大的損失。 ######################面試題：常考如何鎖定一行##################面試題：如何鎖定一行 1.session1 -> begin; select * from test_innodb_lock where a=8 for update;session2 -> update test_innodb_lock set b='8001' where a=8;session1 -> commit;解釋：select xxx... from XX... for update;鎖定某一行后，其他的操作會(huì)被阻塞，直到鎖定行的會(huì)話提交commit;######################案例結(jié)論################## 案例總結(jié)：innodb存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來的性能損耗可能比表級(jí)鎖定會(huì)更好一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于myISAM的表級(jí)鎖定。 當(dāng)系統(tǒng)并發(fā)量較高的時(shí)候，innodb的整體性能和myISAM相比就會(huì)有比較顯著的優(yōu)勢了。但是，innodb的行級(jí)鎖定同樣也有其脆弱的一面(行鎖變表鎖)，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候，可能會(huì)讓innodb的整體性能表現(xiàn)不僅不能比myISAM高，甚至?xí)?#######################行鎖分析###################### 行鎖分析 【如何分析行鎖定】 通過InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況 mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 20428 | | Innodb_row_lock_time_avg | 4085 | | Innodb_row_lock_time_max | 5453 | | Innodb_row_lock_waits | 5 | +-------------------------------+-------+ 對各個(gè)狀態(tài)量的說明： Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量 Innodb_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定的總時(shí)間長度 Innodb_row_lock_time_avg：每次等待所花費(fèi)的平均時(shí)間 Innodb_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長的一次所花的時(shí)間 Innodb_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的此時(shí)對于這五個(gè)狀態(tài)變量，比較重要的是以下三項(xiàng) Innodb_row_lock_time_avg：等待平均時(shí)長 Innodb_row_lock_waits：等待總次數(shù) Innodb_row_lock_time：等待總時(shí)長尤其是當(dāng)?shù)却螖?shù)很高，而且每次等待時(shí)長也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待， 然后根據(jù)分析結(jié)果著手制定優(yōu)化計(jì)劃。
?
優(yōu)化建議 ?
盡可能讓所有數(shù)據(jù)檢索都通過索引來完成，避免無索引行鎖升級(jí)為表鎖 ?
合理設(shè)計(jì)索引，盡量縮小鎖的范圍 ?
頁鎖：
開銷和加鎖時(shí)間界于表鎖和行鎖之間：會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。 ? 了解一下即可
?
?
轉(zhuǎn)載于:https://www.cnblogs.com/weixiaotao/p/10741500.html
總結(jié)
以上是生活随笔為你收集整理的6.mysql 锁机制的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： element-ui select
- 下一篇： mysql to char 用法_pos