SQL 事務(wù)

事務(wù)是在數(shù)據(jù)庫上按照一定的邏輯順序執(zhí)行的任務(wù)序列，既可以由用戶手動執(zhí)行，也可以由某種數(shù)據(jù)庫程序自動執(zhí)行。

事務(wù)實際上就是對數(shù)據(jù)庫的一個或者多個更改。當(dāng)你在某張表上創(chuàng)建更新或者刪除記錄的時，你就已經(jīng)在使用事務(wù)了。控制事務(wù)以保證數(shù)據(jù)完整性，并對數(shù)據(jù)庫錯誤做出處理，對數(shù)據(jù)庫來說非常重要。

實踐中，通常會將很多 SQL 查詢組合在一起，并將其作為某個事務(wù)一部分來執(zhí)行。

事務(wù)的屬性：

事務(wù)具有以下四個標(biāo)準(zhǔn)屬性，通常用縮略詞 ACID 來表示：

• 原子性：保證任務(wù)中的所有操作都執(zhí)行完畢；否則，事務(wù)會在出現(xiàn)錯誤時終止，并回滾之前所有操作到原始狀態(tài)。
• 一致性：如果事務(wù)成功執(zhí)行，則數(shù)據(jù)庫的狀態(tài)得到了進(jìn)行了正確的轉(zhuǎn)變。
• 隔離性：保證不同的事務(wù)相互獨立、透明地執(zhí)行。
• 持久性：即使出現(xiàn)系統(tǒng)故障，之前成功執(zhí)行的事務(wù)的結(jié)果也會持久存在。

事務(wù)控制：

有四個命令用于控制事務(wù)：

• COMMIT：提交更改；
• ROLLBACK：回滾更改；
• SAVEPOINT：在事務(wù)內(nèi)部創(chuàng)建一系列可以 ROLLBACK 的還原點；
• SET TRANSACTION：命名事務(wù)；

COMMIT 命令：

COMMIT 命令用于保存事務(wù)對數(shù)據(jù)庫所做的更改。

COMMIT 命令會將自上次 COMMIT 命令或者 ROLLBACK 命令執(zhí)行以來所有的事務(wù)都保存到數(shù)據(jù)庫中。

COMMIT 命令的語法如下所示：

COMMIT;

示例：

考慮 CUSTOMERS 表，表中的記錄如下所示：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

下面的示例將會刪除表中 age=25 的記錄，然后將更改提交（COMMIT）到數(shù)據(jù)庫中。

SQL> DELETE FROM CUSTOMERS
     WHERE AGE = 25;
SQL> COMMIT;

上述語句將會從表中刪除兩行記錄，再執(zhí)行 SELECT 語句將會得到如下結(jié)果：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

ROLLBACK 命令：

ROLLBACK 命令用于撤銷尚未保存到數(shù)據(jù)庫中的事務(wù)。

ROLLBACK 命令只能撤銷自上次 COMMIT 命令或者 ROLLBACK 命令執(zhí)行以來的事務(wù)。

ROLLBACK 命令的語法如下所示：

ROLLBACK;

示例：

考慮 CUSTOMERS 表，表中的記錄如下所示：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

下面的示例將會從表中刪除所有 age=25 的記錄，然后回滾（ROLLBACK）對數(shù)據(jù)庫所做的更改。

SQL> DELETE FROM CUSTOMERS
     WHERE AGE = 25;
SQL> ROLLBACK;

結(jié)果是刪除操作并不會對數(shù)據(jù)庫產(chǎn)生影響。現(xiàn)在，執(zhí)行 SELECT 語句將會得到如下結(jié)果：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SAVEPOINT 命令：

SAVEPOINT 是事務(wù)中的一個狀態(tài)點，使得我們可以將事務(wù)回滾至特定的點，而不是將整個事務(wù)都撤銷。

SAVEPOINT 命令的記錄如下所示：

SAVEPOINT SAVEPOINT_NAME;

該命令只能在事務(wù)語句之間創(chuàng)建保存點（SAVEPOINT）。ROLLBACK 命令可以用于撤銷一系列的事務(wù)。

回滾至某一保存點的語法如下所示：

ROLLBACK TO SAVEPOINT_NAME;

下面的示例中，你計劃從 CUSTOMERS 表中刪除三條不同的記錄，并在每次刪除之前創(chuàng)建一個保存點（SAVEPOINT），從而使得你可以在任何任何時候回滾到任意的保存點，以恢復(fù)數(shù)據(jù)至其原始狀態(tài)。

示例：

考慮 CUSTOMERS 表，表中的記錄如下所示：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

操作序列如下所示：

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

現(xiàn)在，三次刪除操作已經(jīng)生效了，如果此時你改變主意決定回滾至名字為 SP2 的保存點，由于 SP2 于第一次刪除操作之后創(chuàng)建，所以后兩次刪除操作將會被撤銷。

SQL> ROLLBACK TO SP2;
Rollback complete.

注意，由于你將數(shù)據(jù)庫回滾至 SP2，所以只有第一次刪除真正起效了：

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

RELEASE SAVEPOINT 命令：

RELEASE SAVEPOINT 命令用于刪除先前創(chuàng)建的保存點。

RELEASE SAVEPOINT 的語法如下所示：

RELEASE SAVEPOINT SAVEPOINT_NAME;

保存點一旦被釋放，你就不能夠再用 ROLLBACK 命令來撤銷該保存點之后的事務(wù)了。

SET TRANSACTION 命令：

SET TRANSACTION 命令可以用來初始化數(shù)據(jù)庫事務(wù)，指定隨后的事務(wù)的各種特征。

例如，你可以將某個事務(wù)指定為只讀或者讀寫。

SET TRANSACTION 命令的語法如下所示：

SET TRANSACTION [ READ WRITE | READ ONLY ];