MySQL 事務(wù)概念與用法深入詳解

本文實(shí)例講述了MySQL 事務(wù)概念與用法。分享給大家供大家參考，具體如下：

事務(wù)的概念

MySQL事務(wù)是一個(gè)或者多個(gè)的數(shù)據(jù)庫操作，要么全部執(zhí)行成功，要么全部失敗回滾。

事務(wù)是通過事務(wù)日志來實(shí)現(xiàn)的，事務(wù)日志包括：redo log和undo log。

事務(wù)的狀態(tài)

活動(dòng)的（active）

事務(wù)對(duì)應(yīng)的數(shù)據(jù)庫操作正在執(zhí)行過程中時(shí)，我們就說該事務(wù)處在活動(dòng)的狀態(tài)。

部分提交的（partially committed）

當(dāng)事務(wù)中的最后一個(gè)操作執(zhí)行完成，但由于操作都在內(nèi)存中執(zhí)行，所造成的影響并沒有刷新到磁盤時(shí)，我們就說該事務(wù)處在部分提交的狀態(tài)。

失敗的（failed）

當(dāng)事務(wù)處在活動(dòng)的或者部分提交的狀態(tài)時(shí)，可能遇到了某些錯(cuò)誤（數(shù)據(jù)庫自身的錯(cuò)誤、操作系統(tǒng)錯(cuò)誤或者直接斷電等）而無法繼續(xù)執(zhí)行，或者人為的停止當(dāng)前事務(wù)的執(zhí)行，我們就說該事務(wù)處在失敗的狀態(tài)。

中止的（aborted）

如果事務(wù)執(zhí)行了半截而變?yōu)槭〉臓顟B(tài)，撤銷失敗事務(wù)對(duì)當(dāng)前數(shù)據(jù)庫造成的影響，我們把這個(gè)撤銷的過程稱之為回滾。

當(dāng)回滾操作執(zhí)行完畢時(shí)，也就是數(shù)據(jù)庫恢復(fù)到了執(zhí)行事務(wù)之前的狀態(tài)，我們就說該事務(wù)處在了中止的狀態(tài)。

提交的（committed）

當(dāng)一個(gè)處在部分提交的狀態(tài)的事務(wù)將修改過的數(shù)據(jù)都同步到磁盤上之后，我們就可以說該事務(wù)處在了提交的狀態(tài)。

從圖中大家也可以看出了，只有當(dāng)事務(wù)處于提交的或者中止的狀態(tài)時(shí)，一個(gè)事務(wù)的生命周期才算是結(jié)束了。對(duì)于已經(jīng)提交的事務(wù)來說，該事務(wù)對(duì)數(shù)據(jù)庫所做的修改將永久生效，對(duì)于處于中止?fàn)顟B(tài)的事務(wù)，該事務(wù)對(duì)數(shù)據(jù)庫所做的所有修改都會(huì)被回滾到?jīng)]執(zhí)行該事務(wù)之前的狀態(tài)。

事務(wù)的作用

事務(wù)主要是為了保證復(fù)雜數(shù)據(jù)庫操作數(shù)據(jù)的一致性，尤其是在并發(fā)訪問數(shù)據(jù)時(shí)。
MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。

事務(wù)的特點(diǎn)

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

事務(wù)的數(shù)據(jù)操作，要么全部執(zhí)行成功，要么全部失敗回滾到執(zhí)行之前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣。

隔離性（Isolation，又稱獨(dú)立性）

多個(gè)事務(wù)之間是相互隔離，互不影響的。數(shù)據(jù)庫允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。

四種隔離狀態(tài)：
1. 讀未提交（Read uncommitted）
2. 讀提交（Read committed）
3. 可重復(fù)讀（Repeatable read）
4. 串行化（Serializable）

一致性（Consistency）

在事務(wù)操作之前和之后，數(shù)據(jù)都是保持一個(gè)相同的狀態(tài)，數(shù)據(jù)庫的完整性沒有被破壞。
原子性和隔離性，對(duì)一致性有著至關(guān)重要的影響。

持久性（Durability）

當(dāng)事務(wù)操作完成后，數(shù)據(jù)會(huì)被刷新到磁盤永久保存，即便是系統(tǒng)故障也不會(huì)丟失。

事務(wù)的語法

數(shù)據(jù)

#創(chuàng)建數(shù)據(jù)表：
create table account(
  -> id int(10) auto_increment,
  -> name varchar(30),
  -> balance int(10),
  -> primary key (id));
#插入數(shù)據(jù)：
insert into account(name,balance) values('老王媳婦',100),('老王',10);

mysql> select * from account;
+----+--------------+---------+
| id | name     | balance |
+----+--------------+---------+
| 1 | 老王媳婦 |   100  |
| 2 | 老王    |   10 |
+----+--------------+---------+

老王媳婦有100元存在自己的微信賬戶上了，專門給老王每個(gè)月發(fā)零花錢用的，表現(xiàn)好給的多，老王也有自己的小金庫，目前已經(jīng)攢到了10元零花錢了，哈哈哈。

begin

事務(wù)啟動(dòng)方式1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> 事務(wù)操作SQL......

start transaction [修飾符]

修飾符：
1. read only //只讀
2. read write //讀寫 默認(rèn)
3. WITH CONSISTENT SNAPSHOT //一致性讀

事務(wù)啟動(dòng)方式2

mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)
mysql> 事務(wù)操作SQL......

#如設(shè)置read only后，對(duì)數(shù)據(jù)進(jìn)行修改會(huì)報(bào)錯(cuò)：

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

事務(wù)執(zhí)行提交，提交成功則刷新到磁盤

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

rollback

事務(wù)執(zhí)行回滾，回到事務(wù)操作之前的狀態(tài)。

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

這里需要強(qiáng)調(diào)一下，ROLLBACK語句是我們程序員手動(dòng)的去回滾事務(wù)時(shí)才去使用的，如果事務(wù)在執(zhí)行過程中遇到了某些錯(cuò)誤而無法繼續(xù)執(zhí)行的話，事務(wù)自身會(huì)自動(dòng)的回滾。

完整的提交例子

1月份，老王的表現(xiàn)很不錯(cuò)，老王媳婦給他獎(jiǎng)勵(lì)20元零花錢。

執(zhí)行步驟：
1. 從老王媳婦賬戶讀取數(shù)據(jù)
2. 從老王媳婦賬戶上減掉20元
3. 從老王賬戶讀取數(shù)據(jù)
4. 給老王賬戶增加20元
5. 執(zhí)行提交成功
6. 此時(shí)老王媳婦賬戶只有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月份，老王本來表現(xiàn)得很棒，堅(jiān)持干家務(wù)活和遛狗，老王媳婦要給他25元的零花錢，可老王不經(jīng)夸啊，老王媳婦正在給老王轉(zhuǎn)零花錢時(shí)，突然看到桌子上老王手機(jī)收到一條小女生發(fā)來的微信：親愛的王哥....，老王媳婦特別生氣，一怒之下撤回了轉(zhuǎn)賬，取消這個(gè)月的零花錢。

執(zhí)行步驟：
1. 從老王媳婦賬戶讀取數(shù)據(jù)
2. 從老王媳婦賬戶上減掉25元
3. 從老王賬戶讀取數(shù)據(jù)
4. 給老王賬戶增加25元
5. 此時(shí)老王媳婦撤回之前的操作
6. 此時(shí)，老王和老王媳婦的賬戶余額還是保持操作之前的數(shù)目

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=balance-25 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update account set balance=balance+25 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

#賬戶余額：
mysql> select * from account;
+----+--------------+---------+
| id | name     | balance |
+----+--------------+---------+
| 1 | 老王媳婦 |   80 |
| 2 | 老王    |   30 |
+----+--------------+---------+

事務(wù)支持的存儲(chǔ)引擎

1. InnoDB
2. NDB

不支持的存儲(chǔ)引擎，比如在MyISAM上操作事務(wù)，事務(wù)不會(huì)生效，SQL語句直接自動(dòng)執(zhí)行提交，所以回滾對(duì)于不支持事務(wù)的存儲(chǔ)引擎是無效的。

create table tb1(
  -> id int(10) auto_increment,
  -> name varchar(30),
  -> primary key (id)
  -> )engine=myisam charset=utf8mb4;
  
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1(name) values('Tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)

mysql> rollback;//回滾無效
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)

事務(wù)的設(shè)置與查看

#查看事務(wù)開啟情況：
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+

默認(rèn)是事務(wù)自動(dòng)提交的，每執(zhí)行一條SQL就自動(dòng)提交。

此時(shí)需要操作事務(wù)，則需要顯式開啟（begin or start transaction）和提交（commit）或回滾（rollback）。

如設(shè)置成OFF，則需要執(zhí)行提交（commit）或回滾（rollback）操作時(shí)才會(huì)真正執(zhí)行事務(wù)。

關(guān)閉自動(dòng)提交方式

第一種

顯式的的使用START TRANSACTION或者BEGIN語句開啟一個(gè)事務(wù)。

第二種

把系統(tǒng)變量autocommit的值設(shè)置為OFF。

SET autocommit = OFF;

隱式提交情況

當(dāng)我們使用START TRANSACTION或者BEGIN語句開啟了一個(gè)事務(wù)，或者把系統(tǒng)變量autocommit的值設(shè)置為OFF時(shí)，事務(wù)就不會(huì)進(jìn)行自動(dòng)提交，但是如果我們輸入了某些語句之后就會(huì)悄悄的提交掉，就像我們輸入了COMMIT語句了一樣，這種因?yàn)槟承┨厥獾恼Z句而導(dǎo)致事務(wù)提交的情況稱為隱式提交

定義或修改數(shù)據(jù)庫對(duì)象的數(shù)據(jù)定義語言（Data definition language，縮寫為：DDL）

所謂的數(shù)據(jù)庫對(duì)象，指的就是數(shù)據(jù)庫、表、視圖、存儲(chǔ)過程等等這些東西。當(dāng)我們使用CREATE、ALTER、DROP等語句去修改這些所謂的數(shù)據(jù)庫對(duì)象時(shí)，就會(huì)隱式的提交前邊語句所屬于的事務(wù)。

BEGIN;
SELECT ... # 事務(wù)中的一條語句
UPDATE ... # 事務(wù)中的一條語句
... # 事務(wù)中的其它語句
CREATE TABLE ... # 此語句會(huì)隱式的提交前邊語句所屬于的事務(wù)

隱式使用或修改mysql數(shù)據(jù)庫中的表

隱式使用或修改mysql數(shù)據(jù)庫中的表。

當(dāng)我們使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等語句時(shí)也會(huì)隱式的提交前邊語句所屬于的事務(wù)。

事務(wù)控制或關(guān)于鎖定的語句

事務(wù)控制或關(guān)于鎖定的語句。

當(dāng)我們?cè)谝粋€(gè)事務(wù)還沒提交或者回滾時(shí)就又使用START TRANSACTION或者BEGIN語句開啟了另一個(gè)事務(wù)時(shí)，會(huì)隱式的提交上一個(gè)事務(wù)。

BEGIN;
SELECT ... # 事務(wù)中的一條語句
UPDATE ... # 事務(wù)中的一條語句
... # 事務(wù)中的其它語句
BEGIN; # 此語句會(huì)隱式的提交前邊語句所屬于的事務(wù)

或者當(dāng)前的autocommit系統(tǒng)變量的值為OFF，我們手動(dòng)把它調(diào)為ON時(shí)，也會(huì)隱式的提交前邊語句所屬的事務(wù)。

或者使用LOCK TABLES、UNLOCK TABLES等關(guān)于鎖定的語句也會(huì)隱式的提交前邊語句所屬的事務(wù)。

加載數(shù)據(jù)的語句

比如我們使用LOAD DATA語句來批量往數(shù)據(jù)庫中導(dǎo)入數(shù)據(jù)時(shí)，也會(huì)隱式的提交前邊語句所屬的事務(wù)。

關(guān)于MySQL復(fù)制的一些語句

使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等語句時(shí)也會(huì)隱式的提交前邊語句所屬的事務(wù)。

其它的一些語句

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

事務(wù)的保存點(diǎn)

概念

在事務(wù)對(duì)應(yīng)的數(shù)據(jù)庫語句中打幾個(gè)點(diǎn)，我們?cè)谡{(diào)用ROLLBACK語句時(shí)可以指定會(huì)滾到哪個(gè)點(diǎn)，而不是回到最初的原點(diǎn)。

有了事務(wù)的保存點(diǎn)，我們?cè)谶M(jìn)行復(fù)雜的事務(wù)操作時(shí)，我們不用擔(dān)心一出錯(cuò)直接回滾到最初狀態(tài)，就如一夜回到解放前。

使用語法

1. SAVEPOINT 保存點(diǎn)名稱;//標(biāo)記保存點(diǎn)
2. ROLLBACK TO [SAVEPOINT] 保存點(diǎn)名稱;//回滾到某一個(gè)保存點(diǎn)
3. RELEASE SAVEPOINT 保存點(diǎn)名稱;//刪除

mysql> begin;
Query OK, 0 rows affected (0.00 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> savepoint action1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from account;
+----+--------------+---------+
| id | name     | balance |
+----+--------------+---------+
| 1 | 老王媳婦 |   60 |
| 2 | 老王    |   30 |
+----+--------------+---------+

mysql> update account set balance=balance+30 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> rollback to action1;//回滾到action1保存點(diǎn)
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------------+---------+
| id | name     | balance |
+----+--------------+---------+
| 1 | 老王媳婦 |   60 |
| 2 | 老王    |   30 |
+----+--------------+---------+

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL事務(wù)操作技巧匯總》、《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL存儲(chǔ)過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫計(jì)有所幫助。