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

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

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

MySQL中的鎖

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

這3種鎖的特性如下：

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

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

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

MyISAM

MyISAM表鎖

MySQL為表提供了兩種類(lèi)型的鎖，它們是：

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

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

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

MyISAM在執行查詢(xún)語(yǔ)句(SELECT)前，會(huì )自動(dòng)給使用到的所有表加讀鎖，在執行更新操作(UPDATE、DELETE、INSERT等)前，會(huì )自動(dòng)給涉及的表加寫(xiě)鎖，這個(gè)過(guò)程并不需要我們手動(dòng)干預，所以我們一般不需要用LOCK TABLE命令給MyISAM表顯式加鎖，但是顯示加鎖也沒(méi)有什么問(wèn)題。

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

下面看一個(gè)列子。

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、會(huì )話(huà)1獲取寫(xiě)鎖

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

3、會(huì )話(huà)2讀取。

我們知道在某個(gè)會(huì )話(huà)持有WRITE鎖時(shí)，所有其他會(huì )話(huà)都無(wú)法訪(fǎng)問(wèn)該表的數據，所以在第二個(gè)會(huì )話(huà)執行下面語(yǔ)句時(shí)，會(huì )一直處于等待狀態(tài)。

mysql> select * from test_table;

4、會(huì )話(huà)1解鎖

unlock table;

并發(fā)插入

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

concurrent_insert取值如下：

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

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

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

空洞指的是表的中間沒(méi)有被刪除的行。

InnoDB

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

事務(wù)特性

• 原子性

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

• 一致性

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

• 隔離性

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

• 持久性

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

并發(fā)事務(wù)處理帶來(lái)的問(wèn)題

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

更新丟失

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

臟讀

臟讀又稱(chēng)無(wú)效數據的讀出，當事務(wù)1將某一值修改后，然后事務(wù)2讀取該值，后面事務(wù)1又因為一些原因撤銷(xiāo)對該值的修改，這就導致了事務(wù)2所讀取到的數據是無(wú)效的。

不可重復讀

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

幻讀

當事務(wù)1按相同的查詢(xún)條件重新讀取以前查詢(xún)過(guò)的數據時(shí)，卻發(fā)現其他事務(wù)插入了滿(mǎn)足這個(gè)條件的新數據。

事務(wù)隔離級別

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

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

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

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

查詢(xún)/更改隔離級別

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

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

READ UNCOMMITTED（讀未提交）

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

例子

啟動(dòng)兩個(gè)會(huì )話(huà)，并設置隔離級別為READ UNCOMMITTED。

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

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

READ COMMITTED （讀已提交）

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

例子

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

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

REPEATABLE READ （可重復讀）

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

例子

設置兩個(gè)會(huì )話(huà)隔離級別為可重復讀
set session transaction isolation level repeatable read;

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

例子

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

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

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

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

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

InnoDB行鎖

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

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

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

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

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

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

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

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

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

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

Next-Key鎖

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

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

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

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

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