悲觀鎖與樂觀鎖：

悲觀鎖：顧名思義虐秋，就是很悲觀榕茧，每次去拿數(shù)據(jù)的時候都認為別人會修改，所以每次在拿數(shù)據(jù)的時候都會上鎖客给，這樣別人想拿這個數(shù)據(jù)就會block直到它拿到鎖用押。傳統(tǒng)的關(guān)系型數(shù)據(jù)庫里邊就用到了很多這種鎖機制，比如行鎖靶剑，表鎖等蜻拨，讀鎖，寫鎖等桩引，都是在做操作之前先上鎖缎讼。

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

表級：引擎?MyISAM谅辣，直接鎖定整張表迫悠，在你鎖定期間，其它進程無法對該表進行寫操作承边。如果你是寫鎖桶蛔，則其它進程則讀也不允許

頁級：引擎?BDB蛋辈，表級鎖速度快桶错，但沖突多航唆，行級沖突少胀蛮，但速度慢院刁。所以取了折衷的頁級，一次鎖定相鄰的一組記錄

行級：引擎?INNODB粪狼，僅對指定的記錄進行加鎖退腥，這樣其它進程還是可以對同一個表中的其它記錄進行操作。

上述三種鎖的特性可大致歸納如下：

1）?表級鎖：開銷小再榄，加鎖快狡刘；不會出現(xiàn)死鎖；鎖定粒度大困鸥，發(fā)生鎖沖突的概率最高嗅蔬，并發(fā)度最低。

2）?頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間疾就；會出現(xiàn)死鎖澜术；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般猬腰。

3）?行級鎖：開銷大鸟废，加鎖慢；會出現(xiàn)死鎖姑荷；鎖定粒度最小盒延，發(fā)生鎖沖突的概率最低，并發(fā)度也最高鼠冕。

?三種鎖各有各的特點添寺，若僅從鎖的角度來說，表級鎖更適合于以查詢?yōu)橹餍阜眩挥猩倭堪此饕龡l件更新數(shù)據(jù)的應(yīng)用计露，如WEB應(yīng)用；行級鎖更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用薄坏，如一些在線事務(wù)處理（OLTP）系統(tǒng)趋厉。

?MySQL表級鎖有兩種模式：

1、表共享讀鎖（Table Read Lock）胶坠。對MyISAM表進行讀操作時君账，它不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫操作沈善；

2乡数、表獨占寫鎖（Table Write Lock）。對MyISAM表的寫操作闻牡，則會阻塞其他用戶對同一表的讀和寫操作净赴。

?MyISAM表的讀和寫是串行的，即在進行讀操作時不能進行寫操作罩润，反之也是一樣玖翅。但在一定條件下MyISAM表也支持查詢和插入的操作的并發(fā)進行，其機制是通過控制一個系統(tǒng)變量（concurrent_insert）來進行的割以，當其值設(shè)置為0時金度，不允許并發(fā)插入；當其值設(shè)置為1時严沥，如果MyISAM表中沒有空洞（即表中沒有被刪除的行）猜极，MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄消玄；當其值設(shè)置為2時跟伏，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄翩瓜。

MyISAM鎖調(diào)度是如何實現(xiàn)的呢受扳，這也是一個很關(guān)鍵的問題。例如奥溺，當一個進程請求某個MyISAM表的讀鎖辞色，同時另一個進程也請求同一表的寫鎖，此時mysql將會如優(yōu)先處理進程呢浮定？通過研究表明相满，寫進程將先獲得鎖（即使讀請求先到鎖等待隊列）。但這也造成一個很大的缺陷桦卒，即大量的寫操作會造成查詢操作很難獲得讀鎖立美，從而可能造成永遠阻塞。所幸我們可以通過一些設(shè)置來調(diào)節(jié)MyISAM的調(diào)度行為方灾。我們可通過指定參數(shù)low-priority-updates建蹄，使MyISAM默認引擎給予讀請求以優(yōu)先的權(quán)利碌更，設(shè)置其值為1（set low_priority_updates=1),使優(yōu)先級降低。

InnoDB鎖與MyISAM鎖的最大不同在于：

1洞慎、是支持事務(wù)（TRANCSACTION）痛单。

2、是采用了行級鎖劲腿。

我們知道事務(wù)是由一組SQL語句組成的邏輯處理單元旭绒，其有四個屬性（簡稱ACID屬性），分別為：

原子性（Atomicity）：事務(wù)是一個原子操作單元焦人，其對數(shù)據(jù)的修改挥吵，要么全部執(zhí)行，要么全都不執(zhí)行花椭；

一致性（Consistent）：在事務(wù)開始和完成時忽匈，數(shù)據(jù)都必須保持一致狀態(tài)；

隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制矿辽，保證事務(wù)在不受外部并發(fā)操作影響的“獨立”環(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）：當兩個或多個事務(wù)選擇同一行嘱根，然后基于最初選定的值更新該行時，由于每個事務(wù)都不知道其他事務(wù)的存在巷懈，就會發(fā)生丟失更新問題－－最后的更新覆蓋了由其他事務(wù)所做的更新该抒。例如，兩個編輯人員制作了同一文檔的電子副本顶燕。每個編輯人員獨立地更改其副本凑保，然后保存更改后的副本，這樣就覆蓋了原始文檔涌攻。最后保存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改欧引。如果在一個編輯人員完成并提交事務(wù)之前，另一個編輯人員不能訪問同一文件恳谎，則可避免此問題芝此。

2憋肖、臟讀（Dirty Reads）：一個事務(wù)正在對一條記錄做修改，在這個事務(wù)完成并提交前婚苹，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)岸更；這時，另一個事務(wù)也來讀取同一條記錄膊升，如果不加控制坐慰，第二個事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進一步的處理用僧，就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系结胀。這種現(xiàn)象被形象地叫做"臟讀"。

3责循、不可重復(fù)讀（Non-Repeatable Reads）：一個事務(wù)在讀取某些數(shù)據(jù)后的某個時間糟港，再次讀取以前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變院仿、或某些記錄已經(jīng)被刪除了秸抚！這種現(xiàn)象就叫做“不可重復(fù)讀”。

4歹垫、幻讀（Phantom Reads）：一個事務(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ù)據(jù)庫讀一致性問題凄鼻，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機制來解決。數(shù)據(jù)庫實現(xiàn)事務(wù)隔離的方式聚假，基本上可分為以下兩種块蚌。

1、一種是在讀取數(shù)據(jù)前魔策，對其加鎖匈子，阻止其他事務(wù)對數(shù)據(jù)進行修改。

2闯袒、另一種是不用加任何鎖虎敦，通過一定機制生成一個數(shù)據(jù)請求時間點的一致性數(shù)據(jù)快照（Snapshot)游岳，并用這個快照來提供一定級別（語句級或事務(wù)級）的一致性讀取。從用戶的角度來看其徙，好像是數(shù)據(jù)庫可以提供同一數(shù)據(jù)的多個版本胚迫，因此，這種技術(shù)叫做數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control唾那，簡稱MVCC或MCC）访锻，也經(jīng)常稱為多版本數(shù)據(jù)庫。

數(shù)據(jù)庫的事務(wù)隔離越嚴格闹获，并發(fā)副作用越小期犬，但付出的代價也就越大，因為事務(wù)隔離實質(zhì)上就是使事務(wù)在一定程度上?“串行化”進行避诽，這顯然與“并發(fā)”是矛盾的龟虎。同時，不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的沙庐，比如許多應(yīng)用對“不可重復(fù)讀”和“幻讀”并不敏感鲤妥，可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。

為了解決“隔離”與“并發(fā)”的矛盾拱雏，ISO/ANSI SQL92定義了4個事務(wù)隔離級別棉安，每個級別的隔離程度不同，允許出現(xiàn)的副作用也不同铸抑，應(yīng)用可以根據(jù)自己的業(yè)務(wù)邏輯要求贡耽，通過選擇不同的隔離級別來平衡“隔離”與“并發(fā)”的矛盾。表20-5很好地概括了這4個隔離級別的特性羡滑。

讀數(shù)據(jù)一致性及允許的并發(fā)副作用

隔離級別

讀數(shù)據(jù)一致性臟讀不可重復(fù)讀幻讀

未提交讀（Read uncommitted）最低級別菇爪，只能保證不讀取物理上損壞的數(shù)據(jù)是是是

已提交度（Read committed）語句級否是是

可重復(fù)讀（Repeatable read）事務(wù)級否否是

可序列化（Serializable）最高級別，事務(wù)級否否否

最后要說明的是：各具體數(shù)據(jù)庫并不一定完全實現(xiàn)了上述4個隔離級別柒昏，例如，Oracle只提供Read committed和Serializable兩個標準隔離級別熙揍，另外還提供自己定義的Read only隔離級別职祷；SQL Server除支持上述ISO/ANSI SQL92定義的4個隔離級別外，還支持一個叫做“快照”的隔離級別届囚，但嚴格來說它是一個用MVCC實現(xiàn)的Serializable隔離級別有梆。MySQL支持全部4個隔離級別，但在具體實現(xiàn)時意系，有一些特點泥耀，比如在一些隔離級別下是采用MVCC一致性讀，但某些情況下又不是

InnoDB有兩種模式的行鎖：

1）共享鎖（S）：允許一個事務(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)

為了允許行鎖和表鎖共存夸溶，實現(xiàn)多粒度鎖機制逸吵；同時還有兩種內(nèi)部使用的意向鎖（都是表鎖），分別為意向共享鎖和意向排他鎖缝裁。

1）意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖扫皱，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。

2）意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖捷绑，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖韩脑。

? InnoDB行鎖模式兼容性列表

請求鎖模式

?? 是否兼容

當前鎖模式

XIXSIS

X沖突沖突沖突沖突

IX沖突兼容沖突兼容

S沖突沖突兼容兼容

IS沖突兼容兼容兼容

如果一個事務(wù)請求的鎖模式與當前的鎖兼容，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àn)的滤奈，這一點MySQL與oracle不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的撩满。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)蜒程，InnoDB才使用行級鎖，否則伺帘，InnoDB將使用表鎖昭躺！

在實際應(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的值比較高帝洪，則說明存在著較嚴重的表級鎖爭用情況似舵。

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寫鎖實驗：

對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求碟狞，但會阻塞對同一表的寫請求啄枕；對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作族沃；MyISAM表的讀操作與寫操作之間频祝，以及寫操作之間是串行的！根據(jù)如表20-2所示的例子可以知道脆淹，當一個線程獲得對一個表的寫鎖后常空，只有持有鎖的線程可以對表進行更新操作。其他線程的讀盖溺、寫操作都會等待漓糙，直到鎖被釋放為止。

USER1：

mysql> lock table film_text write;

當前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存儲引擎的共享鎖實驗

USER1：

mysql> set autocommit = 0;

USER2：

mysql> set autocommit = 0;

USER1：

當前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：

當前session對鎖定的記錄進行更新操作醉鳖，等待鎖：

mysql> update actor set last_name = 'MONROE T' where actor_id = 178;

等待

USER2：

其他session也對該記錄進行更新操作，則會導(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：

當前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：

當前session可以對鎖定的記錄進行更新操作北发，更新后釋放鎖：

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)化的幾個重要參數(shù)

bulk_insert_buffer_size

批量插入緩存大小,這個參數(shù)是針對MyISAM存儲引擎來說的.適用于在一次性插入100-1000+條記錄時,提高效率.默認值是8M.可以針對數(shù)據(jù)量的大小,翻倍增加.

concurrent_insert

并發(fā)插入,當表沒有空洞(刪除過記錄),在某進程獲取讀鎖的情況下,其他進程可以在表尾部進行插入.

值可以設(shè)0不允許并發(fā)插入, 1當表沒有空洞時,執(zhí)行并發(fā)插入, 2不管是否有空洞都執(zhí)行并發(fā)插入.

默認是1針對表的刪除頻率來設(shè)置.

delay_key_write

針對MyISAM存儲引擎,延遲更新索引.意思是說,update記錄時,先將數(shù)據(jù)up到磁盤,但不up索引,將索引存在內(nèi)存里,當表關(guān)閉時,將內(nèi)存索引,寫到磁盤.值為0不開啟, 1開啟.默認開啟.

delayed_insert_limit, delayed_insert_timeout, delayed_queue_size

延遲插入,將數(shù)據(jù)先交給內(nèi)存隊列,然后慢慢地插入.但是這些配置,不是所有的存儲引擎都支持,目前來看,常用的InnoDB不支持, MyISAM支持.根據(jù)實際情況調(diào)大,一般默認夠用了

?,?? ??)