MySQL中事務和鎖的示例分析

小編給大家分享一下MySQL中事務和鎖的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

MySQL數據庫是一個多用戶訪問系統，那么就要面臨當多個用戶同時讀取和更新數據時，數據不會被破壞，所以就誕生了鎖，鎖一種并發控制技術，當一個用戶嘗試修改數據庫中的記錄時，首先要獲取鎖，那么持有這個鎖的用戶還在修改時，其他用戶就不能對這些記錄進行修改了。

MySQL中的鎖

但是相對其他數據庫而言，MySQL的鎖機制比較簡單，MySQL不同的存儲引擎有不同的鎖機制，MylSAM和MEMORY存儲引擎采用的是表級鎖，BDB存儲引擎采用的是頁面鎖，而常用的InnoDB存儲引擎支持行級鎖、表級鎖，默認情況下是采用行級鎖。

這3種鎖的特性如下：

• 表級鎖：開銷小，加鎖快，不會出現死鎖，鎖定粒度大，發生鎖沖突的概率最高，并發度最低。

• 行級鎖：開銷大，加鎖慢，會出現死鎖，鎖定粒度最小，發生鎖沖突的概率最低,并發度也最高。

• 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間，會出現死鎖，鎖定粒度界于表鎖和行鎖之間，并發度一般。

MyISAM

MyISAM表鎖

MySQL為表提供了兩種類型的鎖，它們是：

• READ LOCK： 允許用戶僅從表中讀取數據。

• WRITE LOCK： 允許用戶對表進行讀取和寫入操作。

MyISAM對表的讀操作，不會阻塞其他用戶對同一表的讀請求， 但是會阻塞對同一表的寫請求，MyISAM對表的寫操作，會阻塞其他用戶對同一表的讀和寫操作， MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的。

MyISAM在執行查詢語句(SELECT)前，會自動給使用到的所有表加讀鎖，在執行更新操作(UPDATE、DELETE、INSERT等)前，會自動給涉及的表加寫鎖，這個過程并不需要我們手動干預，所以我們一般不需要用LOCK TABLE命令給MyISAM表顯式加鎖，但是顯示加鎖也沒有什么問題。

還有在用LOCK TABLES給表顯式加表鎖時，必須同時取得所有涉及表的鎖，因為在執行LOCK TABLES后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表，否則會出錯，同時，如果加的是讀鎖，那么只能執行查詢操作，不能執行更新操作，否則也會報錯，在自動加鎖的情況下也是如此，這也正是MyISAM表不會出現死鎖的原因。

下面看一個列子。

1、創建一張表

CREATE TABLE test_table (   
      Id INT NOT NULL AUTO_INCREMENT,   
      Name VARCHAR(50) NOT NULL,   
      Message VARCHAR(80) NOT NULL,  
      PRIMARY KEY (Id)  
);

2、會話1獲取寫鎖

mysql> lock table  test_table write;
Query OK, 0 rows affected (0.01 sec)

3、會話2讀取。

我們知道在某個會話持有WRITE鎖時，所有其他會話都無法訪問該表的數據，所以在第二個會話執行下面語句時，會一直處于等待狀態。

mysql> select * from test_table;

4、會話1解鎖

unlock table;

并發插入

在MyISAM里讀寫操作是串行的，但是可以根據concurrent_insert的設置，讓MyISAM支持并行查詢和插入。

concurrent_insert取值如下：

• 0：不允許并發插入功能。

• 1：允許對沒有空洞的表使用并發插入，新數據位于數據文件結尾（缺?。?。

• 2：不管表有沒有空洞，都允許在數據文件結尾并發插入。

空洞指的是表的中間沒有被刪除的行。

InnoDB

InnoDB不同于MyISAM，他有兩個特點，一是支持事務，二是采用了行級鎖，行級鎖和表鎖有很多不同的地方。

事務特性

• 原子性

  事務是一個原子操作單元， 對數據的修改，要么全部執行，要么全都不執行。

• 一致性

  在事務開始和完成時， 數據都必須保持一致狀態。 這意味著所有相關的數據規則都必須應用于事務的修改，以保持數據的完整性。

• 隔離性

  數據庫系統保證事務在不受外部并發操作影響，可以"獨立"環境執行，這意味著事務處理過程中的中間狀態對外部是不可見的。

• 持久性

  事務完成之后，它對于數據的修改是永久性的，即使出現系統故障也能夠保持。

并發事務處理帶來的問題

相對于串行處理來說，雖然提高了資源利用率，可以支持更多的用戶，但并發事務處理也會帶來些問題， 主要包括以下幾種情況。

更新丟失

由于每個事務都不知道其他事務的存在，就會發生丟失更新問題，也就是最后的更新覆蓋了由其他事務所做的更新。

臟讀

臟讀又稱無效數據的讀出，當事務1將某一值修改后，然后事務2讀取該值，后面事務1又因為一些原因撤銷對該值的修改，這就導致了事務2所讀取到的數據是無效的。

不可重復讀

指的是一個事務在讀取某些數據后，再次讀取之前讀過的數據，卻發現讀出的數據已經發生了改變。

幻讀

當事務1按相同的查詢條件重新讀取以前查詢過的數據時，卻發現其他事務插入了滿足這個條件的新數據。

事務隔離級別

上面說的"更新丟失"是應該完全避免的，但防止更新丟失，并不能單靠數據庫事務控制器來解決，需要應用程序對要更新的數據加必要的鎖。

而臟讀、不可重復讀、幻讀，都是數據庫讀一致性問題，必須由數據庫提供事務隔離機制來解決。數據庫實現事務隔離的方式，可分為以下兩種，一種是在讀取數據前加鎖，阻止其他事務對數據進行修改，另一種不需要鎖，通過MVCC或MCC來實現，這種技術叫做數據多版本并發控制，通過一定機制生成一個數據請求時間點的一致性數據快照，并用這個快照來提供一定級別的一致性讀取。

數據庫的事務隔離越嚴格，并發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上串行化進行。

InnoDB有四個事務隔離級別： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默認隔離級別是REPEATABLE READ。

查詢/更改隔離級別

顯示隔離級別
show global variables like '%isolation%';
select @@transaction_isolation;

設置隔離級別
set global transaction_isolation ='read-committed';
set session transaction isolation level read uncommitted;

READ UNCOMMITTED（讀未提交）

在這個隔離級別，所有事務都可以看到其他未提交事務的執行結果。這種隔離級別在實際應用中很少使用，讀取未提交的數據也稱為臟讀。

例子

啟動兩個會話，并設置隔離級別為READ UNCOMMITTED。

mysql> select * from user;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 張三      |     100 |
| 李四      |     100 |
| 王五      |      80 |
+-----------+---------+

可以看到，在T4時刻，事務1沒有提交，但是事務2可以看到被事務1鎖更改的數據。

READ COMMITTED （讀已提交）

這是大多數數據庫系統的默認隔離級別，但不是MySQL的默認級別，他避免了臟讀現象，因為在任何未提交的事務前，對任何其他事務都是不可見的，也就是其他事務看不到未提交的數據，允許不可重復讀。

例子

將兩個會話中隔離級別設置為讀已提交
set session transaction isolation level read committed;

可以看到，在T4時刻，事務1沒有提交，但是事務2讀取到的數據還是100,當事務1提交后，事務2才可以看到。

REPEATABLE READ （可重復讀）

這是 MySQL 的默認事務隔離級別，它確保同一事務讀取數據時，將看到相同的數據行，但是會出現幻讀，當事務1按條件進行查詢后，另一個事務在該范圍內插入一個新數據，那么事務1再次讀取時，就會讀到這個新數據。InnoDB 和 Falcon 存儲引擎通過 mvcc（多版本并發控制）機制解決了這個問題。

例子

設置兩個會話隔離級別為可重復讀
set session transaction isolation level repeatable read;

可以看到，在T3時刻，事務1已經提交更改，但是在T4時刻的事務2中，還是讀取到了原來的數據，但是如果事務2在原來的基礎上再減10元，那么最終余額是90還是70呢？,答案是70。.

mysql> update user set balance=balance-10 where user_name="張三";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where user_name="張三";
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 張三      |      70 |
+-----------+---------+
1 row in set (0.00 sec)

SERIALIZABLE （序列化）

他是最高的隔離級別，InnoDB將所有普通SELECT語句隱式轉換為SELECT ... LOCK IN SHARE MODE，所有事務按照順序依次執行，因此，臟讀、不可重復讀、幻讀都不會出現。但是，由于事務是串行執行，所以效率會大大下降，

例子

設置隔離級別為序列化
set session transaction isolation level serializable;

這一次，有趣的是，事務2在T3時刻更新被阻止了,原因是在serializable隔離級別下，MySQL隱式地將所有普通SELECT查詢轉換為SELECT FOR SHARE， 持有SELECT FOR SHARE鎖的事務只允許其他事務對SELECT行進行處理，而不允許其他事務UPDATE或DELETE它們。

所以有了這個鎖定機制，我們之前看到的不一致數據場景就不再可能了。

但是，這個鎖具有超時時間，在等待一會后，如果其他事務在這段時間內沒有提交或回滾釋放鎖，將拋出鎖等待超時錯誤，如下所示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB行鎖

InnoDB 的行級鎖也分為共享鎖和排他鎖兩種。

• 共享鎖允許持有鎖的事務讀取行。

• 獨占鎖允許持有鎖事務的更新或刪除行。

為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖，這兩種意向鎖都是表鎖。

• 意向共享鎖 事務想要獲得一張表中某幾行的共享鎖。

• 意向排他鎖 事務想要獲得一張表中某幾行的排他鎖。

InnoDB 行鎖是通過鎖定索引上的索引條目來實現的，因此，InnoDB 只有在通過索引條件檢索到數據時才使用行級鎖；否則，InnoDB 將使用表鎖。

我們可以顯示的加鎖，但對于update、delete、insert語句，InnoDB會自動給涉及的數據集加排他鎖，對于普通的 select 語句，InnoDB 不會加任何鎖，下面是顯示的加鎖方式：

• 共享鎖：SELECT  FROM table_name WHERE … LOCK IN SHARE MODE

• 排他鎖：SELECT * FROM table_name WHERE … FOR UPDATE

Next-Key鎖

當我們使用范圍條件而不是相等條件檢索數據，并請求其共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖，對于在條件范圍內但并不存在的記錄，叫做間隙(GAP), InnoDB也會對這個"間隙"加鎖，這種鎖機制就是所謂的Next-Key鎖。

舉例來說，假如user表中只有101條記錄，其user_id的值分別是1.2. ..100. 101，當查找大于100的user_id時，使用下面SQL。

select.* from emp where user_id > 100 for update;

這就是一個范圍條件的查詢， InnoDB不僅會對user_id為101的記錄加鎖，也會對user_id大于101的"間隙"加鎖，雖然這些記錄并不存在。

InnoDB使用Next-Key鎖的目的，一方面是為了防止幻讀，另一方面， 是為了滿足恢復和復制的需要。

以上是“MySQL中事務和鎖的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！