如何使用 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
  • 前言 本文將以 C# 語言來實現一個簡單的布隆過濾器,為簡化說明,設計得很簡單,僅供學習使用。 感謝@時總百忙之中的指導。 布隆過濾器簡介 布隆過濾器(Bloom filter)是一種特殊的 Hash Table,能夠以較小的存儲空間較快地判斷出數據是否存在。常用於允許一定誤判率的數據過濾及防止緩存 ...
  • 目錄 一.簡介 二.效果演示 三.源碼下載 四.猜你喜歡 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 基礎 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 轉場 零基礎 O ...
  • 「簡單有價值的事情長期堅持做」 這是成功最簡單,但也最難學的秘訣。不經過訓練,人很難意識到時間複利的威力。 仙劍奇俠傳的「十里坡劍神」和金庸群俠傳的「十級野球拳」,就是簡單的事情持之以恆反覆做,最後就有巨大的威力 唐家三少成為網文收入第一,最重要的一步是十四年從未斷日更 這樣的案例很多,一開始可能成 ...
  • 迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。 手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“我愛加班”。 面試開始,直入正題。 面試官: 看你簡歷上面寫著精通MySQL,我先問你事務的特性是什麼? 老生常談,這個還有誰不會背的嗎? 我: ...
  • 基礎知識 python是一門腳本語言,它是解釋執行的。 python使用縮進做為語法,而且python2環境下同一個py文件中不能同時存在tab和空格縮進,否則會出錯,建議在IDE中顯示縮進符。 python在聲明變數時不寫數據類型,可以type(xx)來獲取欄位的類型,然後可以int(),list ...
  • 為什麼要多線程下載 俗話說要以終為始,那麼我們首先要明確多線程下載的目標是什麼,不外乎是為了更快的下載文件。那麼問題來了,多線程下載文件相比於單線程是不是更快? 對於這個問題可以看下圖。 橫坐標是線程數,縱坐標是使用對應線程數下載對應文件時花費的時間,藍橙綠代表下載文件的大小,每個線程下載對應文件2 ...
  • 詳細講解python爬蟲代碼,爬微博搜索結果的博文數據。 爬取欄位: 頁碼、微博id、微博bid、微博作者、發佈時間、微博內容、轉發數、評論數、點贊數。 爬蟲技術: 1、requests 發送請求 2、datetime 時間格式轉換 3、jsonpath 快速解析json數據 4、re 正則表達式提... ...
  • 背景: 一般我們可以用HashMap做本地緩存,但是HashMap功能比較弱,不支持Key過期,不支持數據範圍查找等。故在此實現了一個簡易的本地緩存,取名叫fastmap。 功能: 1.支持數據過期 2.支持等值查找 3.支持範圍查找 4.支持key排序 實現思路: 1.等值查找採用HashMap2 ...
  • 目錄 一.簡介 二.效果演示 三.源碼下載 四.猜你喜歡 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 基礎 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 轉場 零基礎 O ...
  • 本章是系列文章的第八章,用著色演算法進行寄存器的分配過程。 本文中的所有內容來自學習DCC888的學習筆記或者自己理解的整理,如需轉載請註明出處。周榮華@燧原科技 寄存器分配 寄存器分配是為程式處理的值找到存儲位置的問題 這些值可以存放到寄存器,也可以存放在記憶體中 寄存器更快,但數量有限 記憶體很多,但 ...