如何使用 SQL COMMIT 和 ROLLBACK 語句管理事務處理

来源:https://www.cnblogs.com/vin-c/archive/2022/05/19/16288225.html
-Advertisement-
Play Games

本文介紹什麼是 SQL 事務處理,如何利用 COMMIT 和 ROLLBACK 語句對何時寫數據、何時撤銷進行明確的管理;還學習瞭如何使用保留點,更好地控制回退操作。 一、事務處理 使用事務處理(transaction processing),通過確保成批的 SQL 操作要麼完全執行,要麼完全不執行 ...


目錄

本文介紹什麼是 SQL 事務處理,如何利用 COMMITROLLBACK 語句對何時寫數據、何時撤銷進行明確的管理;還學習瞭如何使用保留點,更好地控制回退操作。

一、事務處理

使用事務處理(transaction processing),通過確保成批的 SQL 操作要麼完全執行,要麼完全不執行,來維護資料庫的完整性。

正如 如何使用 SQL INNER JOIN 聯結兩個或多個表 所述,關係資料庫把數據存儲在多個表中,使數據更容易操縱、維護和重用。

不用深究如何以及為什麼進行關係資料庫設計,在某種程度上說,設計良好的資料庫模式都是關聯的。

前面使用的 Orders 表就是一個很好的例子。訂單存儲在 OrdersOrderItems 兩個表中:Orders 存儲實際的訂單,OrderItems 存儲訂購的各項物品。

這兩個表使用稱為主鍵(參閱 學習 SQL 之前需要瞭解的基礎知識)的唯一 ID 互相關聯,又與包含客戶和產品信息的其他表相關聯。

給系統添加訂單的過程如下:

(1) 檢查資料庫中是否存在相應的顧客,如果不存在,添加他;

(2) 檢索顧客的 ID;

(3) 在 Orders 表添加一行,它與顧客 ID 相關聯;

(4) 檢索 Orders 表中賦予的新訂單 ID;

(5) 為訂購的每個物品在 OrderItems 表中添加一行,通過檢索出來的 ID 把它與 Orders 表關聯(並且通過產品 ID 與 Products 表關聯)。

現在假設由於某種資料庫故障(如超出磁碟空間、安全限制、表鎖等),這個過程無法完成。資料庫中的數據會出現什麼情況?

如果故障發生在添加顧客之後,添加 Orders 表之前,則不會有什麼問題。某些顧客沒有訂單是完全合法的。

重新執行此過程時,所插入的顧客記錄將被檢索和使用。可以有效地從出故障的地方開始執行此過程。

但是,如果故障發生在插入 Orders 行之後,添加 OrderItems 行之前,怎麼辦?現在,資料庫中有一個空訂單。

更糟的是,如果系統在添加 OrderItems 行之時出現故障,怎麼辦?結果是資料庫中存在不完整的訂單,而你還不知道。

如何解決這種問題?這就需要使用事務處理了。

事務處理是一種機制,用來管理必須成批執行的 SQL 操作,保證資料庫不包含不完整的操作結果。

利用事務處理,可以保證一組操作不會中途停止,它們要麼完全執行,要麼完全不執行(除非明確指示)。

如果沒有錯誤發生,整組語句提交給(寫到)資料庫表;如果發生錯誤,則進行回退(撤銷),將資料庫恢復到某個已知且安全的狀態。

再看這個例子,這次我們說明這一過程是如何工作的:

(1) 檢查資料庫中是否存在相應的顧客,如果不存在,添加他;

(2) 提交顧客信息;

(3) 檢索顧客的 ID;

(4) 在 Orders 表中添加一行;

(5) 如果向 Orders 表添加行時出現故障,回退;

(6) 檢索 Orders 表中賦予的新訂單 ID;

(7) 對於訂購的每項物品,添加新行到 OrderItems 表;

(8) 如果向 OrderItems 添加行時出現故障,回退所有添加的 OrderItems 行和 Orders 行。

在使用事務處理時,有幾個反覆出現的關鍵詞。下麵是關於事務處理需要知道的幾個術語:

  • 事務(transaction)指一組 SQL 語句;
  • 回退(rollback)指撤銷指定 SQL 語句的過程;
  • 提交(commit)指將未存儲的 SQL 語句結果寫入資料庫表;
  • 保留點(savepoint)指事務處理中設置的臨時占位符(placeholder),可以對它發佈回退(與回退整個事務處理不同)。

提示:可以回退哪些語句?

事務處理用來管理 INSERTUPDATEDELETE 語句。

不能回退 SELECT 語句(回退 SELECT 語句也沒有必要),也不能回退 CREATEDROP 操作。

事務處理中可以使用這些語句,但進行回退時,這些操作也不撤銷。

二、控制事務處理

我們已經知道了什麼是事務處理,下麵討論管理事務中涉及的問題。

註意:事務處理實現的差異

不同 DBMS 用來實現事務處理的語法有所不同。在使用事務處理時請參閱相應的 DBMS 文檔。

管理事務的關鍵在於將 SQL 語句組分解為邏輯塊,並明確規定數據何時應該回退,何時不應該回退。

有的 DBMS 要求明確標識事務處理塊的開始和結束。如在 SQL Server 中,標識如下(省略號表示實際的代碼):

BEGIN TRANSACTION
...
COMMIT TRANSACTION

在這個例子中,BEGIN TRANSACTIONCOMMIT TRANSACTION 語句之間的 SQL 必須完全執行或者完全不執行。

MariaDB 和 MySQL 中等同的代碼為:

START TRANSACTION
...

Oracle 使用的語法:

SET TRANSACTION
...

PostgreSQL 使用 ANSI SQL 語法:

BEGIN
...

其他 DBMS 採用上述語法的變體。

你會發現,多數實現沒有明確標識事務處理在何處結束。事務一直存在,直到被中斷。通常,COMMIT 用於保存更改,ROLLBACK 用於撤銷,詳述如下。

2.1 使用 ROLLBACK

SQL 的 ROLLBACK 命令用來回退(撤銷)SQL 語句,請看下麵的語句:

DELETE FROM Orders;
ROLLBACK;

在此例子中,執行 DELETE 操作,然後用 ROLLBACK 語句撤銷。

雖然這不是最有用的例子,但它的確能夠說明,在事務處理塊中,DELETE 操作(與 INSERTUPDATE 操作一樣)並不是最終的結果。

2.2 使用 COMMIT

一般的 SQL 語句都是針對資料庫表直接執行和編寫的。這就是所謂的隱式提交(implicit commit),即提交(寫或保存)操作是自動進行的。

在事務處理塊中,提交不會隱式進行。不過,不同 DBMS 的做法有所不同。有的 DBMS 按隱式提交處理事務端,有的則不這樣。

進行明確的提交,使用 COMMIT 語句。下麵是一個 SQL Server 的例子:

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

在這個 SQL Server 例子中,從系統中完全刪除訂單 12345

因為涉及更新兩個資料庫表 OrdersOrderItems,所以使用事務處理塊來保證訂單不被部分刪除。

最後的 COMMIT 語句僅在不出錯時寫出更改。如果第一條 DELETE 起作用,但第二條失敗,則 DELETE 不會提交。

為在 Oracle 中完成相同的工作,可如下進行:

SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;

2.3 使用保留點

使用簡單的 ROLLBACKCOMMIT 語句,就可以寫入或撤銷整個事務。但是,只對簡單的事務才能這樣做,複雜的事務可能需要部分提交或回退。

例如前面描述的添加訂單的過程就是一個事務。如果發生錯誤,只需要返回到添加 Orders 行之前即可。不需要回退到 Customers 表(如果存在的話)。

要支持回退部分事務,必須在事務處理塊中的合適位置放置占位符。這樣,如果需要回退,可以回退到某個占位符。

在 SQL 中,這些占位符稱為保留點。在 MariaDB、MySQL 和 Oracle 中創建占位符,可使用 SAVEPOINT 語句。

SAVEPOINT delete1;

在 SQL Server 中,如下進行:

SAVE TRANSACTION delete1;

每個保留點都要取能夠標識它的唯一名字,以便在回退時,DBMS 知道回退到何處。要回退到本例給出的保留點,在 SQL Server 中可如下進行。

ROLLBACK TRANSACTION delete1;

在 MariaDB、MySQL 和 Oracle 中,如下進行:

ROLLBACK TO delete1;

下麵是一個完整的 SQL Server 例子:

BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES(1000000010, 'Toys Emporium');
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1',1000000010);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item,prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item,prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION

這裡的事務處理塊中包含了 4 條 INSERT 語句。

在第一條 INSERT 語句之後定義了一個保留點,因此,如果後面的任何一個 INSERT 操作失敗,事務處理能夠回退到這裡。

在 SQL Server 中,可檢查一個名為 @@ERROR 的變數,看操作是否成功。(其他 DBMS 使用不同的函數或變數返回此信息。)

如果 @@ERROR 返回一個非 0 的值,表示有錯誤發生,事務處理回退到保留點。如果整個事務處理成功,發佈 COMMIT 以保留數據。

提示:保留點越多越好

可以在 SQL 代碼中設置任意多的保留點,越多越好。為什麼呢?因為保留點越多,你就越能靈活地進行回退。

三、小結

本文介紹了事務是必須完整執行的 SQL 語句塊。

我們學習瞭如何使用 COMMITROLLBACK 語句對何時寫數據、何時撤銷進行明確的管理;還學習瞭如何使用保留點,更好地控制回退操作。

事務處理是個相當重要的主題,一篇文章的內容無法全部涉及。各種 DBMS 對事務處理的實現不同,詳細內容請參考具體的 DBMS 文檔。

原文鏈接:https://www.developerastrid.com/sql/sql-transaction/

(完)


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 為什麼要使用Redwood Redwood是一個全棧web框架,旨在幫助你從副業項目發展到創業。Redwood的特色是一個端到端的開發工作流,它將React、GraphQL、Prisma、TypeScript、Jest和Storybook中最好的部分編織在一起。 RedwoodJS 是集成 、Pri ...
  • Termius是微軟的一款SSH終端工具,它支持多平臺。而且操作界面十分ha好看且簡潔,今天分享給大家❤️ 軟體下載 關註下方公眾號,回覆termius獲取下載地址 軟體功能介紹 Termius Mac破解版是一款非常好用而且漂亮的SSH客戶端,能快速遠程式控制制伺服器,可以定製自己喜歡的主題,支持FT ...
  • 一、概述 Impala 直接針對存儲在 HDFS、HBase或 Amazon Simple Storage Service (S3)中的 Apache Hadoop 數據提供快速的互動式 SQL 查詢。Impala是一個基於Hive、分散式、大規模並行處理(MPP:Massively Paralle ...
  • 軟硬體環境 軟體 版本 操作系統 Redhat 7 及以上版本,麒麟V10 DM 資料庫 DM 8.0 及以上版本 CPU 架構 x86、ARM、龍芯、飛騰等國內外主流 CPU DM-Oracle 環境準備 在DM伺服器中完成Dblink環境準備工作。 配置Oracle oci客戶端 在Oracle ...
  • 導讀: 隨著全球數據量的不斷增長,越來越多的業務需要支撐高併發、高可用、可擴展、以及海量的數據存儲,在這種情況下,適應各種場景的數據存儲技術也不斷的產生和發展。與此同時,各種資料庫之間的同步與轉化的需求也不斷增多,數據集成成為大數據領域的熱門方向,於是SeaTunnel應運而生。SeaTunnel是 ...
  • hive 存儲格式有很多,但常用的一般是 TextFile、ORC、Parquet 格式,在我們單位最多的也是這三種 hive 預設的文件存儲格式是 TextFile。 除 TextFile 外的其他格式的表不能直接從本地文件導入數據,要先導入到 TextFile 格式的表中,再從表中用 inser ...
  • 本文介紹 SQL 所涉及的幾個數據處理特性:約束、索引和觸發器。約束是實施引用完整性的重要部分,索引可改善數據檢索的性能,觸發器可以用來執行運行前後的處理。 一、約束 SQL 已經改進過多個版本,成為非常完善和強大的語言。許多強有力的特性給用戶提供了高級的數據處理技術,如約束。 關聯表和引用完整性已 ...
  • 本文介紹什麼是 SQL 游標,為什麼使用游標,如何使用游標。你使用的 DBMS 可能會提供某種形式的游標,以及這裡沒有提及的功能。更詳細的內容請參閱具體的 DBMS 文檔。 一、游標 SQL 檢索操作返回一組稱為結果集的行,這組返回的行都是與 SQL 語句相匹配的行(零行到多行)。 簡單地使用 SE ...
一周排行
    -Advertisement-
    Play Games
  • 什麼是工廠模式 工廠模式是最常用的設計模式之一,屬於創建型模式。 有點: 解耦,可以把對象的創建和過程分開 減少代碼量,易於維護 什麼時候用? 當一個抽象類有多個實現的時候,需要多次實例化的時候,就要考慮使用工廠模式。 比如:登錄的抽象類ILoginBusiness,它有2個實現,一個用用戶名密碼登 ...
  • 這次iNeuOS升級主要升級圖形渲染引擎和增加豐富的圖元信息,可以很快的方案應用。總共增加41個通用和行業領域的圖元應用,增加2154個圖元信息,現在iNeuOS視圖建模功能模塊總共包括5894個行業圖元信息。現在完全支持製作高保真的工藝流程和大屏展示效果。 ...
  • 效果圖先附上: 首先 這是我是參考 教程:使用 SignalR 2 和 MVC 5 實時聊天 | Microsoft Docs 先附上教程: 在“添加新項 - SignalRChat”中,選擇 InstalledVisual> C#>WebSignalR>,然後選擇 SignalR Hub 類 (v ...
  • 一、前言 項目中之前涉及到胎兒心率圖曲線的繪製,最近項目中還需要添加心電曲線和血樣曲線的繪製功能。今天就來分享一下心電曲線的繪製方式; 二、正文 1、胎兒心率曲線的繪製是通過DrawingVisual來實現的,這裡的心電曲線我也是採用差不多相同的方式來實現的,只是兩者曲線的數據有所區別。心電圖的數據 ...
  • 安裝 Redis # 首先安裝依賴gcc, 後面需要使用make編譯redis yum install gcc -y # 進入 /usr/local/src 目錄, 把源碼下載到這裡 cd /usr/local/src # 下載 redis 7.0.2 的源碼,github被牆,可以使用國內的地址 ...
  • Redis 的定義? 百度百科: Redis(Remote Dictionary Server ),即遠程字典服務,是一個開源的使用ANSI C語言編寫、支持網路、可基於記憶體亦可持久化的日誌型、Key-Value資料庫,並提供多種語言的API。 中文官網: Redis是一個開源(BSD許可),記憶體存 ...
  • 事情的起因是收到了一位網友的請求,他的java課設需要設計實現迷宮相關的程式——如標題概括。 我這邊不方便透露相關信息,就只把任務要求寫出來。 演示視頻指路👉: 基於JavaFX圖形界面的迷宮程式演示_嗶哩嗶哩_bilibili 完整代碼鏈接🔎: 網盤:https://pan.baidu.com ...
  • Python中的字典 Python中的字典是另一種可變容器模型,且可存儲任意類型對象。鍵值使用冒號分割,你可以看成是一串json。 常用方法 獲取字典中的值 dict[key] 如果key不存在會報錯,建議使用dict.get(key),不存在返回None 修改和新建字典值 dict[key]=va ...
  • 迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。 手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“加班使我快樂”。 面試官: 看你簡歷上用過MySQL,問你幾個簡單的問題吧。什麼是聚簇索引和非聚簇索引? 這個問題難不住我啊。來之前我看一下一燈M ...
  • tunm二進位協議在python上的實現 tunm是一種對標JSON的二進位協議, 支持JSON的所有類型的動態組合 支持的數據類型 基本支持的類型 "u8", "i8", "u16", "i16", "u32", "i32", "u64", "i64", "varint", "float", "s ...