26. 使用MySQL之管理事務處理

hisun9發表於2024-11-15
MySql

1. 事務處理

補充：

  • 並非所有引擎都支援事務處理：

    正如第21章所述，MySQL支援幾種基本的資料庫引擎。正如本章所述，並非所有引擎都支援明確的事務處理管理。MyISAM和InnoDB是兩種最常使用的引擎。前者不支援明確的事務處理管理，而後者支援。這就是為什麼本書中使用的樣例表被建立來使用InnoDB而不是更經常使用的MyISAM的原因。如果你的應用中需要事務處理功能，則一定要使用正確的引擎型別。

事務處理（transaction processing）可以用來維護資料庫的完整性，它保證成批的MySQL操作要麼完全執行，要麼完全不執行。

正如第15章所述，關聯式資料庫設計把資料儲存在多個表中，使資料更容易操縱、維護和重用。不用深究如何以及為什麼進行關聯式資料庫設計，在某種程度上說，設計良好的資料庫模式都是關聯的。

前面章中使用的orders表就是一個很好的例子。訂單儲存在orders和orderitems兩個表中：orders儲存實際的訂單，而orderitems儲存訂購的各項物品。這兩個表使用稱為主鍵的唯一ID互相關聯。這兩個表又與包含客戶和產品資訊的其他表相關聯。

給系統新增訂單的過程如下。

(1) 檢查資料庫中是否存在相應的客戶（從customers表查詢），如果不存在，新增他/她。

(2) 檢索客戶的ID。

(3) 新增一行到orders表，把它與客戶ID關聯。

(4) 檢索orders表中賦予的新訂單ID。

(5) 對於訂購的每個物品在orderitems表中新增一行，透過檢索出來的ID把它與orders表關聯（以及透過產品ID與products表關聯）。

現在，假如由於某種資料庫故障（如超出磁碟空間、安全限制、表鎖等）阻止了這個過程的完成。資料庫中的資料會出現什麼情況？

如果故障發生在新增了客戶之後，orders表新增之前，不會有什麼問題。某些客戶沒有訂單是完全合法的。在重新執行此過程時，所插入的客戶記錄將被檢索和使用。可以有效地從出故障的地方開始執行此過程。

但是，如果故障發生在orders行新增之後，orderitems行新增之前，怎麼辦呢？現在，資料庫中有一個空訂單。

更糟的是，如果系統在新增orderitems行之中出現故障。結果是資料庫中存在不完整的訂單，而且你還不知道。

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

事務處理是一種機制，用來管理必須成批執行的MySQL操作，以保證資料庫不包含不完整的操作結果。利用事務處理，可以保證一組操作不會中途停止，它們或者作為整體執行，或者完全不執行（除非明確指示）。如果沒有錯誤發生，整組語句提交給（寫到）資料庫表。如果發生錯誤，則進行回退（撤銷）以恢復資料庫到某個已知且安全的狀態。

因此，請看相同的例子，這次說明過程如何工作。

(1) 檢查資料庫中是否存在相應的客戶，如果不存在，新增他/她。

(2) 提交客戶資訊。

(3) 檢索客戶的ID。

(4) 新增一行到orders表。

(5) 如果在新增行到orders表時出現故障，回退。

(6) 檢索orders表中賦予的新訂單ID。

(7) 對於訂購的每項物品，新增新行到orderitems表。

(8) 如果在新增新行到orderitems時出現故障，回退所有新增的orderitems行和orders行。

(9) 提交訂單資訊。

在使用事務和事務處理時，有幾個關鍵詞彙反覆出現。下面是關於事務處理需要知道的幾個術語：

  • 事務（transaction）指一組SQL語句；

  • 回退（rollback）指撤銷指定SQL語句的過程；

  • 提交（commit）指將未儲存的SQL語句結果寫入資料庫表；

  • 保留點（savepoint）指事務處理中設定的臨時佔位（placeholder），你可以對它釋出回退（與回退整個事務處理不同）。

2. 控制事務處理

既然我們已經知道了什麼是事務處理，下面討論事務處理的管理中所涉及的問題。

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

MySQL使用下面的語句來標識事務的開始：

start transaction;

2.1 使用ROLLBACK

MySQL的ROLLBACK命令用來回退（撤銷）MySQL語句。

比如：

select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

這個例子從顯示ordertotals表（此表在第24章中填充）的內容開始。首先執行一條SELECT以顯示該表不為空。然後開始一個事務處理，用一條DELETE語句刪ordertotals中的所有行。另一條SELECT語句驗證ordertotals確實為空。這時用一條ROLLBACK語句回退START TRANSACTION之後的所有語句，最後一條SELECT語句顯示該表不為空。

顯然，ROLLBACK只能在一個事務處理內使用（在執行一條START TRANSACTION命令之後）。

補充：

  • 哪些語句可以回退？

    事務處理用來管理INSERT、UPDATE和DELETE語句。你不能回退SELECT語句。（這樣做也沒有什麼意義。）你不能回退CREATE或DROP操作。事務處理塊中可以使這兩條語句，但如果你執行回退，它們不會被撤銷。

2.2 使用COMMIT

一般的MySQL語句都是直接針對資料庫表執行和編寫的。這就是所謂的隱含提交（implicit commit），即提交（寫或儲存）操作是自動進行的。

但是，在事務處理塊中，提交不會隱含地進行。為進行明確的提交，使用COMMIT語句，如下所示：

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

在這個例子中，從系統中完全刪除訂單20010。因為涉及更新兩個資料庫表orders和orderItems，所以使用事務處理塊來保證訂單不被部分刪除。最後的COMMIT語句僅在不出錯時寫出更改。如果第一條DELETE起作用，但第二條失敗，則DELETE不會提交（實際上，它是被自動撤銷的）。

補充：

  • 隱含事務關閉：

    當COMMIT或ROLLBACK語句執行後，事務會自動關閉（將來的更改會隱含提交）。

2.3 使用保留點

簡單的ROLLBACK和COMMIT語句就可以寫入或撤銷整個事務處理。但是，只是對簡單的事務處理才能這樣做，更復雜的事務處理可能需要部分提交或回退。

例如，前面描述的新增訂單的過程為一個事務處理。如果發生錯誤，只需要返回到新增orders行之前即可，不需要回退到customers表（如果存在的話）。

為了支援回退部分事務處理，必須能在事務處理塊中合適的位置放置佔位符。這樣，如果需要回退，可以回退到某個佔位符。

這些佔位符稱為保留點。為了建立佔位符，可如下使用SAVEPOINT語句：

savepoint deletel;

每個保留點都取標識它的唯一名字，以便在回退時，MySQL知道要回退到何處。為了回退到本例給出的保留點，可如下進行：

rollback to deletel;

補充：

  • 保留點越多越好：

    可以在MySQL程式碼中設定任意多的保留點，越多越好。為什麼呢？因為保留點越多，你就越能按自己的意願靈活地進行回退。

  • 釋放保留點：

    保留點在事務處理完成（執行一條ROLLBACK或COMMIT）後自動釋放。自MySQL 5以來，也可以用RELEASE SAVEPOINT明確地釋放保留點。

2.4 更改預設的提交行為

正如所述，預設的MySQL行為是自動提交所有更改。換句話說，任何時候你執行一條MySQL語句，該語句實際上都是針對表執行的，而且所做的更改立即生效。為指示MySQL不自動提交更改，需要使用以下語句：

set autocommit = 0;

autocommit標誌決定是否自動提交更改，不管有沒有COMMIT語句。設定autocommit為0（假）指示MySQL不自動提交更改（直到autocommit被設定為真為止）。

補充：

  • 標誌為連線專用：

    autocommit標誌是針對每個連線而不是伺服器的。

相關文章