mysql進階(四)mysql中select

mysql中select * for update

註：

 FOR UPDATE 僅適用於InnoDB，且必須在事務區塊(BEGIN/COMMIT)中才能生效。

作用

鎖定該語句所選取的物件。防止在選擇之後別的地方修改這些物件造成資料不一致。若要確保在統計（查詢）執行過程中，記錄不會被其他使用者更新，

則可以使用For update子句進行加鎖。這樣在這個鎖定釋放前其他用戶不能對這些記錄作update、delete和加鎖。

Select   daptno   from    dept Where   deptno=25   For update；
如果你使用了FOR UPDATE來對錶鎖加鎖，則必須使用資料表的附加鎖定記錄。

鎖定分成兩類：加鎖範圍子句和加鎖行為子句 加鎖範圍子句： 在select…for update之後，可以使用of子句選擇對select的特定資料表進行加鎖操作。預設情況下，不使用of子句表示在select所有的資料表中加鎖 加鎖行為子句： 當我們進行for update的操作時，與普通select有很大不同。一般select是不需要考慮資料是否被鎖定，最多根據多版本一致讀的特性讀取之前的版本。
規則 for UPDATE語句將鎖定查詢結果中的元組，這些元組將不能被其他事務的UPDATE，delete和for UPDATE操作，直到本事務提交。

應用場景

那麼，什麼時候需要使用for update？就是那些需要業務層面資料獨佔時，可以考慮使用for update。場景上，例如火車票訂票，在螢幕上顯示餘票，而真正進行出票時，需要重新確定一下這個資料沒有被其他客戶端修改。所以，在這個確認過程中，可以使用for update。這是統一的解決方案問題，需要前期準備。

由於InnoDB預設是Row-Level Lock，所以只有「明確」的指定主鍵，MySQL才會執行Row lock (只鎖定已選取的資料例) ，否則MyMy將整個資料表單給鎖住)。

舉例1

給你舉幾個例子：

select * from t for update 会等待行锁释放之后，返回查询结果。
select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果
select * from t for update wait 5 等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录

SELECT...FOR UPDATE 陳述式的語法如下： 
　

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

其中： 
　

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

上的特定列。 
　　WAIT 子句指定等待其他使用者釋放鎖定的秒數，防止無限期的等待。

「使用FOR UPDATE WAIT」子句的優點如下： 
　　1防止無限期地等待被鎖定的行； 
　　2允許應用程式中對鎖定的等待時間進行更多的控制。
　　3對於互動式應用程式非常有用，因為這些使用者不能等待不確定 

　　4 若使用了skip locked，則可以越過鎖定的行，不會報告由wait n 引發的'資源忙'異常報告

舉例2

假設有個表單products ，裡面有id跟name二欄位，id是主鍵。

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

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

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

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

 

無主鍵，table lock)

SELECT * FROM products WHERE id<>&#39;3&#39; FOR UPDATE;

 

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

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

 

例4: rrreee

 

例4: (主鍵不明確，table3:13) FOR UPDATE僅適用於InnoDB，且必須在交易區塊(BEGIN/COMMIT)中才能生效。

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

 

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

另外：MyAsim 只支援表級鎖定，InnerDB支援行級鎖定。

新增了(行級鎖/表級鎖)鎖的資料不能被其它事務再鎖定，也不被其它事務修改（修改、刪除）是表級鎖時，不管是否查詢到記錄，都會鎖定表。

此外，如果A與B都對錶id進行查詢但查詢不到記錄，則A與B在查詢上不會進行row鎖，但A與B都會取得排它鎖，此時A再插入一條記錄的話則會因為B已經有鎖而處於等待中，此時B再插入一條同樣的數據則會拋出Deadlock found when trying to get lock; try restarting transaction然後釋放鎖，此時A就獲得了鎖而插入成功。

知識補充

鎖是資料庫中的一個非常重要的概念，它主要用於在多用戶環境下保證資料庫完整性和一致性。 我們知道，多個使用者能夠同時操縱同一個資料庫中的數據，會發生數據不一致現象。即如果沒有鎖定且多個使用者同時存取一個資料庫，則當他們的事務同時使用相同的資料時可能會發生問題。這些問題包括：遺失更新、髒讀、不可重複讀和幻覺讀：

1．當兩個或多個交易選擇同一行，然後基於最初選定的值更新該行時，會發生遺失更新問題。每個事務都不知道其它事務的存在。最後的更新將重寫由其它事務所所做的更新，這將導致資料遺失。例如，兩位編輯人員製作了同一文件的電子複本。每個編輯人員獨立地更改其複本，然後保存更改後的複本，這樣就覆蓋了原始文檔。最後儲存其更改複本的編輯人員覆蓋了第一個編輯人員所做的更改。如果在第一個編輯人員完成之後第二個編輯人員才能進行更改，則可以避免該問題。

2. 臟讀就是指當一個事務正在訪問數據，並且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時，另外一個事務也訪問這個數據，然後使用了這個數據。因為這個數據是還沒有提交的數據，那麼另外一個事務讀到的這個數據是髒數據，而依據髒數據所做的操作可能是不正確的。例如，一個編輯人員正在更改電子文檔。在變更過程中，另一個編輯人員複製了該文件（複本包含到目前為止所做的全部變更）並將其分發給預期的使用者。此後，第一個編輯人員認為目前所做的更改是錯誤的，於是刪除了所做的編輯並儲存了文件。分發給使用者的文件包含不再存在的編輯內容，而這些編輯內容應認為從未存在過。如果在第一個編輯人員確定最終更改之前任何人都無法讀取更改的文檔，則可以避免該問題。

3．不可重複讀是指在一個交易內，多次讀相同資料。在這個事務還沒結束時，另外一個事務也存取該相同資料。那麼，在第一個事務中的兩次讀取資料之間，由於第二個事務的修改，那麼第一個事務兩次讀到的的資料可能是不一樣的。這樣就發生了在一個事務內兩次讀到的資料是不一樣的，因此稱為是不可重複讀。例如，一個編輯人員兩次讀取同一文檔，但在兩次讀取之間，作者重寫了該文檔。當編輯人員第二次讀取文件時，文件已更改。原始讀取不可重複。如果只有在作者全部完成編寫後編輯人員才可以讀取文檔，則可以避免該問題。

 
4．幻覺讀取是指當事務不是獨立執行時發生的一種現象，例如第一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，第二個事務也修改這個表中的數據，這種修改是向表中插入一行新數據。那麼，以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行，就好像發生了幻覺一樣。例如，一個編輯人員更改作者提交的文檔，但當生產部門將其更改內容合併到該文檔的主副本時，發現作者已將未編輯的新資料新增至該文檔。如果在編輯人員和生產部門完成原始文件的處理之前，任何人都無法將新資料新增至文件中，則可以避免該問題。

      所以，處理多重使用者並發存取的方法就是加鎖。鎖是防止其他事務存取指定的資源控制、實現並發控制的主要手段。當一個使用者鎖住資料庫中的某個物件時，其他使用者就無法再存取該物件。加鎖對並發存取的影響體現在鎖的粒度。為了控制鎖定的資源，應該先了解系統的空間管理。

以上就是 mysql進階(四)mysql中select的內容，更多相關內容請關注PHP中文網（www.php.cn）！