MySQL中事務(wù)和鎖的示例分析

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

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

MySQL中的鎖

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

這3種鎖的特性如下：

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

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

• 頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間，會出現(xiàn)死鎖，鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

MyISAM

MyISAM表鎖

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

• READ LOCK： 允許用戶僅從表中讀取數(shù)據(jù)。

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

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

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

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

下面看一個列子。

1、創(chuàng)建一張表

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鎖時(shí)，所有其他會話都無法訪問該表的數(shù)據(jù)，所以在第二個會話執(zhí)行下面語句時(shí)，會一直處于等待狀態(tài)。

mysql> select * from test_table;

4、會話1解鎖

unlock table;

并發(fā)插入

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

concurrent_insert取值如下：

• 0：不允許并發(fā)插入功能。

• 1：允許對沒有空洞的表使用并發(fā)插入，新數(shù)據(jù)位于數(shù)據(jù)文件結(jié)尾（缺?。?。

• 2：不管表有沒有空洞，都允許在數(shù)據(jù)文件結(jié)尾并發(fā)插入。

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

InnoDB

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

事務(wù)特性

• 原子性

  事務(wù)是一個原子操作單元， 對數(shù)據(jù)的修改，要么全部執(zhí)行，要么全都不執(zhí)行。

• 一致性

  在事務(wù)開始和完成時(shí)， 數(shù)據(jù)都必須保持一致狀態(tài)。 這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持?jǐn)?shù)據(jù)的完整性。

• 隔離性

  數(shù)據(jù)庫系統(tǒng)保證事務(wù)在不受外部并發(fā)操作影響，可以"獨(dú)立"環(huán)境執(zhí)行，這意味著事務(wù)處理過程中的中間狀態(tài)對外部是不可見的。

• 持久性

  事務(wù)完成之后，它對于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

并發(fā)事務(wù)處理帶來的問題

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

更新丟失

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

臟讀

臟讀又稱無效數(shù)據(jù)的讀出，當(dāng)事務(wù)1將某一值修改后，然后事務(wù)2讀取該值，后面事務(wù)1又因?yàn)橐恍┰虺蜂N對該值的修改，這就導(dǎo)致了事務(wù)2所讀取到的數(shù)據(jù)是無效的。

不可重復(fù)讀

指的是一個事務(wù)在讀取某些數(shù)據(jù)后，再次讀取之前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變。

幻讀

當(dāng)事務(wù)1按相同的查詢條件重新讀取以前查詢過的數(shù)據(jù)時(shí)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足這個條件的新數(shù)據(jù)。

事務(wù)隔離級別

上面說的"更新丟失"是應(yīng)該完全避免的，但防止更新丟失，并不能單靠數(shù)據(jù)庫事務(wù)控制器來解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖。

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

數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上串行化進(jìn)行。

InnoDB有四個事務(wù)隔離級別： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默認(rèn)隔離級別是REPEATABLE READ。

查詢/更改隔離級別

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

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

READ UNCOMMITTED（讀未提交）

在這個隔離級別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。這種隔離級別在實(shí)際應(yīng)用中很少使用，讀取未提交的數(shù)據(jù)也稱為臟讀。

例子

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

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

可以看到，在T4時(shí)刻，事務(wù)1沒有提交，但是事務(wù)2可以看到被事務(wù)1鎖更改的數(shù)據(jù)。

READ COMMITTED （讀已提交）

這是大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認(rèn)隔離級別，但不是MySQL的默認(rèn)級別，他避免了臟讀現(xiàn)象，因?yàn)樵谌魏挝刺峤坏氖聞?wù)前，對任何其他事務(wù)都是不可見的，也就是其他事務(wù)看不到未提交的數(shù)據(jù)，允許不可重復(fù)讀。

例子

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

可以看到，在T4時(shí)刻，事務(wù)1沒有提交，但是事務(wù)2讀取到的數(shù)據(jù)還是100,當(dāng)事務(wù)1提交后，事務(wù)2才可以看到。

REPEATABLE READ （可重復(fù)讀）

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

例子

設(shè)置兩個會話隔離級別為可重復(fù)讀
set session transaction isolation level repeatable read;

可以看到，在T3時(shí)刻，事務(wù)1已經(jīng)提交更改，但是在T4時(shí)刻的事務(wù)2中，還是讀取到了原來的數(shù)據(jù)，但是如果事務(wù)2在原來的基礎(chǔ)上再減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語句隱式轉(zhuǎn)換為SELECT ... LOCK IN SHARE MODE，所有事務(wù)按照順序依次執(zhí)行，因此，臟讀、不可重復(fù)讀、幻讀都不會出現(xiàn)。但是，由于事務(wù)是串行執(zhí)行，所以效率會大大下降，

例子

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

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

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

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

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

InnoDB行鎖

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

• 共享鎖允許持有鎖的事務(wù)讀取行。

• 獨(dú)占鎖允許持有鎖事務(wù)的更新或刪除行。

為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖，這兩種意向鎖都是表鎖。

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

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

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

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

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

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

Next-Key鎖

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

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

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

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

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

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