一、MySQL鎖概述 資料庫鎖機制簡單來說，就是資料庫為了保證數據併發訪問的一致性、有效性，使得數據被併發訪問變得有序所設計的一種規則。 由於MySQL有不同的存儲引擎，而不同的存儲引擎又採用不同的鎖機制。比如：MyISAM存儲引擎採用的是表級鎖（table-level locking）；InnoD ...
一、MySQL鎖概述
資料庫鎖機制簡單來說，就是資料庫為了保證數據併發訪問的一致性、有效性，使得數據被併發訪問變得有序所設計的一種規則。
由於MySQL有不同的存儲引擎，而不同的存儲引擎又採用不同的鎖機制。比如：MyISAM存儲引擎採用的是表級鎖（table-level locking）；InnoDB存儲引擎既支持表級鎖，又支持行級鎖（row-level locking），預設情況下採用行級鎖；BDB存儲引擎採用的是頁面鎖（page-level locking），但也支持表級鎖。
下麵來介紹下這三種鎖：
1、表級鎖（table-level locking）
表級鎖是MySQL中最大粒度的鎖定機制。特點是：開銷最小、獲取和釋放鎖的速度最快。由於鎖定的是整張表，所以不會出現死鎖。缺點是，發生資源爭用的概率最高，併發量度大大降低。
2、行級鎖（row-level locking）
行級鎖是MySQL中最小粒度的鎖定機制。特點是：發生資源爭用的概率最低，併發度高。但是由於粒度最小，所以獲取和釋放鎖的速度也慢，開銷更大，而且容易出現死鎖。
3、頁級鎖（page-level locking）
頁級鎖是MySQL中比較特殊的一種鎖定機制，在其他資料庫中不常見。它的鎖定粒度在行級鎖和表級鎖之間，所以帶來的開銷和併發處理能力也在兩者之間，而且也容易發生死鎖。
應用場景：表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如web應用；而行級鎖更適應於有大量按索引條件併發更新少量不同數據，同時又有併發查詢的應用，如一些線上事務處理（OLTP）系統。
二、表級鎖
由於MyISAM存儲引擎使用的鎖定機制完全是由MySQL提供的表級鎖定實現，所以下麵我們將以MyISAM存儲引擎作為示例存儲引擎。
1、MySQL表級鎖的鎖模式
MySQL的表級鎖有兩種模式：表共用讀鎖（Table Read Lock） 和表獨占寫鎖（Table Write Lock）。鎖模式的相容性：
對MyISAM表的讀操作，不會阻塞其他用戶對錶的讀請求，但是會阻塞對同一表的寫請求。
對MyISAM表的寫操作，則會阻塞其他用戶對錶的讀和寫請求。
MyISAM表的讀操作與寫操作之間，以及寫操作之間是串列的。當一個線程獲得對一個表的寫操作後，只有持有鎖的線程可以對錶進行寫操作。其他線程的讀、寫都會等待，直到鎖被釋放為止。
1.1 MyISAM存儲引擎的寫阻塞讀例子
| 會話1 | 會話2 |
|
/*獲得表auth_type的WRITE鎖定*/ lock table auth_type write; |
|
|
/*當前回話對鎖定的表可以進行增刪改查的操作*/ INSERT INTO `auth_type` (`id`, `type_code`, `type_name`) VALUES (1, '001', '一級用戶'); 受影響的行: 0； id type_code type_name
受影響的行: 0； 受影響的行: 0； 受影響的行: 0； 受影響的行: 0； |
|
|
/*其他會話對鎖定表的查詢被阻塞，需要等待鎖被釋放*/ select * from auth_type where id = 1; 等待 |
|
|
/*釋放鎖*/ unlock tables; |
等待 |
|
/*會話2獲得鎖，查詢返回結果*/ id type_code type_name |
1.2 如何加表鎖
MyISAM在執行SELECT語句前，會自動給涉及的所有表加讀鎖，在執行更新語句（INSERT\UPDATE\DELETE等）前，會自動給涉及的表加寫鎖，並不需要向上面的例子一樣人為的用LOCK TABLE 命令顯示的加鎖。這裡只是為了演示而已。
1.2.1 在Lock tables read 時有一個“local”選項，如：lock table auth_type read local 其作用就是滿足MyISAM表併發插入條件的情況下，允許其他用戶在表尾併發插入記錄。
1.2.2 在用Lock tables給表顯示加鎖時，必須同時取得所有涉及表的鎖，如果同一個表在SQL語句中出現多次，就要通過SQL語句中不同的別名鎖定多次，否則也會出錯。
如：lock tables auth_type as a read,auth_type as b read;
1.3 併發插入
MyISAM表的讀和寫是串列的，但這是就總體而言。在一定條件下，MyISAM表也支持查詢和插入操作的併發進行。
MyISAM存儲引擎有一個系統變數concurrent_insert，專門用以控制其併發插入的行為，其值分別可以為0、1或2。
concurrent_insert = 0時，不允許併發插入。
concurrent_insert = 1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表時，另一個進程從表尾插入記錄。但是更新會等待。
concurrent_insert = 2時，無論表有沒有空洞，都允許在表尾併發插入記錄。但是更新會等待。
可以利用MyISAM存儲引擎的併發插入特性，來解決應用中對同一表查詢和插入的鎖爭用問題。
1.4 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鎖問題 1、InnoDB的行鎖模式及加鎖方法 InnoDB實現了以下兩種類型的行鎖： 共用鎖（S）：鎖粒度是行或者元組（多行）。一個事務獲取了共用鎖後，可以對鎖定範圍內的數據執行讀操作。如果事務1獲得一個元組的共用鎖，事務2還可以立即獲得這個元組的共用鎖，但是不能立即獲取這個元組的排他鎖，必須等到事務1釋放共用鎖之後。 排他鎖（X）：鎖粒度和共用鎖相同。一個事務獲取了排他鎖之後，可以對鎖定範圍內的數據執行寫操作。如果事務1獲得一個元組的排他鎖，事務2不能立即獲得這個元組的共用鎖，也不能立即獲取這個元組的排他鎖，必須等到事務1釋放排他鎖之後。 意向鎖：一種表鎖，鎖定的粒度是整張表，分為意向共用鎖（IS）和意向排他鎖（IX）兩類。意向共用鎖表示一個事務有意對數據上共用鎖或者排他鎖。“有意”這兩個字的意思就是，事務想乾這個事但是還沒乾。舉例說明下意向共用鎖，比如一個事務t執行了這樣一個語句：select * from table lock in share model ，如果這個語句執行成功，就對錶table上了一個意向共用鎖。lock in share model就是說事務t1在接下來要執行的語句中要獲取S鎖。如果t1的select * from table lock in share model執行成功，那麼接下來t1應該可以暢通無阻的去執行只需要共用鎖的語句了。意向排它鎖的含義同理可知，上例中要獲取意向排它鎖，可以使用select * from table for update 。2、鎖的互斥與相容關係 鎖和鎖之間的關係，要麼相容，要麼互斥。 鎖a和鎖b相容是指：操作相同一組數據時，如果事務1獲取了鎖a，另一個事務2還可以獲取鎖b。 鎖a和鎖b互斥是指：操作相同一組數據時，如果事務1獲取了鎖a，另一個事務2在事務1釋放鎖a之前無法獲取鎖b。
| X | S | IX | IS | |
| X | n | n | n | n |
| S | n | y | n | y |
| IX | n | n | y | y |
| IS | n | y | y | y |
解析：
事務1獲取了X鎖，事務2無法立即獲取X鎖、S鎖、IX鎖和IS鎖。
事務1獲取了S鎖，事務2無法立即獲取X鎖和IX鎖，可以立即獲取S鎖和IS鎖。
事務1獲取IX鎖，事務2無法立即獲取X鎖和S鎖，可以立即獲取IX鎖和IS鎖。
事務1獲取IS鎖，事務2無法立即獲取X鎖，可以立即獲取S鎖、IX鎖和IS鎖。
意向鎖是InnoDB自動加的，不需要用戶干預。對於UPDATE\DELETE\INSERT語句，InnoDB會自動給涉及數據集加排他鎖（X）；對於普通SELECT語句，InnoDB不會加任何鎖；事務可以通過以下語句給記錄集加共用鎖或者排他鎖。
S鎖： SELECT * FROM auth_type WHERE ... LOCK IN SHARE MODE
X鎖： SELECT * FROM auth_type WHERE ... FOR UPDATE
用SELECT ... IN SHARE MODE獲得共用鎖，主要用在需要數據依存關係時來確認某行記錄是否存在，並確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對於鎖定行記錄後需要進行更新操作的應用，應該使用SELECT... FOR UPDATE方式獲得排他鎖。
3、InnoDB行鎖實現方式
InnoDB行鎖是通過給索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是通過在數據塊中對相應數據行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
（1）在不通過索引條件查詢的時候，InnoDB確實使用的是表鎖，而不是行鎖。
沒有設置索引的情況
| 會話1 | 會話2 |
| set autocommit=0; Query OK, 0 rows affected (0.00 sec) select * from auth_type where id = 1 ; 1 row in set (0.00 sec) | set autocommit=0; Query OK, 0 rows affected (0.00 sec) select * from auth_type where id = 2 ; 1 row in set (0.00 sec) |
| select * from auth_type where id = 1 for update; 1 row in set (0.00 sec) | |
| select * from auth_type where id = 2 for update; 等待 |
有設置索引的情況
| 會話1 | 會話2 |
| set autocommit=0; Query OK, 0 rows affected (0.00 sec) select * from auth_type where id = 1 ; 1 row in set (0.00 sec) | set autocommit=0; Query OK, 0 rows affected (0.00 sec) select * from auth_type where id = 2 ; 1 row in set (0.00 sec) |
| select * from auth_type where id = 1 for update; 1 row in set (0.00 sec) | |
| select * from auth_type where id = 2 for update; 1 row in set (0.00 sec) |
（2）由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的。應用設計的時候要註意這一點。
| 會話1 | 會話2 |
| set autocommit=0; Query OK, 0 rows affected (0.00 sec) select * from auth_type where id = 1 ; 1 row in set (0.00 sec) | set autocommit=0; Query OK, 0 rows affected (0.00 sec) select * from auth_type where id = 2 ; 1 row in set (0.00 sec) |
| select * from auth_type where id = 1 for update; 1 row in set (0.00 sec) | |
|
select * from auth_type where id = 1 AND auth_type = '001' for update;
等待 |
（3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數據加鎖。
對name也加了索引
| 會話1 | 會話2 |
| set autocommit=0; Query OK, 0 rows affected (0.00 sec) select * from auth_type where id = 1 ; 1 row in set (0.00 sec) | set autocommit=0; Query OK, 0 rows affected (0.00 sec) select * from auth_type where id = 2 ; 1 row in set (0.00 sec) |
| select * from auth_type where id = 1 for update; 1 row in set (0.00 sec) | |
|
select * from auth_type where id = 2 for update; 1 row in set (0.00 sec) |
|
| select * from auth_type where auth_type = '001' for update; 等待(該記錄被會話1鎖定，所以等待獲得鎖) |
（4）即便在條件中使用了索引欄位，但是否使用索引來檢索數據是由MySQL通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。
四 間隙鎖（Next-key lock）
當我們用範圍條件而不是相等條件檢索數據，並請求共用或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。
舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,...,100,101，下麵的SQL：Select * from emp where empid > 100 for update;是一個範圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大於101（這些記錄並不存在）的“間隙”加鎖。
InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關隔離級別的要求，對於上面的例子，要是不使用間隙鎖，如果其他事務插入了empid大於100的任何記錄，那麼本事務如果再次執行上述語句，就會發生幻讀；另外一方面，是為了滿足其恢復和複製的需要。
很顯然，在使用範圍條件檢索並鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件範圍內鍵值的併發插入，這往往會造成嚴重的鎖等待。因此，在實際應用開發中，尤其是併發插入比較多的應用，我們要儘量優化業務邏輯，儘量使用相等條件來訪問更新數據，避免使用範圍條件。
還要特別說明的是，InnoDB除了通過範圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！| 會話1 | 會話2 |
| mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) | mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) |
| 當前session對不存在的記錄加for update的鎖： mysql> select * from emp where empid = 102 for update; Empty set (0.00 sec) | |
| 這時，如果其他session插入empid為102的記錄（註意：這條記錄並不存在），也會出現鎖等待： mysql>insert into emp(empid,...) values(102,...); 阻塞等待 | |
| Session_1 執行rollback： mysql> rollback; Query OK, 0 rows affected (13.04 sec) | |
| 由於其他session_1回退後釋放了Next-Key鎖，當前session可以獲得鎖併成功插入記錄： mysql>insert into emp(empid,...) values(102,...); Query OK, 1 row affected (13.35 sec) |
|
會話1 |
會話2 |
| mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) | mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) |
| mysql> insert into target_tab select d1,name from source_tab where name = '1'; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 | |
| mysql> update source_tab set name = '1' where name = '8'; 等待 | |
| commit; | |
| 返回結果 commit; |
在上面的例子中，只是簡單地讀 source_tab表的數據，相當於執行一個普通的SELECT語句，用一致性讀就可以了。RACLE正是這麼做的，它通過MVCC技術實現的多版本數據來實現一致性讀，不需要給source_tab加任何鎖。我們知道InnoDB也實現了多版本數據，對普通的SELECT一致性讀，也不需要加任何鎖；但這裡InnoDB卻給source_tab加了共用鎖，並沒有使用多版本數據一致性讀技術！
MySQL為什麼要這麼做呢？其原因還是為了保證恢復和複製的正確性。因為不加鎖的話，如果在上述語句執行過程中，其他事務對source_tab做了更新操作，就可能導致數據恢復的結果錯誤。為了演示這一點，我們再重覆一下前面的例子，不同的是在session_1執行事務前，先將系統變數 innodb_locks_unsafe_for_binlog的值設置為“on”（其預設值為off），具體結果如下表所示。| 會話1 | 會話2 |
| mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql>set innodb_locks_unsafe_for_binlog='on' Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) | mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 1 | 1 | | 5 | 1 | 1 | | 6 | 1 | 1 | | 7 | 1 | 1 | | 8 | 1 | 1 | +----+------+----+ 5 rows in set (0.00 sec) |
| mysql> insert into target_tab select d1,name from source_tab where name = '1'; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 | |
| session_1未提交，可以對session_1的select的記錄進行更新操作。 mysql> update source_tab set name = '8' where name = '1'; Query OK, 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from source_tab where name = '8'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) | |
| 更新操作先提交 mysql> commit; Query OK, 0 rows affected (0.05 sec) | |
| 插入操作後提交 mysql> commit; Query OK, 0 rows affected (0.07 sec) | |
| 此時查看數據，target_tab中可以插入source_tab更新前的結果，這符合應用邏輯： mysql> select * from source_tab where name = '8'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql> select * from target_tab; +------+------+ | id | name | +------+------+ | 4 | 1.00 | | 5 | 1.00 | | 6 | 1.00 | | 7 | 1.00 | | 8 | 1.00 | +------+------+ 5 rows in set (0.00 sec) | mysql> select * from tt1 where name = '1'; Empty set (0.00 sec) mysql> select * from source_tab where name = '8'; +----+------+----+ | d1 | name | d2 | +----+------+----+ | 4 | 8 | 1 | | 5 | 8 | 1 | | 6 | 8 | 1 | | 7 | 8 | 1 | | 8 | 8 | 1 | +----+------+----+ 5 rows in set (0.00 sec) mysql> select * from target_tab; +------+------+ | id | name | +------+------+ | 4 | 1.00 | | 5 | 1.00 | | 6 | 1.00 | | 7 | 1.00 | | 8 | 1.00 | +------+------+ 5 rows in set (0.00 sec) |
從上可見，設置系統變數innodb_locks_unsafe_for_binlog的值為“on”後，InnoDB不再對source_tab加鎖，結果也符合應用邏輯，但是如果分析BINLOG的內容：
...... SET TIMESTAMP=1169175130; BEGIN; # at 274 #070119 10:51:57 server id 1 end_log_pos 105 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1169175117; update source_tab set name = '8' where name = '1'; # at 379 #070119 10:52:10 server id 1 end_log_pos 406 Xid = 5 COMMIT; # at 406 #070119 10:52:14 server id 1 end_log_pos 474 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1169175134; BEGIN; # at 474 #070119 10:51:29 server id 1 end_log_pos 119 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1169175089; insert into target_tab select d1,name from source_tab where name = '1'; # at 593 #070119 10:52:14 server id 1 end_log_pos 620 Xid = 7 COMMIT; ...... 可以發現，在BINLOG中，更新操作的位置在INSERT...SELECT之前，如果使用這個BINLOG進行資料庫恢復，恢復的結果與實際的應用邏輯不符；如果進行複製，就會導致主從資料庫不一致！ 通過上面的例子，我們就不難理解為什麼MySQL在處理“Insert into target_tab select * from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...”時要給source_tab加鎖，而不是使用對併發影響最小的多版本數據來實現一致性讀。還要特別說明的是，如果上述語句的SELECT是範圍條件，InnoDB還會給源表加間隙鎖（Next-Lock）。 因此，INSERT...SELECT...和 CREATE TABLE...SELECT...語句，可能會阻止對源表的併發更新，造成對源表鎖的等待。如果查詢比較複雜的話，會造成嚴重的性能問題，我們在應用中應儘量避免使用。實際上，MySQL將這種SQL叫作不確定（non-deterministic）的SQL，不推薦使用。 如果應用中一定要用這種SQL來實現業務邏輯，又不希望對源表的併發更新產生影響，可以採取以下兩種措施： 一是採取上面示例中的做法，將innodb_locks_unsafe_for_binlog的值設置為“on”，強制MySQL使用多版本數據一致性讀。但付出的代價是可能無法用binlog正確地恢復或複製數據，因此，不推薦使用這種方式。 二是通過使用“select * from source_tab ... Into outfile”和“load data infile ...”語句組合來間接實現，採用這種方式MySQL不會給source_tab加鎖。
六、InnoDB在不同隔離級別下的一致性讀及鎖的差異
Read Uncommitted（讀取未提交內容）
在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用於實際應用，因為它的性能也不比其他級別好多少。讀取未提交的數據，也被稱之為臟讀（Dirty Read）。
Read Committed（讀取提交內容）
這是大多數資料庫系統的預設隔離級別（但不是MySQL預設的）。它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。這種隔離級別
也支持所謂的不可重覆讀（Nonrepeatable
Read），因為同一事務的其他實例在該實例處理其間可能會有新的commit，所以同一select可能返回不同結果。
Repeatable Read（可重讀）
這是MySQL的預設事務隔離級別，它確保同一事務的多個實例在併發讀取數據時，會看到同樣的數據行。不過理論上，這會導致另一個棘手的問題：幻讀 （Phantom Read）。簡單的說，幻讀指當用戶讀取某一範圍的數據行時，另一個事務又在該範圍內插入了新行，當用戶再讀取該範圍的數據行時，會發現有新的“幻影” 行。InnoDB和Falcon存儲引擎通過多版本併發控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。
Serializable（可串列化）
這是最高的隔離級別，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。簡言之，它是在每個讀的數據行上加上共用鎖。在這個級別，可能導致大量的超時現象和鎖競爭。
這四種隔離級別採取不同的鎖類型來實現，若讀取的是同一個數據的話，就容易發生問題。例如：
臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由於某些原因，前一個RollBack了操作，則後一個事務所讀取的數據就會是不正確的。
不可重覆讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的數據。
幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列(Row)數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。
InnoDB存儲引擎中不同SQL在不同隔離級別下鎖比較| 隔離級別 一致性讀和鎖 SQL | Read Uncommited | Read Commited | Repeatable Read | Serializable | |
| SQL | 條件 | ||||
| select | 相等 | None locks | Consisten read/None lock | Consisten read/None lock | Share locks |
| 範圍 | None locks | Consisten read/None lock | Consisten read/None lock | Share Next-Key | |
| update | 相等 | exclusive locks | exclusive locks | exclusive locks | Exclusive locks |
| 範圍 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
| Insert | N/A | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| replace | 無鍵衝突 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
| 鍵衝突 | exclusive next-key |
  更多相關文章
一周排行
 所有分類
| |||