悲觀鎖與樂觀鎖：
悲觀鎖：顧名思義，就是很悲觀，每次去拿數(shù)據(jù)的時候都認為別人會修改，所以每次在拿數(shù)據(jù)的時候都會上鎖，這樣別人想拿這個數(shù)據(jù)就會block直到它拿到鎖。傳統(tǒng)的關系型數(shù)據(jù)庫里邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。
樂觀鎖：顧名思義，就是很樂觀，每次去拿數(shù)據(jù)的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數(shù)據(jù)，可以使用版本號等機制。樂觀鎖適用于多讀的應用類型，這樣可以提高吞吐量，像數(shù)據(jù)庫如果提供類似于write_condition機制的其實都是提供的樂觀鎖。
表級：引擎?MyISAM，直接鎖定整張表，在你鎖定期間，其它進程無法對該表進行寫操作。如果你是寫鎖，則其它進程則讀也不允許
頁級：引擎?BDB，表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄
行級：引擎?INNODB，僅對指定的記錄進行加鎖，這樣其它進程還是可以對同一個表中的其它記錄進行操作。
上述三種鎖的特性可大致歸納如下：
1）?表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
2）?頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。
3）?行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
?三種鎖各有各的特點，若僅從鎖的角度來說，表級鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應用，如WEB應用；行級鎖更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應用，如一些在線事務處理（OLTP）系統(tǒng)。
?MySQL表級鎖有兩種模式：
1、表共享讀鎖（Table Read Lock）。對MyISAM表進行讀操作時，它不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫操作；
2、表獨占寫鎖（Table Write Lock）。對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作。
?MyISAM表的讀和寫是串行的，即在進行讀操作時不能進行寫操作，反之也是一樣。但在一定條件下MyISAM表也支持查詢和插入的操作的并發(fā)進行，其機制是通過控制一個系統(tǒng)變量（concurrent_insert）來進行的，當其值設置為0時，不允許并發(fā)插入；當其值設置為1時，如果MyISAM表中沒有空洞（即表中沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄；當其值設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄。
MyISAM鎖調度是如何實現(xiàn)的呢，這也是一個很關鍵的問題。例如，當一個進程請求某個MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，此時mysql將會如優(yōu)先處理進程呢？通過研究表明，寫進程將先獲得鎖（即使讀請求先到鎖等待隊列）。但這也造成一個很大的缺陷，即大量的寫操作會造成查詢操作很難獲得讀鎖，從而可能造成永遠阻塞。所幸我們可以通過一些設置來調節(jié)MyISAM的調度行為。我們可通過指定參數(shù)low-priority-updates，使MyISAM默認引擎給予讀請求以優(yōu)先的權利，設置其值為1（set low_priority_updates=1),使優(yōu)先級降低。
InnoDB鎖與MyISAM鎖的最大不同在于：
1、是支持事務（TRANCSACTION）。
2、是采用了行級鎖。
我們知道事務是由一組SQL語句組成的邏輯處理單元，其有四個屬性（簡稱ACID屬性），分別為：
原子性（Atomicity）：事務是一個原子操作單元，其對數(shù)據(jù)的修改，要么全部執(zhí)行，要么全都不執(zhí)行；
一致性（Consistent）：在事務開始和完成時，數(shù)據(jù)都必須保持一致狀態(tài)；
隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制，保證事務在不受外部并發(fā)操作影響的“獨立”環(huán)境執(zhí)行；
持久性（Durable）：事務完成之后，它對于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。
并發(fā)事務處理帶來的問題
相對于串行處理來說，并發(fā)事務處理能大大增加數(shù)據(jù)庫資源的利用率，提高數(shù)據(jù)庫系統(tǒng)的事務吞吐量，從而可以支持更多的用戶。但并發(fā)事務處理也會帶來一些問題，主要包括以下幾種情況。
1、更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發(fā)生丟失更新問題－－最后的更新覆蓋了由其他事務所做的更新。例如，兩個編輯人員制作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成并提交事務之前，另一個編輯人員不能訪問同一文件，則可避免此問題。
2、臟讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務完成并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進一步的處理，就會產生未提交的數(shù)據(jù)依賴關系。這種現(xiàn)象被形象地叫做"臟讀"。
3、不可重復讀（Non-Repeatable Reads）：一個事務在讀取某些數(shù)據(jù)后的某個時間，再次讀取以前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經發(fā)生了改變、或某些記錄已經被刪除了！這種現(xiàn)象就叫做“不可重復讀”。
4、幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀”。
事務隔離級別
在上面講到的并發(fā)事務處理帶來的問題中，“更新丟失”通常是應該完全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫事務控制器來解決，需要應用程序對要更新的數(shù)據(jù)加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
“臟讀”、“不可重復讀”和“幻讀”，其實都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務隔離機制來解決。數(shù)據(jù)庫實現(xiàn)事務隔離的方式，基本上可分為以下兩種。
1、一種是在讀取數(shù)據(jù)前，對其加鎖，阻止其他事務對數(shù)據(jù)進行修改。
2、另一種是不用加任何鎖，通過一定機制生成一個數(shù)據(jù)請求時間點的一致性數(shù)據(jù)快照（Snapshot)，并用這個快照來提供一定級別（語句級或事務級）的一致性讀取。從用戶的角度來看，好像是數(shù)據(jù)庫可以提供同一數(shù)據(jù)的多個版本，因此，這種技術叫做數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經常稱為多版本數(shù)據(jù)庫。
數(shù)據(jù)庫的事務隔離越嚴格，并發(fā)副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上?“串行化”進行，這顯然與“并發(fā)”是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對“不可重復讀”和“幻讀”并不敏感，可能更關心數(shù)據(jù)并發(fā)訪問的能力。
為了解決“隔離”與“并發(fā)”的矛盾，ISO/ANSI SQL92定義了4個事務隔離級別，每個級別的隔離程度不同，允許出現(xiàn)的副作用也不同，應用可以根據(jù)自己的業(yè)務邏輯要求，通過選擇不同的隔離級別來平衡“隔離”與“并發(fā)”的矛盾。表20-5很好地概括了這4個隔離級別的特性。
讀數(shù)據(jù)一致性及允許的并發(fā)副作用
隔離級別
讀數(shù)據(jù)一致性臟讀不可重復讀幻讀
未提交讀（Read uncommitted）最低級別，只能保證不讀取物理上損壞的數(shù)據(jù)是是是
已提交度（Read committed）語句級否是是
可重復讀（Repeatable read）事務級否否是
可序列化（Serializable）最高級別，事務級否否否
最后要說明的是：各具體數(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）：允許一個事務去讀一行，阻止其他事務獲得相同數(shù)據(jù)集的排他鎖。
????( Select * from table_name where ......lock in share mode)
2）排他鎖（X）：允許獲得排他鎖的事務更新數(shù)據(jù)，阻止其他事務取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。(select * from table_name where.....for update)
為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機制；同時還有兩種內部使用的意向鎖（都是表鎖），分別為意向共享鎖和意向排他鎖。
1）意向共享鎖（IS）：事務打算給數(shù)據(jù)行加行共享鎖，事務在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
2）意向排他鎖（IX）：事務打算給數(shù)據(jù)行加行排他鎖，事務在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。
? InnoDB行鎖模式兼容性列表
請求鎖模式
?? 是否兼容
當前鎖模式
XIXSIS
X沖突沖突沖突沖突
IX沖突兼容沖突兼容
S沖突沖突兼容兼容
IS沖突兼容兼容兼容
如果一個事務請求的鎖模式與當前的鎖兼容，InnoDB就將請求的鎖授予該事務；反之，如果兩者不兼容，該事務就要等待鎖釋放。
意向鎖是InnoDB自動加的，不需用戶干預。對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X）；對于普通SELECT語句，InnoDB不會加任何鎖；事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。
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ù)塊中對相應數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖沖突，從而影響并發(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也對該記錄進行更新操作，則會導致死鎖退出：
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ā)插入,當表沒有空洞(刪除過記錄),在某進程獲取讀鎖的情況下,其他進程可以在表尾部進行插入.
值可以設0不允許并發(fā)插入, 1當表沒有空洞時,執(zhí)行并發(fā)插入, 2不管是否有空洞都執(zhí)行并發(fā)插入.
默認是1針對表的刪除頻率來設置.
delay_key_write
針對MyISAM存儲引擎,延遲更新索引.意思是說,update記錄時,先將數(shù)據(jù)up到磁盤,但不up索引,將索引存在內存里,當表關閉時,將內存索引,寫到磁盤.值為0不開啟, 1開啟.默認開啟.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延遲插入,將數(shù)據(jù)先交給內存隊列,然后慢慢地插入.但是這些配置,不是所有的存儲引擎都支持,目前來看,常用的InnoDB不支持, MyISAM支持.根據(jù)實際情況調大,一般默認夠用了
?,?? ??)
