mysql資料庫加悲觀鎖（資料庫怎麼實現悲觀鎖）

本文目錄一覽：

• 1、mysql中的樂觀鎖和悲觀鎖怎麼用
• 2、mysql什麼是悲觀鎖和樂觀鎖
• 3、mysql 悲觀鎖 沒有鎖定 什麼原因
• 4、mysql的事務四個特性以及事務的四個隔離級別
• 5、悲觀鎖和樂觀鎖的用處和區別？
• 6、mysql事務隔離級別 以及 悲觀鎖-樂觀鎖

mysql中的樂觀鎖和悲觀鎖怎麼用

關於mysql中的樂觀鎖和悲觀鎖面試的時候被問到的概率還是比較大的。

mysql的悲觀鎖：

其實理解起來非常簡單，當數據被外界修改持保守態度，包括自身系統當前的其他事務，以及來自外部系統的事務處理，因此，在整個數據處理過程中，將數據處於鎖定狀態。悲觀鎖的實現，往往依靠資料庫提供的鎖機制，但是也只有資料庫層提供的鎖機制才能真正保證數據訪問的排他性，否則，即使在自身系統中實現了加鎖機制，也無法保證外部系統不會修改數據。

來點實際的，當我們使用悲觀鎖的時候我們首先必須關閉mysql資料庫的自動提交屬性，因為MySQL默認使用autocommit模式，也就是說，當你執行一個更新操作後，MySQL會立刻將結果進行提交。

關閉命令為：set autocommit=0;

悲觀鎖可以使用select…for update實現，在執行的時候會鎖定數據，雖然會鎖定數據，但是不影響其他事務的普通查詢使用。此處說普通查詢就是平時我們用的：select * from table 語句。在我們使用悲觀鎖的時候事務中的語句例如：

//開始事務

begin;/begin work;/start transaction; (三選一)

//查詢信息

select * from order where id=1 for update;

//修改信息

update order set name=’names’;

//提交事務

commit;/commit work;(二選一)

此處的查詢語句for update關鍵字，在事務中只有SELECT … FOR UPDATE 或LOCK IN SHARE MODE 同一條數據時會等待其它事務結束後才執行，一般的SELECT查詢則不受影響。

執行事務時關鍵字select…for update會鎖定數據，防止其他事務更改數據。但是鎖定數據也是有規則的。

查詢條件與鎖定範圍：

1、具體的主鍵值為查詢條件

比如查詢條件為主鍵ID=1等等，如果此條數據存在，則鎖定當前行數據，如果不存在，則不鎖定。

2、不具體的主鍵值為查詢條件

比如查詢條件為主鍵ID1等等，此時會鎖定整張數據表。

3、查詢條件中無主鍵

會鎖定整張數據表。

4、如果查詢條件中使用了索引為查詢條件

明確指定索引並且查到，則鎖定整條數據。如果找不到指定索引數據，則不加鎖。

悲觀鎖的確保了數據的安全性，在數據被操作的時候鎖定數據不被訪問，但是這樣會帶來很大的性能問題。因此悲觀鎖在實際開發中使用是相對比較少的。

mysql的樂觀鎖：

相對悲觀鎖而言，樂觀鎖假設數據一般情況下不會造成衝突，所以在數據進行提交更新的時候，才會對數據的衝突與否進行檢測，如果發現衝突，則讓返回用戶錯誤的信息，讓用戶決定如何去做。

一般來說，實現樂觀鎖的方法是在數據表中增加一個version欄位，每當數據更新的時候這個欄位執行加1操作。這樣當數據更改的時候，另外一個事務訪問此條數據進行更改的話就會操作失敗，從而避免了並發操作錯誤。當然，還可以將version欄位改為時間戳，不過原理都是一樣的。

例如有表student，欄位：

id,name,version

1 a 1

當事務一進行更新操作：update student set name=’ygz’ where id = #{id} and version = #{version};

此時操作完後數據會變為id = 1,name = ygz,version = 2，當另外一個事務二同樣執行更新操作的時候，卻發現version != 1，此時事務二就會操作失敗，從而保證了數據的正確性。

悲觀鎖和樂觀鎖都是要根據具體業務來選擇使用，本文僅作簡單介紹。

mysql什麼是悲觀鎖和樂觀鎖

悲觀鎖(Pessimistic Lock), 顧名思義，就是很悲觀，每次去拿數據的時候都認為別人會修改，所以每次在拿數據的時候都會上鎖，這樣別人想拿這個數據就會block直到它拿到鎖。傳統的關係型資料庫裡邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。

樂觀鎖(Optimistic Lock), 顧名思義，就是很樂觀，每次去拿數據的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數據，可以使用版本號等機制。樂觀鎖適用於多讀的應用類型，這樣可以提高吞吐量，像資料庫如果提供類似於write_condition機制的其實都是提供的樂觀鎖。

mysql 悲觀鎖 沒有鎖定 什麼原因

mysql 悲觀鎖 沒有鎖定 的原因主要是事務控制不嚴謹。

悲觀鎖指的是對數據被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度，因此，在整個數據處理過程中，將數據處於鎖定狀態。

悲觀鎖的實現，往往依靠資料庫提供的鎖機制（也只有資料庫層提供的鎖機制才能真正保證數據訪問的排他性，否則，即使在本系統中實現了加鎖機制，也無法保證外部系統不會修改數據）。

舉例說明：

可以使用命令設置MySQL為非autocommit模式：

set autocommit=0;

設置完autocommit後，就可以執行系統的正常業務了。

具體如下：

//0.開始事務

begin;

1.查詢出商品信息

select status from t_goods where id=1 for update; 在此使用了select…for update的方式，這樣就通過資料庫實現了悲觀鎖。

2.根據商品信息生成訂單

insert into t_orders (id,goods_id) values (null,1);

3.修改商品status為2

update t_goods set status=2;

4.提交事務

commit;

註：上面的begin/commit為事務的開始和結束，因為在前一步關閉了mysql的autocommit，所以需要手動控制事務的提交。

mysql的事務四個特性以及事務的四個隔離級別

分別是原子性、一致性、隔離性、持久性。

原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗回滾，因此事務的操作如果成功就必須要完全應用到資料庫，如果操作失敗則不能對資料庫有任何影響。

一致性是指事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態，也就是說一個事務執行之前和執行之後都必須處於一致性狀態。舉例來說，假設用戶A和用戶B兩者的錢加起來一共是1000，那麼不管A和B之間如何轉賬、轉幾次賬，事務結束後兩個用戶的錢相加起來應該還得是1000，這就是事務的一致性。

隔離性是當多個用戶並發訪問資料庫時，比如同時操作同一張表時，資料庫為每一個用戶開啟的事務，不能被其他事務的操作所干擾，多個並發事務之間要相互隔離。關於事務的隔離性資料庫提供了多種隔離級別，稍後會介紹到。

持久性是指一個事務一旦被提交了，那麼對資料庫中的數據的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。例如我們在使用JDBC操作資料庫時，在提交事務方法後，提示用戶事務操作完成，當我們程序執行完成直到看到提示後，就可以認定事務已經正確提交，即使這時候資料庫出現了問題，也必須要將我們的事務完全執行完成。否則的話就會造成我們雖然看到提示事務處理完畢，但是資料庫因為故障而沒有執行事務的重大錯誤。這是不允許的。

在資料庫操作中，在並發的情況下可能出現如下問題：

正是為了解決以上情況，資料庫提供了幾種隔離級別。

資料庫事務的隔離級別有4個，由低到高依次為Read uncommitted(未授權讀取、讀未提交)、Read committed（授權讀取、讀提交）、Repeatable read（可重複讀取）、Serializable（序列化），這四個級別可以逐個解決臟讀、不可重複讀、幻象讀這幾類問題。

雖然資料庫的隔離級別可以解決大多數問題，但是靈活度較差，為此又提出了悲觀鎖和樂觀鎖的概念。

悲觀鎖，它指的是對數據被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度。因此，在整個數據處理過程中，將數據處於鎖定狀態。悲觀鎖的實現，往往依靠資料庫提供的鎖機制。也只有資料庫層提供的鎖機制才能真正保證數據訪問的排他性，否則，即使在本系統的數據訪問層中實現了加鎖機制，也無法保證外部系統不會修改數據。

商品t_items表中有一個欄位status，status為1代表商品未被下單，status為2代表商品已經被下單（此時該商品無法再次下單），那麼我們對某個商品下單時必須確保該商品status為1。假設商品的id為1。

如果不採用鎖，那麼操作方法如下：

但是上面這種場景在高並發訪問的情況下很可能會出現問題。例如當第一步操作中，查詢出來的商品status為1。但是當我們執行第三步Update操作的時候，有可能出現其他人先一步對商品下單把t_items中的status修改為2了，但是我們並不知道數據已經被修改了，這樣就可能造成同一個商品被下單2次，使得數據不一致。所以說這種方式是不安全的。

在上面的場景中，商品信息從查詢出來到修改，中間有一個處理訂單的過程，使用悲觀鎖的原理就是，當我們在查詢出t_items信息後就把當前的數據鎖定，直到我們修改完畢後再解鎖。那麼在這個過程中，因為t_items被鎖定了，就不會出現有第三者來對其進行修改了。需要注意的是，要使用悲觀鎖，我們必須關閉mysql資料庫的自動提交屬性，因為MySQL默認使用autocommit模式，也就是說，當你執行一個更新操作後，MySQL會立刻將結果進行提交。我們可以使用命令設置MySQL為非autocommit模式： set autocommit=0;

設置完autocommit後，我們就可以執行我們的正常業務了。具體如下：

上面的begin/commit為事務的開始和結束，因為在前一步我們關閉了mysql的autocommit，所以需要手動控制事務的提交。

上面的第一步我們執行了一次查詢操作： select status from t_items where id=1 for update; 與普通查詢不一樣的是，我們使用了 select…for update 的方式，這樣就通過資料庫實現了悲觀鎖。此時在t_items表中，id為1的那條數據就被我們鎖定了，其它的事務必須等本次事務提交之後才能執行。這樣我們可以保證當前的數據不會被其它事務修改。需要注意的是，在事務中，只有 SELECT … FOR UPDATE 或 LOCK IN SHARE MODE 操作同一個數據時才會等待其它事務結束後才執行，一般 SELECT … 則不受此影響。拿上面的實例來說，當我執行 select status from t_items where id=1 for update; 後。我在另外的事務中如果再次執行 select status from t_items where id=1 for update; 則第二個事務會一直等待第一個事務的提交，此時第二個查詢處於阻塞的狀態，但是如果我是在第二個事務中執行 select status from t_items where id=1; 則能正常查詢出數據，不會受第一個事務的影響。

使用 select…for update 會把數據給鎖住，不過我們需要注意一些鎖的級別，MySQL InnoDB默認Row-Level Lock，所以只有「明確」地指定主鍵或者索引，MySQL 才會執行Row lock (只鎖住被選取的數據) ，否則MySQL 將會執行Table Lock (將整個數據表單給鎖住)。舉例如下：

1、 select * from t_items where id=1 for update;

這條語句明確指定主鍵（id=1），並且有此數據（id=1的數據存在），則採用row lock。只鎖定當前這條數據。

2、 select * from t_items where id=3 for update;

這條語句明確指定主鍵，但是卻查無此數據，此時不會產生lock（沒有元數據，又去lock誰呢？）。

3、 select * from t_items where name=’手機’ for update;

這條語句沒有指定數據的主鍵，那麼此時產生table lock，即在當前事務提交前整張數據表的所有欄位將無法被查詢。

4、 select * from t_items where id0 for update; 或者 select * from t_items where id1 for update; （註：在SQL中表示不等於）

上述兩條語句的主鍵都不明確，也會產生table lock。

5、 select * from t_items where status=1 for update; （假設為status欄位添加了索引）

這條語句明確指定了索引，並且有此數據，則產生row lock。

6、 select * from t_items where status=3 for update; （假設為status欄位添加了索引）

這條語句明確指定索引，但是根據索引查無此數據，也就不會產生lock。

樂觀鎖（ Optimistic Locking ） 相對悲觀鎖而言，樂觀鎖假設認為數據一般情況下不會造成衝突，所以只會在數據進行提交更新的時候，才會正式對數據的衝突與否進行檢測，如果發現衝突了，則返回用戶錯誤的信息，讓用戶決定如何去做。實現樂觀鎖一般來說有以下2種方式：

悲觀鎖和樂觀鎖的用處和區別？

樂觀鎖和悲觀鎖的區別如下：

1、悲觀鎖是當線程拿到資源時，就對資源上鎖，並在提交後，才釋放鎖資源，其他線程才能使用資源。

2、樂觀鎖是當線程拿到資源時，上樂觀鎖，在提交之前，其他的鎖也可以操作這個資源，當有衝突的時候，並發機制會保留前一個提交，打回後一個提交，讓後一個線程重新獲取資源後，再操作，然後提交。和git上傳代碼一樣，兩個線程都不是直接獲取資源本身，而是先獲取資源的兩個copy版本，然後在這兩個copy版本上修改。

3、悲觀鎖和樂觀鎖在並發量低的時候，性能差不多，但是在並發量高的時候，樂觀鎖的性能遠遠優於悲觀鎖。

4、常用的synchronized是悲觀鎖，lock是樂觀鎖。

mysql事務隔離級別 以及 悲觀鎖-樂觀鎖

以上不是重點，重點是 對事務控制語句不熟悉。

SAVEPOINT identifier : 在事務中 創建保存點。一個事務中 允許有多個保存點。

RELEASE SAVEPOINT identifier : 刪除保存點。當事務中 沒有指定的 保存點，執行該語句 會拋異常。

ROLLBACK TO identifier : 把事務回滾到 保存點。

Say you have a table T with a column C with one row in it, say it has the value ‘1’. And consider you have a simple task like following:

That is a simple task that issue two reads from table T, with a delay of 1 minute between them.

If you follow the logic above you can quickly realize that SERIALIZABLE transactions, while they may make life easy for you, are always completely blocking every possible concurrent operation, since they require that nobody can modify, delete nor insert any row. The default transaction isolation level of the .Net System.Transactions scope is serializable, and this usually explains the abysmal performance that results.

在Repeatable Read隔離級別下，一個事務可能會遇到幻讀（Phantom Read）的問題。

幻讀是指，在一個事務中，第一次查詢某條記錄，發現沒有，但是，當試圖更新這條不存在的記錄時，竟然能成功，並且，再次讀取同一條記錄，它就神奇地出現了。

我們仍然先準備好students表的數據：

然後，分別開啟兩個MySQL客戶端連接，按順序依次執行事務A和事務B：

事務B在第3步第一次讀取id=99的記錄時，讀到的記錄為空，說明不存在id=99的記錄。隨後，事務A在第4步插入了一條id=99的記錄並提交。事務B在第6步再次讀取id=99的記錄時，讀到的記錄仍然為空，但是，事務B在第7步試圖更新這條不存在的記錄時，竟然成功了，並且，事務B在第8步再次讀取id=99的記錄時，記錄出現了。

可見，幻讀就是沒有讀到的記錄，以為不存在，但其實是可以更新成功的，並且，更新成功後，再次讀取，就出現了。

在衝突較少的情況下，使用樂觀鎖。樂觀鎖 因為沒有 加鎖 釋放鎖，也減少了 加鎖 釋放鎖的開銷。

衝突較多時，如果使用樂觀鎖 需要不停地嘗試，所以 使用悲觀鎖。

如果樂觀鎖 進行嘗試時 的花銷較大，也是使用悲觀鎖。