如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
MySQL锁概述
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
比如
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）。
InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。下面几节我们重点介绍MySQL表锁和 InnoDB行锁的问题。
MyISAM表锁
MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎（实际 InnoDB是单独的一个公司，现在已经被Oracle公司收购）。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。
查询表级锁争用情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：
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的值比较高，则说明存在着较严重的表级锁争用情况。
MySQL表级锁的锁模式
MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作与写操作之间，以及写操作之间是串行的；
当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
获得表film_text的WRITE锁定
mysql> lock table film_text write;
Query OK, 0 rows affected (0.00 sec)
当前session对锁定表的查询、更新、插入操作都可以执行：
mysql> select film_id,title from film_text where film_id = 1001;
| film_id | title |
| 1001 | Update Test |
1 row in set (0.00 sec)
mysql> insert into film_text (film_id,title) values(1003,'Test');
Query OK, 1 row affected (0.00 sec)
mysql> update film_text set title = 'Test' where film_id = 1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
其他session对锁定表的查询被阻塞，需要等待锁被释放：
mysql> select film_id,title from film_text where film_id = 1001;
等待
释放锁：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
等待
Session2获得锁，查询返回：
mysql> select film_id,title from film_text where film_id = 1001;
| film_id | title |
| 1001 | Test |
1 row in set (57.59 sec)
如何加錶鎖定？
MyISAM在執行查詢語句（SELECT）前，會自動將所有涉及的資料表加讀鎖定，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動為涉及的表格加寫鎖，這個過程並不需要使用者乾預，因此，使用者一般不需要直接用LOCK TABLE指令來為MyISAM表明確加鎖。在範例中，明確加鎖基本上都是為了方便而已，並非必須如此。
給MyISAM表顯示加鎖，一般是為了在一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。例如，有一個訂單表orders，記錄有各訂單的總金額total，同時還有一個訂單明細表order_detail，其中記錄有各訂單每一產品的金額小計subtotal，假設我們需要檢查這兩個表的金額合計是否相符，可能就需要執行以下兩個SQL：
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
#此時，如果不先給兩個表加鎖，就可能產生錯誤的結果，因為第一個語句執行過程中，order_detail表可能已經發生了改變。因此，正確的方法應該是：
Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
##Select sum(subtotal) from order_detail;Unlock tables;要特別說明以下兩點內容。 上面的例子在LOCK TABLES時加了「local」選項，其作用就是在滿足MyISAM表並發插入條件的情況下，允許其他用戶在表尾並發插入記錄，有關MyISAM表的並發插入問題，後面還會進一步介紹。 在用LOCK TABLES為表明確加表鎖時，必須同時取得所有涉及到表的鎖，且MySQL不支援鎖升級。也就是說，在執行LOCK TABLES後，只能存取明確加鎖的這些表，不能存取未加鎖的表；同時，如果加的是讀鎖，那麼只能執行查詢操作，而不能執行更新操作。其實，在自動加鎖的情況下也基本上是如此，MyISAM總是一次獲得SQL語句所需的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因。 一個session使用LOCK TABLE指令為表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或存取其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現鎖定等待。 當使用LOCK TABLES時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現多少次，就要透過與SQL語句中相同的別名鎖定多少次，否則也會出錯！ 舉例說明如下。 （1）對actor表獲得讀鎖：mysql> lock table actor read;Query OK, 0 rows affected (0.00 sec)（2）但是透過別名存取會提示錯誤：mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name b.last_name;ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES （3）需要分別鎖定別名：mysql> lock table actor as a read,actor as b read;Query OK, 0 rows affected (0.00 sec )（4）依照別名的查詢可以正確執行：mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a .first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name b.last_name;1 row in set (0.00 sec)
並發插入（Concurrent Inserts）
上文提到MyISAM表的讀和寫是串行的，但這是就總體而言的。在一定條件下，MyISAM表也支援查詢和插入操作的並發進行。
MyISAM儲存引擎有一個系統變數concurrent_insert，專門用於控制其並發插入的行為，其值分別可以為0、1或2。
當concurrent_insert設定為0時，不允許並發插入。
當concurrent_insert設定為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個程序從表尾插入記錄。這也是MySQL的預設值。
當concurrent_insert設定為2時，無論MyISAM表中有沒有空洞，都允許在表尾並發插入記錄。
可以利用MyISAM儲存引擎的並發插入特性，來解決應用程式中對相同資料表查詢和插入的鎖定爭用。例如，將concurrent_insert系統變數設為2，總是允許並發插入；同時，透過定期在系統空閒時段執行 OPTIMIZE TABLE語句來整理空間碎片，收回因刪除記錄而產生的中間空洞。有關OPTIMIZE TABLE語句的詳細介紹，可以參考第18章中「兩個簡單實用的最佳化方法」一節的內容。
MyISAM的鎖定調度
前面講過，MyISAM儲存引擎的讀鎖和寫入鎖定是互斥的，讀寫作業是串列的。那麼，一個進程請求某個 MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL該如何處理呢？答案是寫進程先獲得鎖。不僅如此，即使讀取請求先到鎖等待佇列，寫入請求後到，寫鎖也會插到讀鎖請求之前！這是因為MySQL認為寫入請求一般比讀取請求重要。這也正是MyISAM表不太適合於有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以透過一些設定來調節MyISAM 的調度行為。
透過指定啟動參數low-priority-updates，使MyISAM引擎預設給予讀取請求以優先的權利。
透過執行指令SET LOW_PRIORITY_UPDATES=1，使該連線發出的更新請求優先權降低。
透過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先權。
雖然上面3種方法都是要更新優先，或是查詢優先的方法，但還是可以用其來解決查詢相對重要的應用程式（如使用者登入系統）中，讀鎖等待嚴重的問題。
另外，MySQL也提供了一種折衷的方法來調節讀寫衝突，即給系統參數max_write_lock_count設定一個合適的值，當一個表的讀鎖達到這個值後，MySQL就暫時將寫入請求的優先權降低，給讀進程一定獲得鎖的機會。
上面已經討論了寫優先調度機制所帶來的問題和解決方法。這裡還要強調一點：一些需要長時間運行的查詢操作，也會讓寫入進程「餓死」！因此，應用程式應盡量避免長時間運行的查詢操作，不要總是想用一條SELECT語句來解決問題，因為這種看似巧妙的SQL語句，往往比較複雜，執行時間較長，在可能的情況下可以透過使用中間表等措施對SQL語句做一定的“分解”，使每一步查詢都能在較短時間完成，從而減少鎖定衝突。如果複雜查詢不可避免，應盡量安排在資料庫空閒時段執行，例如一些定期統計可以安排在夜間執行。
InnoDB鎖定
InnoDB與MyISAM的最大差異有兩點：一是支援事務（TRANSACTION）；二是採用了行級鎖定。行級鎖與表級鎖本來就有許多不同之處，另外，事務的引進也帶來了一些新問題。下面我們先介紹一點背景知識，然後再詳細討論InnoDB的鎖定問題。
1.事務（Transaction）及其ACID屬性
事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。
（Atomicity）原子性：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。
（Consistent）一致性：在交易開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性；當事務結束時，所有的內部資料結構（如B樹索引或雙向鍊錶）也都必須是正確的。
（Isolation）隔離性：資料庫系統提供一定的隔離機制，確保交易在不受外部並發操作影響的「獨立」環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
（Durable）持久性：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。
銀行轉帳就是事務的典型例子。
2.並發事務處理所帶來的問題
相對於串列處理來說，並發事務處理能大幅增加資料庫資源的使用率，提高資料庫系統的事務吞吐量，從而可以支援更多的使用者。但並發事務處理也會帶來一些問題，主要包括以下幾種情況。
更新遺失（Lost Update）：當兩個或多個交易選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生遺失更新問題－最後的更新覆蓋了其他事務所所做的更新。例如，兩位編輯人員製作了同一文件的電子副本。每個編輯人員獨立地更改其副本，然後保存更改後的副本，這樣就覆蓋了原始文件。最後儲存其更改副本的編輯人員會覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員無法存取相同文件，則可避免此問題。
髒讀（Dirty Reads）：一個事務正在對一筆記錄做修改，在這個事務完成並提交前，這條記錄的資料就處於不一致狀態；這時，另一個事務也來讀取同一筆記錄，如果不加控制，第二個事務讀取了這些「髒」數據，並據此做進一步的處理，就會產生未提交的數據依賴關係。這種現像被形像地叫做"髒讀"。
不可重複讀（Non-Repeatable Reads）：一個事務在讀取某些數據後的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做「不可重複讀」。
幻讀（Phantom Reads）：一個事務以相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為“幻讀」。
3.事務隔離等級
在上面講到的並發事務處理所帶來的問題中，「更新遺失」通常是應該完全避免的。但防止更新遺失，並不能單靠資料庫事務控制器來解決，需要應用程式對要更新的資料加必要的鎖定來解決，因此，防止更新遺失應該是應用程式的責任。
“髒讀”、“不可重複讀”和“幻讀”，其實都是資料庫讀取一致性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本上可分為以下兩種。
一種是在讀取資料前，對其加鎖，阻止其他交易對資料進行修改。
另一種是不用加任何鎖，透過一定機制產生一個資料請求時間點的一致性資料快照（Snapshot)，並用這個快照來提供某一層級（語句級或交易級）的一致性讀取。從使用者的角度來看，好像是資料庫可以提供相同資料的多個版本，因此，這種技術叫做資料多版本並發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也常稱為多版本資料庫。
一致性讀，又稱為快照讀。使用的是MVCC機制讀取undo中的已經提交的資料。所以它的讀取是非阻塞的。
一致性讀取肯定是讀取在某個時間點已經提交了的數據，有個特例：本事務中修改的數據，即使未提交的數據也可以在本事務的後面部分讀取到。一致性讀是指普通的select語句，不含 for update, in share mode 等等子句。使用的是undo中的提交的數據，不需要使用鎖(MDL除外)。而當前讀，是指update, delete, select for update, select in share mode等等語句進行的讀，它們讀取的是資料庫中的最新的數據，並且會鎖住讀取的行和gap(RR隔離時)。如果不能獲得鎖，則會一直等待，直到獲得或逾時。
資料庫的事務隔離越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上「串行化」進行，這顯然與「並發」是矛盾的。同時，不同的應用程式對讀取一致性和事務隔離程度的要求也是不同的，例如許多應用程式對「不可重複讀取」和「幻讀」並不敏感，可能更關心資料並發存取的能力。
為了解決「隔離」與「並發」的矛盾，ISO/ANSI SQL92定義了4個事務隔離級別，每個級別的隔離程度不同，允許出現的副作用也不同，應用可以根據自己的業務邏輯要求，透過選擇不同的隔離等級來平衡「隔離」與「並發」的矛盾。表20-5很好地概括了這4個隔離等級的特性。
各具體資料庫不一定完全實現了上述4個隔離級別Oracle只提供Read committed和Serializable兩個標準隔離級別，另外還提供自己定義的Read only隔離級別；SQL Server除支援上述ISO/ANSI SQL92定義的4個隔離級別外，還支援一個稱為「快照」的隔離級別，但嚴格來說它是一個用MVCC實現的Serializable隔離級別。
MySQL 支援全部4個隔離級別，但在具體實現時，有一些特點，例如在一些隔離級別下是採用MVCC一致性讀，但某些情況下又不是，這些內容在後面的章節中將會進一步介紹。
4.取得InnoDB行鎖定爭用情況
可以透過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖定的爭奪情況。
mysql> show status like 'innodb_row_lock%';
| Variable_name #
#| InnoDB_row_lock_lock_current_waits | 0 |
| InnoDB_row_lock_time | 000_row_lock_lock_no_ | |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
