MySQL之事務及并發控制

本節索引：

一、事務Transaction

二、事務的隔離級別

三、并發控制

四、MVCC-多版本的并發控制協議

 

一、事務

什么是事務？

事務Transactions：一組原子性的SQL語句，或一個獨立工作單元。

事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統中，你刪除一個

人員，你既需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，

這樣，這些數據庫操作語句就構成一個事務

 

事務日志：記錄事務信息，實現undo,redo等故障恢復功能

 

ACID特性：

A：atomicity原子性；

整個事務中的所有操作要么全部成功執行，要么全部失敗后回滾，不可能停滯在中間某個環節。

 

C：consistency一致性；

數據庫總是從一個一致性狀態轉換為另一個一致性狀態

也就是說：如果事務是并發多個，系統也必須如同串行事務一樣操作。其主要特征是保護性和不

變性(Preserving an Invariant)，以轉賬案例為例，假設有五個賬戶，每個賬戶余額是100元，那

么五個賬戶總額是500元，如果在這個5個賬戶之間同時發生多個轉賬，無論并發多少個，比如在

A與B賬戶之間轉賬5元，在C與D賬戶之間轉賬10元，在B與E之間轉賬15元，五個賬戶總額也應該

還是500元，這就是保護性和不變性

 

I：Isolation隔離性；

一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離級別，實現并發。

如果有兩個事務，運行在相同的時間內，執行相同的功能，事務的隔離性將確保每一事務在系統中

認為只有該事務在使用系統。這種屬性有時稱為串行化，為了防止事務操作間的混淆，必須串行化

或序列化請求，使得在同一時間僅有一個請求用于同一數據。

 

D：durability持久性；

一旦事務提交，其所做的修改會永久保存于數據庫中

 

如圖所示一個事務的生命周期：

(1) 開啟一個事務

(2) 進行事務操作，注：只有對數據庫的增、刪、改操作才記入事務，SELECT查

詢語句不計入事務。

(3) ROLLBACK將會使未提交的數據回滾，數據還原至更改前的數據

(4) 一旦進行了COMMIT提交，新的數據將會持久的保存在數據庫之中，并不會被回

滾，即事務的持久性。

 

 

事務控制語句：

BEGIN或START TRANSACTION：顯式地開啟一個事務；

 

ROLLBACK：回滾會結束用戶的事務，并撤銷正在進行的所有未提交的修改；

 

COMMIT：提交事務，并使已對數據庫進行的所有修改稱為永久性的；

 

SAVEPOINT identifier：SAVEPOINT允許在事務中創建一個保存點，一個事務中可以有多個SAVEPOINT；

 

ROLLBACK TO identifier：把事務回滾到標記點；

 

RELEASE SAVEPOINT identifier：刪除一個事務的保存點，當沒有指定的保存點時，執行該語句會拋出一個異常；

 

 

實現MySQL事務處理：

方法一：用 BEGIN, ROLLBACK, COMMIT來實現

BEGIN 開始一個事務

 

ROLLBACK 事務回滾

 

COMMIT 事務確認

 

方法二：直接用 SET 來改變 MySQL 的自動提交模式:

 

SET AUTOCOMMIT=OFF 禁止自動提交

 

SET AUTOCOMMIT=ON? 開啟自動提交（系統默認項）

 

示例：事務測試

 

二、事務隔離級別

在數據庫操作中，為了有效保證并發讀取數據的正確性，提出的事務隔離級別。

數據庫是要被廣大客戶所共享訪問的，那么在數據庫操作過程中很可能出現以下幾種不確定情況。

更新丟失

兩個事務都同時更新一行數據，一個事務對數據的更新把另一個事務對數據的更新覆蓋了。這是因為系統

沒有執行任何的鎖操作，因此并發事務并沒有被隔離開來。

 

臟讀

一個事務讀取到了另一個事務未提交的數據操作結果。這是相當危險的，因為很可能所有的操作都被回

滾。

 

虛讀

事務T1讀取某一數據后，事務T2對其做了修改，當事務T1再次讀該數據時得到與前一次不同的值。

 

幻讀

事務在操作過程中進行兩次查詢，第二次查詢的結果包含了第一次查詢中未出現的數據或者缺少了第一次

查詢中出現的數據（這里并不要求兩次查詢的SQL語句相同）。這是因為在兩次查詢過程中有另外一個事

務插入數據造成的。

 

事務隔離級別

為了避免上面出現的幾種情況，在標準SQL規范中，定義了4個事務隔離級別，不同的隔離級別對事務的

處理不同。

從上至下更加嚴格：

READ UNCOMMITTED：可讀取到未提交數據，產生臟讀

 

READ COMMITTED：可讀取到提交數據，但未提交數據不可讀，產

生不可重復讀，即可讀取到多個提交數據，導致每次讀取數據不一致

 

REPEATABLE READ 可重復讀，多次讀取數據都一致，產生幻讀，即

讀取過程中，即使有其它提交的事務修改數據，仍只能讀取到未修改

前的舊數據。此為MySQL默認設置

 

SERIALIZABILE 可串行化，未提交的讀事務阻塞修改事務，或者未

提交的修改事務阻塞讀事務。導致并發性能差

 

 

管理事務隔離級別：

服務器變量tx_isolation指定，

默認為REPEATABLE-READ，可在GLOBAL和SESSION級進行設置

SET tx_isolation=”

? ? ? ? ? ? ? READ-UNCOMMITTED

? ? ? ? ? ? ? READ-COMMITTED

? ? ? ? ? ? ? REPEATABLE-READ

? ? ? ? ? ? ? SERIALIZABLE

服務器選項中指定

vim /etc/my.cnf

[mysqld]

transaction-isolation=SERIALIZABLE

 

 

 

三、并發控制

鎖粒度：

表級鎖

行級鎖

鎖：

讀鎖：共享鎖，只讀不可寫，多個讀互不阻塞，

寫鎖：獨占鎖,排它鎖，一個寫鎖會阻塞其它讀和它鎖

實現：

存儲引擎：自行實現其鎖策略和鎖粒度

服務器級：實現了鎖，表級鎖；用戶可顯式請求

分類：

隱式鎖：由存儲引擎自動施加鎖

顯式鎖：用戶手動請求

 

鎖策略：在鎖粒度及數據安全性尋求的平衡機制

顯示使用鎖

? LOCK TABLES

tbl_name [[AS] alias] lock_type

???????? [, tbl_name [[AS] alias] lock_type] …

???????? lock_type: READ ， WRITE

解鎖

UNLOCK TABLES

 

FLUSH TABLES tb_name[,…] [WITH READ LOCK]?

關閉正在打開的表（清除查詢緩存），通常在備份前加全局讀鎖

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

查詢時加寫或讀鎖

 

讀鎖：

讀鎖也稱為共享鎖，讀鎖允許多個連接可以同一時刻并發的讀取同一資源,互不干擾；

添加讀鎖：

MariaDB [hellodb]>?lock tables teachers read;

 

寫鎖：

寫鎖也稱為排他鎖，一個寫鎖會阻塞其他的寫鎖或讀鎖，保證同一時刻只有一個連接可以寫入數據，同時

防止其他用戶對這個數據的讀寫。

添加寫鎖：

MariaDB [hellodb]>?lock tables students write;

 

 

死鎖：

兩個或多個事務在同一資源相互占用并請求鎖定對方占用的資源的狀態

如下所示：

事務1 ? ? ? ? ? ? ? ? ? ?事務2

update table1??? update table2

update table2 ? ?update table1

產生死鎖

 

四、MVCC-多版本的并發控制協議

MySQL InnoDB存儲引擎，實現的是基于多版本的并發控制協議——MVCC?(Multi-Version

?Concurrency Control)?(注：與MVCC相對的，是基于鎖的并發控制，Lock-Based

Concurrency Control)。MVCC最大的好處，相信也是耳熟能詳：讀不加鎖，讀寫不沖突。

在讀多寫少的OLTP應用中，讀寫不沖突是非常重要的，極大的增加了系統的并發性能。

 

InnoDB在每行數據都增加兩個隱藏字段，一個記錄創建的版本號，一個記錄刪除的版本號。

 

* SELECT：

當隔離級別是REPEATABLE READ時select操作，InnoDB必須每行數據來保證它符合兩個條件：

1、InnoDB必須找到一個行的版本，它至少要和事務的版本一樣老(也即它的版本號不大于

事務的版本號)。這保證了不管是事務開始之前，或者事務創建時，或者修改了這行數據的

時候，這行數據是存在的。

2、這行數據的刪除版本必須是未定義的或者比事務版本要大。這可以保證在事務開始之前

這行數據沒有被刪除。

符合這兩個條件的行可能會被當作查詢結果而返回。

* INSERT：<br>InnoDB為這個新行記錄當前的系統版本號。

* DELETE：<br>InnoDB將當前的系統版本號設置為這一行的刪除ID。

* UPDATE：<br>InnoDB會寫一個這行數據的新拷貝，這個拷貝的版本為當前的系統版本號。

它同時也會將這個版本號寫到舊行的刪除版本里。