實作練習 : 交易 (事務) 操作 commit 以及 rollback

web - 5月 04, 2024

什麼是交易(Transaction 很多人稱為事務)功能?  交易(或事務)指的是一連串的指令，並且把它們作為一個單一的工作單位執行。這些操作要嘛全部執行，要嘛全部取消。

交易主要用於保證資料的一致性和完整性，特別是在多用戶環境中，其中多個操作可能同時對相同的資料進行讀寫。

例如一個訂單的完成，包含「將庫存資料表的數量更新」，然後「將訂購的商品資料寫入訂單資料表」。

這兩個動作如果不是都完成，就會產生問題。例如庫存資料表的數量已經-1，但是訂單資料沒有寫入，那麼庫存數量就發生錯誤了；或是訂單資料已經寫入，但是庫存的數量-1發生錯誤，這樣也會發生資料一致性的問題。

例如以下的PHP程式 order.php : 

<?php
include 'conn.inc.php'; // 引入連線設定檔

// 從 myproducts 表抓取產品
$query = "SELECT pid, pname FROM myproducts";
$result = $conn->query($query);

// 檢查訂購按鈕是否被點擊
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['submit'])) {
$pid = $_POST['product_id'];


// 開始交易(事務)
$conn->begin_transaction();

// 檢查庫存
$stock_query = "SELECT qty FROM myproducts_stock WHERE pid = $pid FOR UPDATE";
$stock_result = $conn->query($stock_query);
$stock_row = $stock_result->fetch_assoc();
if ($stock_row['qty'] > 0) {
try {
// 有庫存，庫存量-1
$update_stock = "UPDATE myproducts_stock SET qty = qty - 1 WHERE pid = $pid";
$conn->query($update_stock);
$dateTime = new DateTime();
$new = $dateTime->format('Y-m-d H:i:s');

                        // 插入新訂單資料
$order_query = "INSERT INTO myorders (pid, otime, oip) VALUES ($pid, '$new', '{$_SERVER['REMOTE_ADDR']}')";
$conn->query($order_query);

echo "產品 (編號 ".$pid.") 訂購完成！";

} catch (Exception $e) {
$conn->rollback();
echo "訂購錯誤：" . $e->getMessage();
}
} else {
// 無庫存
echo "<font color=red>抱歉，此產品 (編號 ".$pid.") 目前沒有庫存。</font>";
}
// 提交事務
$conn->commit();
}
$conn->close();
?>

<!DOCTYPE html>
<html>
<head>
<title>產品訂購</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<style>
body {
font-family: Arial, sans-serif;
padding: 20px;
margin: 0;
background: #f4f4f4;
}
form {
background: white;
padding: 20px;
border-radius: 5px;
}
label, select, button {
display: block;
width: 100%;
margin-top: 10px;
}
button {
padding: 10px;
background: #007BFF;
color: white;
border: none;
border-radius: 5px;
cursor: pointer;
}
button:hover {
background: #0056b3;
}
</style>
</head>
<body>
<h1>選擇產品並訂購</h1>
<form method="post">
<label for="product_id">選擇產品:</label>
<select name="product_id" id="product_id">
<?php
if ($result->num_rows > 0) {
// 輸出每一行資料
while($row = $result->fetch_assoc()) {
echo "<option value='" . $row["pid"] . "'>" . $row["pname"] . "</option>";
}
} else {
echo "<option>無可用產品</option>";
}
?>
</select>
<button type="submit" name="submit">訂購</button>
</form>
</body>
</html>

以上的程式就是一個交易 (事務) 的操作 : 

(1) 根據產品編號，去myproducts_stock查詢是否有庫存 (qty是否大於1)，查詢時為了避免其他連線更新庫存數量，因此使用select for update來做row lock。

思考 : 什麼情況下不lock會出錯?

(2) 如果有庫存，則該產品庫存數量-1。

(2-1) 如果沒有庫存，則commit 並跳到(5)。

(3) 把訂購資料插入myorders資料表。

(4) 如果發生錯誤則rollback，如果都正常則commit。

(5) 關閉連線並結束程式。


關於交易需要知道的重點


以上的操作有幾個問題需要了解 : 

(A) 上面例子中，應該先庫存-1 ? 還是應該先成立訂單? 
在一個交易中的多個操作，要決定誰先誰後，並沒有特別別的規定。
例如轉帳交易，應該先從轉帳方扣款? 還是應該先把款項存入轉帳目的帳戶? 

但是如果沒有處理好鎖定，程序的先後其實蠻重要的。

如果在交易中先執行庫存-1，再執行成立訂單，會比較好。但是如果有處理鎖定，程序的先後就比較沒有關係。

思考 : 為什麼?


(B) 在一般情況下，如果系統參數autocommit=1 (預設)，表示任何SQL指令都會馬上commit，因此 start transaction (也可以用begin) 執行後表示 autocommit=0。

也就是下了start transaction之後，任何DML的SQL指令都會等commit或是rollback來決定指令要不要執行。

例如以下範例 : 



但是要注意的是，並不是所有的指令都可以rollback，DDL類型的指令就無法rollback，也就是在start transaction之後，你去定義修改資料表結構，是無法回滾 (rollback)的。

更多閱讀 : MySQL~Rollback與Commit


(C) SELECT @@AUTOCOMMIT; 可以查詢目前AUTOCOMMIT 的值。

因此要能夠使用rollback，必須讓AUTOCOMMIT=0，當然使用start transaction就不需要再查詢AUTOCOMMIT的值。

以下三個是不一樣的
SELECT @@AUTOCOMMIT;
SELECT @AUTOCOMMIT;
SELECT AUTOCOMMIT;

以上三個有何差異? @@AUTOCOMMIT指系統變數，@AUTOCOMMIT指自訂變數，最後一個則是錯誤的指令。


(D) start transaction (或begin) 之後，並不代表會去鎖定資料表，如果需要鎖定資料表，需要額外的指令去完成。


1. SELECT ... LOCK IN SHARE MODE
這個語句用於在選定的數據行上放置一個共享鎖。共享鎖允許多個交易讀取同一數據行，但阻止其他交易對這些被鎖定的行進行修改，直到鎖定的交易完成。這主要用於需要讀取但不修改數據的情況，並且需要確保在讀取操作進行時，這些數據不會被其他交易修改。

例如 :
SELECT * FROM inventory WHERE product_id = 101 LOCK IN SHARE MODE;

2. SELECT ... FOR UPDATE
這個語句用於在選定的數據行上放置一個排他鎖（exclusive lock）。排他鎖阻止其他所有交易讀取或修改這些被鎖定的行，直到當前交易完成。這是一種更嚴格的鎖定方式，常用於你需要在讀取數據後進行修改的情況。

例如 :
SELECT balance FROM accounts WHERE account_id = 456 FOR UPDATE;

3. Table Lock for READ/WRITE (資料表鎖定)
表級鎖定 (Table Level Lock) 可以分為兩種類型：讀鎖定（READ LOCK）和寫鎖定（WRITE LOCK）。這些鎖定通常用於更簡單的存取控制策略，通常在較少競爭或者不需要高度並行的環境下使用。

讀鎖定允許多個交易讀取鎖定的表，但阻止任何交易修改該表。讀鎖定是共享的，意味著多個交易可以同時對同一表進行讀取操作。

例如 :
LOCK TABLES employees READ;
SELECT * FROM employees WHERE department = 'HR';
UNLOCK TABLES;

寫鎖定則更加嚴格，它不僅阻止其他交易修改表，還阻止其他交易讀取表。寫鎖定是排他的，只有獲得鎖定的交易才能讀取或修改表。

例如 :
LOCK TABLES employees WRITE;
UPDATE employees SET salary = salary * 1.1 WHERE department = 'HR';
UNLOCK TABLES;


(E) MySQL 常用的兩個資料表類型：MyISAM 不支援交易功能，InnoDB則支援交易功能。因此要使用交易功能前，請先檢查你的資料表的儲存引擎是哪種。

你可以使用這個指令，把InnoDB的資料表都列出來 : 

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';


你可以使用這個指令，了解預設的儲存引擎是哪種 : 

SELECT @@default_storage_engine;




(F) 交易過程中，可標示多個不同的儲存點，有需要時可 ROLLBACK 到某個儲存點。

建立儲存點：SAVEPOINT 名稱 
刪除儲存點：RELEASE SAVEPOINT 名稱 
ROLLBACK 到某個儲存點：ROLLBACK TO SAVEPOINT 名稱 
如果建立新儲存點時，已有同名稱的舊儲存點，舊儲存點將被刪除，並建立新的儲存點。 

範例 : 
BEGIN; 
INSERT INTO student VALUES(10, 'abc'); 
COMMIT; 

範例 : 
START TRANSACTION; 
INSERT INTO student VALUES(10, 'abc'); 
COMMIT; 

範例 : 
START TRANSACTION; 
SELECT @A:=SUM(salary) FROM table1 WHERE type=1; 
UPDATE table2 SET summary=@A WHERE type=1; 
COMMIT;

範例 : 
BEGIN; 
INSERT INTO testtable (id, t) VALUES(1, 10); 
SAVEPOINT p1; 
UPDATE student SET t=20 WHERE id=1; 
ROLLBACK TO SAVEPOINT  p1; 
COMMIT;

總之，在多人連線的系統下，正確的處理交易以及正確的使用鎖定是很重要的事情，並且正確性與效能是同樣重要的事情。

有些系統效能比正確性還重要 (例如臉書的數據處理)，有些系統正確性比效能還重要 (例如銀行的數據處理)。

效能比正確性還重要時，太多的鎖定會影響效能，就要避免不必要的鎖定，資料的正確性再用另外的方式彙整，所以你經常會看到臉書的粉絲數量忽高忽低。

正確性比效能還重要時，就要用悲觀鎖定策略，任何可能出錯的地方都要鎖定，寧可延遲也不能錯誤。


[後記]

以上的程式 order.php 中，會影響執行結果的地方就在於這行 :

$stock_query = "SELECT qty FROM myproducts_stock WHERE pid = $pid FOR UPDATE";

如果把這行改為 : 

$stock_query = "SELECT qty FROM myproducts_stock WHERE pid = $pid";

變成沒有鎖定，在多人環境下資料就會出錯，qty可能會出現負值，或是訂單數與庫存不符合。


標籤 :

張貼留言

0 留言