該如何使用MySQL事務

2024-07-24 12:36:14

字體：大中小

來源：轉載

供稿：網友

　　這篇文章主要為大家展示了如何使用MySQL事務，內容簡而易懂，希望大家可以學習一下，學習完之后肯定會有收獲的，下面讓小編帶大家一起來看看吧。

　　事務的概念

　　MySQL事務是一個或者多個的數據庫操作，要么全部執行成功，要么全部失敗回滾。

　　事務是通過事務日志來實現的，事務日志包括：redo log和undo log。

　　事務的狀態

　　活動的（active）

　　事務對應的數據庫操作正在執行過程中時，我們就說該事務處在活動的狀態。

　　部分提交的（partially committed）

　　當事務中的最后一個操作執行完成，但由于操作都在內存中執行，所造成的影響并沒有刷新到磁盤時，我們就說該事務處在部分提交的狀態。

　　失敗的（failed）

　　當事務處在活動的或者部分提交的狀態時，可能遇到了某些錯誤（數據庫自身的錯誤、操作系統錯誤或者直接斷電等）而無法繼續執行，或者人為的停止當前事務的執行，我們就說該事務處在失敗的狀態。

　　中止的（aborted）

　　如果事務執行了半截而變為失敗的狀態，撤銷失敗事務對當前數據庫造成的影響，我們把這個撤銷的過程稱之為回滾。

　　當回滾操作執行完畢時，也就是數據庫恢復到了執行事務之前的狀態，我們就說該事務處在了中止的狀態。

　　提交的（committed）

　　當一個處在部分提交的狀態的事務將修改過的數據都同步到磁盤上之后，我們就可以說該事務處在了提交的狀態。

　　如何使用MySQL事務

　　從圖中大家也可以看出了，只有當事務處于提交的或者中止的狀態時，一個事務的生命周期才算是結束了。對于已經提交的事務來說，該事務對數據庫所做的修改將永久生效，對于處于中止狀態的事務，該事務對數據庫所做的所有修改都會被回滾到沒執行該事務之前的狀態。

　　事務的作用

　　事務主要是為了保證復雜數據庫操作數據的一致性，尤其是在并發訪問數據時。

　　MySQL 事務主要用于處理操作量大，復雜度高的數據。

　　事務的特點

　　原子性（Atomicity，又稱不可分割性）

　　事務的數據操作，要么全部執行成功，要么全部失敗回滾到執行之前的狀態，就像這個事務從來沒有執行過一樣。

　　隔離性（Isolation，又稱獨立性）

　　多個事務之間是相互隔離，互不影響的。數據庫允許多個并發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致。

　　四種隔離狀態：

　　1. 讀未提交（Read uncommitted）

　　2. 讀提交（Read committed）

　　3. 可重復讀（Repeatable read）

　　4. 串行化（Serializable）

　　一致性（Consistency）

　　在事務操作之前和之后，數據都是保持一個相同的狀態，數據庫的完整性沒有被破壞。

　　原子性和隔離性，對一致性有著至關重要的影響。

　　持久性（Durability）

　　當事務操作完成后，數據會被刷新到磁盤永久保存，即便是系統故障也不會丟失。

　　事務的語法

　　數據

　　#創建數據表：

　　create table account(

　　 -> id int(10) auto_increment,

　　 -> name varchar(30),

　　 -> balance int(10),

　　 -> primary key (id));

　　#插入數據：

　　insert into account(name,balance) values('老王媳婦',100),('老王',10);

　　mysql> select * from account;

　　+----+--------------+---------+

　　| id | name     | balance |

　　+----+--------------+---------+

　　| 1 | 老王媳婦 |   100 |

　　| 2 | 老王    |   10 |

　　+----+--------------+---------+

　　老王媳婦有100元存在自己的微信賬戶上了，專門給老王每個月發零花錢用的，表現好給的多，老王也有自己的小金庫，目前已經攢到了10元零花錢了，哈哈哈。

　　begin

　　事務啟動方式1

　　mysql> begin;

　　Query OK, 0 rows affected (0.00 sec)

　　mysql> 事務操作SQL......

　　start transaction [修飾符]

　　修飾符：

　　1. read only //只讀

　　2. read write //讀寫默認

　　3. WITH CONSISTENT SNAPSHOT //一致性讀

　　事務啟動方式2

　　mysql> start transaction read only;

　　Query OK, 0 rows affected (0.00 sec)

　　mysql> 事務操作SQL......

　　#如設置read only后，對數據進行修改會報錯：

　　mysql> start transaction read only;

　　Query OK, 0 rows affected (0.00 sec)

　　mysql> update account set balance=banlance+30 where id = 2;

　　ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

　　commit

　　事務執行提交，提交成功則刷新到磁盤

　　mysql> commit;

　　Query OK, 0 rows affected (0.00 sec)

　　rollback

　　事務執行回滾，回到事務操作之前的狀態。

　　mysql> rollback;

　　Query OK, 0 rows affected (0.00 sec)

　　這里需要強調一下，ROLLBACK語句是我們程序員手動的去回滾事務時才去使用的，如果事務在執行過程中遇到了某些錯誤而無法繼續執行的話，事務自身會自動的回滾。

　　完整的提交例子

　　1月份，老王的表現很不錯，老王媳婦給他獎勵20元零花錢。

　　執行步驟：

　　1. 從老王媳婦賬戶讀取數據

　　2. 從老王媳婦賬戶上減掉20元

　　3. 從老王賬戶讀取數據

　　4. 給老王賬戶增加20元

　　5. 執行提交成功

　　6. 此時老王媳婦賬戶只有80元啦，而老王賬戶有30元啦，老王高興不得了咯

　　mysql> begin;

　　Query OK, 0 rows affected (0.01 sec)

　　mysql> update account set balance=balance-20 where id = 1;

　　Query OK, 1 row affected (0.00 sec)

　　Rows matched: 1 Changed: 1 Warnings: 0

　　mysql> update account set balance=balance+20 where id = 2;

　　Query OK, 1 row affected (0.00 sec)

　　Rows matched: 1 Changed: 1 Warnings: 0

　　mysql> commit;

　　Query OK, 0 rows affected (0.01 sec)

　　#賬戶余額：

　　mysql> select * from account;

　　+----+--------------+---------+

　　| id | name | balance |

　　+----+--------------+---------+

　　| 1 | 老王媳婦 | 80 |

　　| 2 | 老王 | 30 |

　　+----+--------------+---------+

　　完整的回滾例子

　　2月份，老王本來表現得很棒，堅持干家務活和遛狗，老王媳婦要給他25元的零花錢，可老王不經夸啊，老王媳婦正在給老王轉零花錢時，突然看到桌子上老王手機收到一條小女生發來的微信：親愛的王哥....，老王媳婦特別生氣，一怒之下撤回了轉賬，取消這個月的零花錢。

　　執行步驟：

　　1. 從老王媳婦賬戶讀取數據

　　2. 從老王媳婦賬戶上減掉25元

　　3. 從老王賬戶讀取數據

　　4. 給老王賬戶增加25元

　　5. 此時老王媳婦撤回之前的操作

　　6. 此時，老王和老王媳婦的賬戶余額還是保持操作之前的數目

　　關閉自動提交方式

　　第一種

　　顯式的的使用START TRANSACTION或者BEGIN語句開啟一個事務。

　　第二種

　　把系統變量autocommit的值設置為OFF。

　　SET autocommit = OFF;

　　隱式提交情況

　　當我們使用START TRANSACTION或者BEGIN語句開啟了一個事務，或者把系統變量autocommit的值設置為OFF時，事務就不會進行自動提交，但是如果我們輸入了某些語句之后就會悄悄的提交掉，就像我們輸入了COMMIT語句了一樣，這種因為某些特殊的語句而導致事務提交的情況稱為隱式提交

　　定義或修改數據庫對象的數據定義語言（Data definition language，縮寫為：DDL）

　　所謂的數據庫對象，指的就是數據庫、表、視圖、存儲過程等等這些東西。當我們使用CREATE、ALTER、DROP等語句去修改這些所謂的數據庫對象時，就會隱式的提交前邊語句所屬于的事務。

　　... # 事務中的其它語句

　　CREATE TABLE ... # 此語句會隱式的提交前邊語句所屬于的事務

　　當我們使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等語句時也會隱式的提交前邊語句所屬于的事務。

　　當我們在一個事務還沒提交或者回滾時就又使用START TRANSACTION或者BEGIN語句開啟了另一個事務時，會隱式的提交上一個事務。

　　... # 事務中的其它語句

　　BEGIN; # 此語句會隱式的提交前邊語句所屬于的事務

　　或者當前的autocommit系統變量的值為OFF，我們手動把它調為ON時，也會隱式的提交前邊語句所屬的事務。

　　或者使用LOCK TABLES、UNLOCK TABLES等關于鎖定的語句也會隱式的提交前邊語句所屬的事務。

　　加載數據的語句

　　比如我們使用LOAD DATA語句來批量往數據庫中導入數據時，也會隱式的提交前邊語句所屬的事務。

　　關于MySQL復制的一些語句

　　使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等語句時也會隱式的提交前邊語句所屬的事務。

　　其它的一些語句

　　使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等語句也會隱式的提交前邊語句所屬的事務。

（編輯：武林網）

上一篇：MySQL的啟動選項和系統變量的用法介紹

下一篇：MySQL5.7基于無損復制搭建主從詳解