久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

mysql數(shù)據(jù)庫中鎖機(jī)制的示例分析

134次閱讀
沒有評論

共計(jì) 7430 個(gè)字符,預(yù)計(jì)需要花費(fèi) 19 分鐘才能閱讀完成。

自動寫代碼機(jī)器人,免費(fèi)開通

這篇文章主要介紹 mysql 數(shù)據(jù)庫中鎖機(jī)制的示例分析,文中介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們一定要看完!

悲觀鎖與樂觀鎖:
悲觀鎖:顧名思義,就是很悲觀,每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人會修改,所以每次在拿數(shù)據(jù)的時(shí)候都會上鎖,這樣別人想拿這個(gè)數(shù)據(jù)就會 block 直到它拿到鎖。傳統(tǒng)的關(guān)系型數(shù)據(jù)庫里邊就用到了很多這種鎖機(jī)制,比如行鎖,表鎖等,讀鎖,寫鎖等,都是在做操作之前先上鎖。

樂觀鎖:顧名思義,就是很樂觀,每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人不會修改,所以不會上鎖,但是在更新的時(shí)候會判斷一下在此期間別人有沒有去更新這個(gè)數(shù)據(jù),可以使用版本號等機(jī)制。樂觀鎖適用于多讀的應(yīng)用類型,這樣可以提高吞吐量,像數(shù)據(jù)庫如果提供類似于 write_condition 機(jī)制的其實(shí)都是提供的樂觀鎖。

表級:引擎 MyISAM,直接鎖定整張表,在你鎖定期間,其它進(jìn)程無法對該表進(jìn)行寫操作。如果你是寫鎖,則其它進(jìn)程則讀也不允許

頁級:引擎 BDB,表級鎖速度快,但沖突多,行級沖突少,但速度慢。所以取了折衷的頁級,一次鎖定相鄰的一組記錄

行級:引擎 INNODB,僅對指定的記錄進(jìn)行加鎖,這樣其它進(jìn)程還是可以對同一個(gè)表中的其它記錄進(jìn)行操作。

上述三種鎖的特性可大致歸納如下:
1)表級鎖:開銷小,加鎖快;不會出現(xiàn)死鎖;鎖定粒度大,發(fā)生鎖沖突的概率最高,并發(fā)度最低。
2)頁面鎖:開銷和加鎖時(shí)間界于表鎖和行鎖之間;會出現(xiàn)死鎖;鎖定粒度界于表鎖和行鎖之間,并發(fā)度一般。
3)行級鎖:開銷大,加鎖慢;會出現(xiàn)死鎖;鎖定粒度最小,發(fā)生鎖沖突的概率最低,并發(fā)度也最高。

三種鎖各有各的特點(diǎn),若僅從鎖的角度來說,表級鎖更適合于以查詢?yōu)橹鳎挥猩倭堪此饕龡l件更新數(shù)據(jù)的應(yīng)用,如 WEB 應(yīng)用;行級鎖更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù),同時(shí)又有并發(fā)查詢的應(yīng)用,如一些在線事務(wù)處理(OLTP)系統(tǒng)。 

MySQL 表級鎖有兩種模式:
1、表共享讀鎖(Table Read Lock)。對 MyISAM 表進(jìn)行讀操作時(shí),它不會阻塞其他用戶對同一表的讀請求,但會阻塞 對同一表的寫操作;
2、表獨(dú)占寫鎖(Table Write Lock)。對 MyISAM 表的寫操作,則會阻塞其他用戶對同一表的讀和寫操作。

MyISAM 表的讀和寫是串行的,即在進(jìn)行讀操作時(shí)不能進(jìn)行寫操作,反之也是一樣。但在一定條件下 MyISAM 表也支持查詢和插入的操作的并發(fā)進(jìn)行,其機(jī)制是通過控制一個(gè)系統(tǒng)變量(concurrent_insert)來進(jìn)行的,當(dāng)其值設(shè)置為 0 時(shí),不允許并發(fā)插入;當(dāng)其值設(shè)置為 1 時(shí),如果 MyISAM 表中沒有空洞(即表中沒有被刪除的行),MyISAM 允許在一個(gè)進(jìn)程讀表的同時(shí),另一個(gè)進(jìn)程從表尾插入記錄;當(dāng)其值設(shè)置為 2 時(shí),無論 MyISAM 表中有沒有空洞,都允許在表尾并發(fā)插入記錄。

MyISAM 鎖調(diào)度是如何實(shí)現(xiàn)的呢,這也是一個(gè)很關(guān)鍵的問題。例如,當(dāng)一個(gè)進(jìn)程請求某個(gè) MyISAM 表的讀鎖,同時(shí)另一個(gè)進(jìn)程也請求同一表的寫鎖,此時(shí) mysql 將會如優(yōu)先處理進(jìn)程呢?通過研究表明,寫進(jìn)程將先獲得鎖(即使讀請求先到鎖等待隊(duì)列)。但這也造成一個(gè)很大的缺陷,即大量的寫操作會造成查詢操作很難獲得讀鎖,從而可能造成永遠(yuǎn)阻塞。所幸我們可以通過一些設(shè)置來調(diào)節(jié) MyISAM 的調(diào)度行為。我們可通過指定參數(shù) low-priority-updates,使 MyISAM 默認(rèn)引擎給予讀請求以優(yōu)先的權(quán)利,設(shè)置其值為 1(set low_priority_updates=1), 使優(yōu)先級降低。

InnoDB 鎖與 MyISAM 鎖的最大不同在于:
1、是支持事務(wù)(TRANCSACTION)。
2、是采用了行級鎖。

我們知道事務(wù)是由一組 SQL 語句組成的邏輯處理單元,其有四個(gè)屬性(簡稱 ACID 屬性),分別為:
原子性(Atomicity):事務(wù)是一個(gè)原子操作單元,其對數(shù)據(jù)的修改,要么全部執(zhí)行,要么全都不執(zhí)行;
一致性(Consistent):在事務(wù)開始和完成時(shí),數(shù)據(jù)都必須保持一致狀態(tài);
隔離性(Isolation):數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制,保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行;
持久性(Durable):事務(wù)完成之后,它對于數(shù)據(jù)的修改是永久性的,即使出現(xiàn)系統(tǒng)故障也能夠保持。

并發(fā)事務(wù)處理帶來的問題
相對于串行處理來說,并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫資源的利用率,提高數(shù)據(jù)庫系統(tǒng)的事務(wù)吞吐量,從而可以支持更多的用戶。但并發(fā)事務(wù)處理也會帶來一些問題,主要包括以下幾種情況。
1、更新丟失(Lost Update):當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行,然后基于最初選定的值更新該行時(shí),由于每個(gè)事務(wù)都不知道其他事務(wù)的存在,就會發(fā)生丟失更新問題--最后的更新覆蓋了由其他事務(wù)所做的更新。例如,兩個(gè)編輯人員制作了同一文檔的電子副本。每個(gè)編輯人員獨(dú)立地更改其副本,然后保存更改后的副本,這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋另一個(gè)編輯人員所做的更改。如果在一個(gè)編輯人員完成并提交事務(wù)之前,另一個(gè)編輯人員不能訪問同一文件,則可避免此問題。
2、臟讀(Dirty Reads):一個(gè)事務(wù)正在對一條記錄做修改,在這個(gè)事務(wù)完成并提交前,這條記錄的數(shù)據(jù)就處于不一致狀態(tài);這時(shí),另一個(gè)事務(wù)也來讀取同一條記錄,如果不加控制,第二個(gè)事務(wù)讀取了這些“臟”數(shù)據(jù),并據(jù)此做進(jìn)一步的處理,就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做”臟讀”。
3、不可重復(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ù)讀”。
4、幻讀(Phantom Reads):一個(gè)事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù),卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù),這種現(xiàn)象就稱為“幻讀”。

事務(wù)隔離級別
在上面講到的并發(fā)事務(wù)處理帶來的問題中,“更新丟失”通常是應(yīng)該完全避免的。但防止更新丟失,并不能單靠數(shù)據(jù)庫事務(wù)控制器來解決,需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來解決,因此,防止更新丟失應(yīng)該是應(yīng)用的責(zé)任。
“臟讀”、“不可重復(fù)讀”和“幻讀”,其實(shí)都是數(shù)據(jù)庫讀一致性問題,必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決。數(shù)據(jù)庫實(shí)現(xiàn)事務(wù)隔離的方式,基本上可分為以下兩種。
1、一種是在讀取數(shù)據(jù)前,對其加鎖,阻止其他事務(wù)對數(shù)據(jù)進(jìn)行修改。
2、另一種是不用加任何鎖,通過一定機(jī)制生成一個(gè)數(shù)據(jù)請求時(shí)間點(diǎn)的一致性數(shù)據(jù)快照(Snapshot),并用這個(gè)快照來提供一定級別(語句級或事務(wù)級)的一致性讀取。從用戶的角度來看,好像是數(shù)據(jù)庫可以提供同一數(shù)據(jù)的多個(gè)版本,因此,這種技術(shù)叫做數(shù)據(jù)多版本并發(fā)控制(MultiVersion Concurrency Control,簡稱 MVCC 或 MCC),也經(jīng)常稱為多版本數(shù)據(jù)庫。

數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格,并發(fā)副作用越小,但付出的代價(jià)也就越大,因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行,這顯然與“并發(fā)”是矛盾的。同時(shí),不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的,比如許多應(yīng)用對“不可重復(fù)讀”和“幻讀”并不敏感,可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。
為了解決“隔離”與“并發(fā)”的矛盾,ISO/ANSI SQL92 定義了 4 個(gè)事務(wù)隔離級別,每個(gè)級別的隔離程度不同,允許出現(xiàn)的副作用也不同,應(yīng)用可以根據(jù)自己的業(yè)務(wù)邏輯要求,通過選擇不同的隔離級別來平衡“隔離”與“并發(fā)”的矛盾。表 20- 5 很好地概括了這 4 個(gè)隔離級別的特性。

讀數(shù)據(jù)一致性及允許的并發(fā)副作用
隔離級別     讀數(shù)據(jù)一致性   臟讀   不可重復(fù)讀   幻讀
未提交讀(Read uncommitted)  最低級別,只能保證不讀取物理上損壞的數(shù)據(jù)     是   是   是
已提交度(Read committed)    語句級 否   是   是
可重復(fù)讀(Repeatable read)  事務(wù)級 否   否   是
可序列化(Serializable)  最高級別,事務(wù)級     否   否   否

最后要說明的是:各具體數(shù)據(jù)庫并不一定完全實(shí)現(xiàn)了上述 4 個(gè)隔離級別,例如,Oracle 只提供 Read committed 和 Serializable 兩個(gè)標(biāo)準(zhǔn)隔離級別,另外還提供自己定義的 Read only 隔離級別;SQL Server 除支持上述 ISO/ANSI SQL92 定義的 4 個(gè)隔離級別外,還支持一個(gè)叫做“快照”的隔離級別,但嚴(yán)格來說它是一個(gè)用 MVCC 實(shí)現(xiàn)的 Serializable 隔離級別。MySQL 支持全部 4 個(gè)隔離級別,但在具體實(shí)現(xiàn)時(shí),有一些特點(diǎn),比如在一些隔離級別下是采用 MVCC 一致性讀,但某些情況下又不是
InnoDB 有兩種模式的行鎖:
1)共享鎖(S):允許一個(gè)事務(wù)去讀一行,阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
   (Select * from table_name where ……lock in share mode)
2)排他鎖(X):允許獲得排他鎖的事務(wù)更新數(shù)據(jù),阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。(select * from table_name where…..for update)
為了允許行鎖和表鎖共存,實(shí)現(xiàn)多粒度鎖機(jī)制;同時(shí)還有兩種內(nèi)部使用的意向鎖(都是表鎖),分別為意向共享鎖和意向排他鎖。
1)意向共享鎖(IS):事務(wù)打算給數(shù)據(jù)行加行共享鎖,事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的 IS 鎖。
2)意向排他鎖(IX):事務(wù)打算給數(shù)據(jù)行加行排他鎖,事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的 IX 鎖。 
InnoDB 行鎖模式兼容性列表
請求鎖模式
  是否兼容
當(dāng)前鎖模式   X   IX  S   IS
X   沖突   沖突   沖突   沖突
IX   沖突   兼容   沖突   兼容
S   沖突   沖突   兼容   兼容
IS   沖突   兼容   兼容   兼容
如果一個(gè)事務(wù)請求的鎖模式與當(dāng)前的鎖兼容,InnoDB 就將請求的鎖授予該事務(wù);反之,如果兩者不兼容,該事務(wù)就要等待鎖釋放。
意向鎖是 InnoDB 自動加的,不需用戶干預(yù)。對于 UPDATE、DELETE 和 INSERT 語句,InnoDB 會自動給涉及數(shù)據(jù)集加排他鎖(X);對于普通 SELECT 語句,InnoDB 不會加任何鎖;事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖。
1、共享鎖(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
2、排他鎖(X):SELECT * FROM table_name WHERE … FOR UPDATE。
InnoDB 行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的,這一點(diǎn) MySQL 與 oracle 不同,后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。InnoDB 這種行鎖實(shí)現(xiàn)特點(diǎn)意味著:只有通過索引條件檢索數(shù)據(jù),InnoDB 才使用行級鎖,否則,InnoDB 將使用表鎖!
在實(shí)際應(yīng)用中,要特別注意 InnoDB 行鎖的這一特性,不然的話,可能導(dǎo)致大量的鎖沖突,從而影響并發(fā)性能。 

查詢表級鎖爭用情況
表鎖定爭奪:
可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態(tài)變量來分析系統(tǒng)上的表鎖定爭奪:

mysql  show status like ‘table%’; 
+———————–+——-+ 
| Variable_name | Value | 
+———————–+——-+ 
| Table_locks_immediate | 2979 | 
| Table_locks_waited | 0 | 
+———————–+——-+ 
2 rows in set (0.00 sec))

如果 Table_locks_waited 的值比較高,則說明存在著較嚴(yán)重的表級鎖爭用情況。
InnoDB 行鎖爭奪:   
可以通過檢查 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 | 0 | 
| InnoDB_row_lock_time_avg | 0 | 
| InnoDB_row_lock_time_max | 0 | 
| InnoDB_row_lock_waits | 0 | 
+——————————-+——-+ 
5 rows in set (0.01 sec)

MyISAM 寫鎖實(shí)驗(yàn):
對 MyISAM 表的讀操作,不會阻塞其他用戶對同一表的讀請求,但會阻塞對同一表的寫請求;對 MyISAM 表的寫操作,則會阻塞其他用戶對同一表的讀和寫操作;MyISAM 表的讀操作與寫操作之間,以及寫操作之間是串行的!根據(jù)如表 20- 2 所示的例子可以知道,當(dāng)一個(gè)線程獲得對一個(gè)表的寫鎖后,只有持有鎖的線程可以對表進(jìn)行更新操作。其他線程的讀、寫操作都會等待,直到鎖被釋放為止。
USER1:

mysql  lock table film_text write;

當(dāng)前 session 對鎖定表的查詢、更新、插入操作都可以執(zhí)行:

mysql  select film_id,title from film_text where film_id = 1001;

USER2:

mysql  select film_id,title from film_text where film_id = 1001;

等待
USER1:
釋放鎖:

mysql  unlock tables;

USER2:
獲得鎖,查詢返回:
InnoDB 存儲引擎的共享鎖實(shí)驗(yàn)

USER1: 
mysql  set autocommit = 0; 
USER2: 
mysql  set autocommit = 0;

USER1:
當(dāng)前 session 對 actor_id=178 的記錄加 share mode 的共享鎖:

mysql  select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

USER2:
其他 session 仍然可以查詢記錄,并也可以對該記錄加 share mode 的共享鎖:

mysql  select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

USER1:
當(dāng)前 session 對鎖定的記錄進(jìn)行更新操作,等待鎖:

mysql  update actor set last_name = ‘MONROE T’ where actor_id = 178;

等待
USER2:
其他 session 也對該記錄進(jìn)行更新操作,則會導(dǎo)致死鎖退出:

mysql  update actor set last_name = ‘MONROE T’ where actor_id = 178;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
USER1:
獲得鎖后,可以成功更新:

mysql  update actor set last_name = ‘MONROE T’ where actor_id = 178; 
Query OK, 1 row affected (17.67 sec) 
Rows matched: 1 Changed: 1 Warnings: 0

InnoDB 存儲引擎的排他鎖例子

USER1: 
mysql  set autocommit = 0; 
USER2: 
mysql  set autocommit = 0;

USER1:
當(dāng)前 session 對 actor_id=178 的記錄加 for update 的排它鎖:

mysql  select actor_id,first_name,last_name from actor where actor_id = 178 for update;

USER2:
其他 session 可以查詢該記錄,但是不能對該記錄加共享鎖,會等待獲得鎖:

mysql  select actor_id,first_name,last_name from actor where actor_id = 178;

USER1:
當(dāng)前 session 可以對鎖定的記錄進(jìn)行更新操作,更新后釋放鎖:

mysql  update actor set last_name = ‘MONROE T’ where actor_id = 178;

USER2:
其他 session 獲得鎖,得到其他 session 提交的記錄:

mysql  select actor_id,first_name,last_name from actor where actor_id = 178 for update;

更新性能優(yōu)化的幾個(gè)重要參數(shù)
bulk_insert_buffer_size
批量插入緩存大小, 這個(gè)參數(shù)是針對 MyISAM 存儲引擎來說的. 適用于在一次性插入 100-1000+ 條記錄時(shí), 提高效率. 默認(rèn)值是 8M. 可以針對數(shù)據(jù)量的大小, 翻倍增加.
concurrent_insert
并發(fā)插入, 當(dāng)表沒有空洞 (刪除過記錄), 在某進(jìn)程獲取讀鎖的情況下, 其他進(jìn)程可以在表尾部進(jìn)行插入.
值可以設(shè) 0 不允許并發(fā)插入, 1 當(dāng)表沒有空洞時(shí), 執(zhí)行并發(fā)插入, 2 不管是否有空洞都執(zhí)行并發(fā)插入.
默認(rèn)是 1 針對表的刪除頻率來設(shè)置.
delay_key_write
針對 MyISAM 存儲引擎, 延遲更新索引. 意思是說,update 記錄時(shí), 先將數(shù)據(jù) up 到磁盤, 但不 up 索引, 將索引存在內(nèi)存里, 當(dāng)表關(guān)閉時(shí), 將內(nèi)存索引, 寫到磁盤. 值為 0 不開啟, 1 開啟. 默認(rèn)開啟.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延遲插入, 將數(shù)據(jù)先交給內(nèi)存隊(duì)列, 然后慢慢地插入. 但是這些配置, 不是所有的存儲引擎都支持, 目前來看, 常用的 InnoDB 不支持, MyISAM 支持. 根據(jù)實(shí)際情況調(diào)大, 一般默認(rèn)夠用了。

以上是“mysql 數(shù)據(jù)庫中鎖機(jī)制的示例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!希望分享的內(nèi)容對大家有幫助,更多相關(guān)知識,歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道!

向 AI 問一下細(xì)節(jié)

正文完
 
丸趣
版權(quán)聲明:本站原創(chuàng)文章,由 丸趣 2023-12-04發(fā)表,共計(jì)7430字。
轉(zhuǎn)載說明:除特殊說明外本站除技術(shù)相關(guān)以外文章皆由網(wǎng)絡(luò)搜集發(fā)布,轉(zhuǎn)載請注明出處。
評論(沒有評論)
主站蜘蛛池模板: 东海县| 禹城市| 高邑县| 石泉县| 武义县| 祁门县| 镇安县| 扎囊县| 卓尼县| 澳门| 合肥市| 旌德县| 衡水市| 开封市| 哈尔滨市| 宕昌县| 原阳县| 安西县| 无锡市| 辉南县| 东莞市| 紫金县| 蓝田县| 迁西县| 武清区| 陈巴尔虎旗| 定边县| 汉阴县| 新田县| 漯河市| 绥宁县| 洞头县| 海淀区| 壤塘县| 云梦县| 金塔县| 郎溪县| 彭州市| 临泉县| 吴堡县| 太原市|