———————————?MySQL?—————————————————–

MySQL中select * for update鎖表的問題

頁級:引擎 BDB。?

表級:引擎 MyISAM ， 理解為鎖住整個表，可以同時讀，寫不行?

行級:引擎 INNODB ， 單獨(dú)的一行記錄加鎖?

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

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

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

MySQL 5.1支持對MyISAM和MEMORY表進(jìn)行表級鎖定，對BDB表進(jìn)行頁級鎖定，對InnoDB表進(jìn)行行級鎖定。?

對WRITE，MySQL使用的表鎖定方法原理如下：?

如果在表上沒有鎖，在它上面放一個寫鎖。?

否則，把鎖定請求放在寫鎖定隊列中。?

對READ，MySQL使用的鎖定方法原理如下：?

如果在表上沒有寫鎖定，把一個讀鎖定放在它上面?

否則，把鎖請求放在讀鎖定隊列中。?

InnoDB使用行鎖定，BDB使用頁鎖定。對于這兩種存儲引擎，都可能存在死鎖。這是因為，在SQL語句處理期間，InnoDB自動獲得行鎖定和BDB獲得頁鎖定，而不是在事務(wù)啟動時獲得。

MySQL中select * for update鎖表的問題?

由于InnoDB預(yù)設(shè)是Row-Level Lock，所以只有「明確」的指定主鍵，MySQL才會執(zhí)行Row lock (只鎖住被選取的資料例) ，否則MySQL將會執(zhí)行Table Lock (將整個資料表單給鎖住)。?

舉個例子:?

假設(shè)有個表單products ，里面有id跟name二個欄位，id是主鍵。?

例1: (明確指定主鍵，并且有此筆資料，row lock)?

SELECT * FROM products WHERE id=’3’ FOR UPDATE;?

SELECT * FROM products WHERE id=’3’ and type=1 FOR UPDATE;

例2: (明確指定主鍵，若查無此筆資料，無lock)?

SELECT * FROM products WHERE id=’-1’ FOR UPDATE;

例2: (無主鍵，table lock)?

SELECT * FROM products WHERE name=’Mouse’ FOR UPDATE;

例3: (主鍵不明確，table lock)?

SELECT * FROM products WHERE id<>’3’ FOR UPDATE;

例4: (主鍵不明確，table lock)?

SELECT * FROM products WHERE id LIKE ‘3’ FOR UPDATE;

注1: FOR UPDATE僅適用于InnoDB，且必須在交易區(qū)塊(BEGIN/COMMIT)中才能生效。?

注2: 要測試鎖定的狀況，可以利用MySQL的Command Mode ，開二個視窗來做測試。

在MySql 5.0中測試確實是這樣的?

另外：MyAsim 只支持表級鎖，InnerDB支持行級鎖?

添加了(行級鎖/表級鎖)鎖的數(shù)據(jù)不能被其它事務(wù)再鎖定，也不被其它事務(wù)修改（修改、刪除）?

是表級鎖時，不管是否查詢到記錄，都會鎖定表。

———————————?Oracle?—————————————————–?

Oracle 的for update行鎖

鍵字: oracle 的for update行鎖?

SELECT…FOR UPDATE 語句的語法如下：?

SELECT … FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];?

其中：?

OF 子句用于指定即將更新的列，即鎖定行上的特定列。?

WAIT 子句指定等待其他用戶釋放鎖的秒數(shù)，防止無限期的等待。?

“使用FOR UPDATE WAIT”子句的優(yōu)點(diǎn)如下：?

１防止無限期地等待被鎖定的行；?

２允許應(yīng)用程序中對鎖的等待時間進(jìn)行更多的控制。?

３對于交互式應(yīng)用程序非常有用，因為這些用戶不能等待不確定?

４ 若使用了skip locked，則可以越過鎖定的行，不會報告由wait n 引發(fā)的‘資源忙’異常報告

示例1:?

create table t(a varchar2(20),b varchar2(20));?

insert into t values(‘1’,’1’);?

insert into t values(‘2’,’2’);?

insert into t values(‘3’,’3’);?

insert into t values(‘4’,’4’);?

現(xiàn)在執(zhí)行如下操作：?

在plsql develope中打開兩個sql窗口，?

在1窗口中運(yùn)行sql?

select * from t where a=’1’ for update;?

在2窗口中運(yùn)行sql1?

1. select * from t where a=’1’; 這一點(diǎn)問題也沒有，因為行級鎖不會影響純粹的select語句?

再運(yùn)行sql2?

2. select * from t where a=’1’ for update; 則這一句sql在執(zhí)行時，永遠(yuǎn)處于等待狀態(tài)，除非窗口1中sql被提交或回滾。?

如何才能讓sql2不等待或等待指定的時間呢？ 我們再運(yùn)行sql3?

3. select * from t where a=’1’ for update nowait; 則在執(zhí)行此sql時，直接報資源忙的異常。?

若執(zhí)行 select * from t where a=’1’ for update wait 6; 則在等待6秒后，報 資源忙的異常。?

如果我們執(zhí)行sql4?

4. select * from t where a=’1’ for update nowait skip Locked; 則執(zhí)行sql時，即不等待，也不報資源忙異常。?

現(xiàn)在我們看看執(zhí)行如下操作將會發(fā)生什么呢？?

在窗口1中執(zhí)行：?

select * from t where rownum<=3 nowait skip Locked;?

在窗口2中執(zhí)行：?

select * from t where rownum<=6 nowait skip Locked;?

select for update 也就如此了吧，insert、update、delete操作默認(rèn)加行級鎖，其原理和操作與select for update并無兩樣。?

select for update of，這個of子句在牽連到多個表時，具有較大作用，如不使用of指定鎖定的表的列，則所有表的相關(guān)行均被鎖定，若在of中指定了需修改的列，則只有與這些列相關(guān)的表的行才會被鎖定。

實例2?

elect * from t for update 會等待行鎖釋放之后，返回查詢結(jié)果。?

select * from t for update nowait 不等待行鎖釋放，提示鎖沖突，不返回結(jié)果?

select * from t for update wait 5 等待5秒，若行鎖仍未釋放，則提示鎖沖突，不返回結(jié)果?

select * from t for update skip locked 查詢返回查詢結(jié)果，但忽略有行鎖的記錄。

關(guān)于Oracle中for update的補(bǔ)充說明：

分成兩類：加鎖范圍子句和加鎖行為子句

加鎖范圍子句：?

在select…for update之后，可以使用of子句選擇對select的特定數(shù)據(jù)表進(jìn)行加鎖操作。默認(rèn)情況下，不使用of子句表示在select所有的數(shù)據(jù)表中加鎖

加鎖行為子句：?

當(dāng)我們進(jìn)行for update的操作時，與普通select存在很大不同。一般select是不需要考慮數(shù)據(jù)是否被鎖定，最多根據(jù)多版本一致讀的特性讀取之前的版本。加入for update之后，Oracle就要求啟動一個新事務(wù)，嘗試對數(shù)據(jù)進(jìn)行加鎖。如果當(dāng)前已經(jīng)被加鎖，默認(rèn)的行為必然是block等待。使用nowait子句的作用就是避免進(jìn)行等待，當(dāng)發(fā)現(xiàn)請求加鎖資源被鎖定未釋放的時候，直接報錯返回。

在日常中，我們對for update的使用還是比較普遍的，特別是在如pl/sql developer中手工修改數(shù)據(jù)。此時只是覺得方便，而對for update真正的含義缺乏理解。

For update是Oracle提供的手工提高鎖級別和范圍的特例語句。Oracle的鎖機(jī)制是目前各類型數(shù)據(jù)庫鎖機(jī)制中比較優(yōu)秀的。所以，Oracle認(rèn)為一般不需要用戶和應(yīng)用直接進(jìn)行鎖的控制和提升。甚至認(rèn)為死鎖這類鎖相關(guān)問題的出現(xiàn)場景，大都與手工提升鎖有關(guān)。所以，Oracle并不推薦使用for update作為日常開發(fā)使用。而且，在平時開發(fā)和運(yùn)維中，使用了for update卻忘記提交，會引起很多鎖表故障。

那么，什么時候需要使用for update？就是那些需要業(yè)務(wù)層面數(shù)據(jù)獨(dú)占時，可以考慮使用for update。場景上，比如火車票訂票，在屏幕上顯示郵票，而真正進(jìn)行出票時，需要重新確定一下這個數(shù)據(jù)沒有被其他客戶端修改。所以，在這個確認(rèn)過程中，可以使用for update。這是統(tǒng)一的解決方案方案問題，需要前期有所準(zhǔn)備。

原文鏈接?數(shù)據(jù)庫中Select For update語句的解析 - wblearn的博客 - CSDN博客