mysql的事務

標籤：

當多個使用者訪問同一份資料時候，一個使用者在更改資料的過程中可能有其他使用者同時發起變更要求,為保證資料的更改從一個一致性狀態更改為另外一個一致性狀態,從而引入事務。

MySQL提供了多種儲存引擎支援事務,支援事務的儲存引擎有InnoDB和BDB。InnoDB儲存引擎事務主要通過UNDO日誌和REDO日誌實現。MyISAM和MEMORY儲存引擎則不支援事務。

1. 事務概述事務的四個特性:

a. 原子性(Atomicity):事務中所有的操作視為一個原子單元,即對於事務所進行的資料修改等操作只能是完全提交或者完全復原。

b. 一致性(Consistency):事務在完成時,必須使所有的資料從一種一致性狀態變更為另外一種一致性狀態,所有的變更都必須應用於事務的修改,以確保資料的完整性。

c. 隔離性(Isolation):一個事務中的動作陳述式所做的修改必須與其他事物所做的修改相隔離。在進行事務查看資料時資料所處的狀態,要麼是被另一併發事務修改之前的狀態,要麼是被另一併發事務修改之後的狀態,即當前事務不會查看由另一個並發事務正在修改的資料，這種特性通過鎖機制實現。

d. 持久性(Durability):事務完成之後,所做的修改對資料的影響是永久的,即使系統重啟,或者出現系統故障資料仍可以恢複。

MySQL提供了多種事務型儲存引擎支援,如InnoDB和BDB等,而MyISAM不支援事務,InnoDB支援ACID事務,行級鎖和高並發。為支援事務,InnoDB儲存引擎引入了與事務相關的UNDO日誌和REDO日誌,同時事務依賴於MySQL提供的鎖機制。

1.1 REDO日誌

事務執行時需要將執行的交易記錄寫入到記錄檔裡,對應的記錄檔為REDO日誌。當每條SQL進行資料庫更新操作時,首先將REDO日誌寫入到日誌緩衝區。當用戶端執行COMMIT命令提交時,日誌緩衝區的內容將被重新整理到磁碟,日誌緩衝區的重新整理方式或者時間間隔可以通過參數innidb_flush_log_at_trx_commit控制。

REDO日誌對應於磁碟上的ib_logfileN檔案,該檔案預設為5MB,建議設定成512MB以便容納較大的事務。在MySQL崩潰恢複時會重新執行REDO日誌中的記錄。

1.2 UNDO日誌

UNDO日誌與 REDO日誌相反,UNDO日誌主要用於事務異常時的資料復原,具體內容就是複製事務前的資料庫內容到UNDO緩衝區,然後在合適的時間將內容重新整理到磁碟。

與REDO日誌不同的是,磁碟上不存在單獨的UNDO記錄檔,所有的UNDO日誌均存放在資料表空間對應的.ibd資料檔案中,即使MySQL服務啟用了獨立資料表空間,依然如此。UNDO日誌又稱為復原段。

2. MySQL事務控制語句

MySQL中使用事務BEGIN事務,事務OMMIT結束事務,中間可以使用ROLLBACK復原事務。MySQL通過SET AUTOCOMMIT,START TRANSACTION,COMMIT和ROLLBACK等語句支援本地事務。文法如下:

START TRANSACTION | BEGIN [WORK]COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]SET AUTOCOMMIT = {0 | 1}

在預設設定下,MySQL中的事務是預設提交的。如需對某些語句進行事務控制則使用START TRANSACTION或者BEGIN開始一個事務比較方便。這樣事務結束之後可以自動回到自動認可的方式。

下面樣本示範一個簡單的事務,功能為更新表中的一條記錄,為保證資料從一個一致性狀態更新到另外一種一致性狀態,因此採用事務完成更新過程,如果更新失敗或者其他原因可以使用復原。此執行個體執行時對應的MySQL預設隔離等級為REPEATABLE-READ。

#查看MySQL隔離等級mysql> show variables like ‘tx_isolation‘;+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.33 sec)#建立測試需要的表,主要儲存引擎為InnoBmysql> use company;Database changedmysql> create table test_1(id int,username varchar(20)) engine=InnoDB;Query OK, 0 rows affected (0.67 sec)mysql> insert into test_1 values(1,‘petter‘),(2,‘bob‘),(3,‘allen‘),(4,‘aron‘);Query OK, 4 rows affected (0.17 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from test_1;+------+----------+| id   | username |+------+----------+|    1 | petter   ||    2 | bob      ||    3 | allen    ||    4 | aron     |+------+----------+4 rows in set (0.00 sec)#開啟一個事務mysql> begin;Query OK, 0 rows affected (0.02 sec)#更新一條記錄mysql> update test_1 set username=‘test‘ where id = 1;Query OK, 1 row affected (0.06 sec)Rows matched: 1  Changed: 1  Warnings: 0#提交事務mysql> commit;Query OK, 0 rows affected (0.06 sec)#發現記錄已經更改生效mysql> select * from test_1;+------+----------+| id   | username |+------+----------+|    1 | test     ||    2 | bob      ||    3 | allen    ||    4 | aron     |+------+----------+4 rows in set (0.00 sec)#開啟另外一個事務mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update test_1 set username=‘petter‘ where id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from test_1;+------+----------+| id   | username |+------+----------+|    1 | petter   ||    2 | bob      ||    3 | allen    ||    4 | aron     |+------+----------+4 rows in set (0.00 sec)#復原事務mysql> rollback;Query OK, 0 rows affected (0.13 sec)#此時探索資料已經復原mysql> select * from test_1;+------+----------+| id   | username |+------+----------+|    1 | test     ||    2 | bob      ||    3 | allen    ||    4 | aron     |+------+----------+4 rows in set (0.00 sec)

View Code

3. MySQL交易隔離等級

SQL標準定義了四種隔離等級,指定了事務中哪些資料改變其他事務可見,哪些資料改變其他事務不可見,低層級的隔離等級可以支援更高的並發處理,同時佔用的系統資源更少。

查看MySQL隔離等級:

SHOW VARIABLES LIKE ‘tx_isolation‘;

交易隔離等級可以使用以下語句設定:

#未提交讀SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;#提交讀SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;#可重複讀SET GLOBAL TRANSACTION ISOLATION LEVEL  REPEATABLE READ;  #可序列化SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

3.1 READ-UNCOMMITTED(讀取未提交內容)

在該隔離等級,所有事務都可以看到其他未提交事務的執行結果。因為其效能也不比其他層級高很多,因此此隔離等級實際應用中很少使用,讀取未提交的資料被稱為髒讀(Dirty Read)。

髒讀樣本如下:

3.2 READ-COMMITTED(讀取提交內容)

這是大多數資料庫系統的預設隔離等級,但並不是MySQL預設的隔離等級。其滿足了隔離的簡單定義,一個事務從開始到提交前所做的任何改變都是不可見的,事務只能看見已經提交事務所做的改變。這種隔離等級也支援所謂的不可重複讀取(Nonrepeatable Read),因為同一事務的其他執行個體在該執行個體處理期間可能會有新的資料提交導致資料改變,所以同一查詢可能返回不同的結果。此層級導致的不可重複讀取樣本如下所示:

3.3 REPEATABLE -READ(可重讀)

這是MySQL的預設交易隔離等級,能確保同一事務的多個執行個體在並發讀取資料時,會看到同樣的資料行。理論上會導致幻讀(Phantom Read)。例如第一個事務對一個表中 的資料進行了修改,這種修改涉及表中的全部資料行。同時第二個事務也修改這個表中的資料,這種修改是向表中插入一行新資料。那麼,以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行。InnoDB和Falcon儲存引擎通過多版本並發控制(Multi_Version Concurrency Control,MVCC)機制解決了該問題。

InnoDB儲存引擎MVCC機制:

3.3 SERIALIZABLE(可序列化)

mysql的事務