深入理解MySQL中的事務機制

 使用數據庫事務可以確保除事務性單元內的所有操作都成功完成。MySQL中的InnoDB引擎的表才支持transaction。在一個事務裡，如果出現一個數據庫操作失敗了，事務內的所有操作將被回滾，數據庫將會回到事務前的初始狀態。有一些不能被回滾的語句：將在本文的最後討論。

在一個web應用中，會很經常遇到需要使用事務的地方，要麼希望若干語句都執行成功，要麼都不執行，如果出現有些執行成功，而其他的失敗將會導致數據損壞。

在這篇文章的例子中，我們使用下面的兩張表"employee"和"telephone"，下面是SQL語句（作為參考）：

創建 employee表：
 

CREATE TABLE `employee` (
 `id` int NOT NULL AUTO_INCREMENT,
 `first_name` varchar(100) NOT NULL,
 `last_name` varchar(100) NOT NULL,
 `job_title` varchar(100) DEFAULT NULL,
 `salary` double DEFAULT NULL,
 `notes` text,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

向employee中插入數據
 

INSERT INTO `employee` (`first_name`, `last_name`, `job_title`, `salary`) VALUES
            ('Robin', 'Jackman', 'Software Engineer', 5500),
            ('Taylor', 'Edward', 'Software Architect', 7200),
            ('Vivian', 'Dickens', 'Database Administrator', 6000),
            ('Harry', 'Clifford', 'Database Administrator', 6800),
            ('Eliza', 'Clifford', 'Software Engineer', 4750),
            ('Nancy', 'Newman', 'Software Engineer', 5100),
            ('Melinda', 'Clifford', 'Project Manager', 8500),
            ('Harley', 'Gilbert', 'Software Architect', 8000);

創建telephone表
 

CREATE TABLE `telephone` (
 `id` int NOT NULL AUTO_INCREMENT,
 `employee_id` int DEFAULT NULL,
 `type` varchar(20) NOT NULL,
 `no` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

向telephone表插入數據
 

INSERT INTO `telephone` (`employee_id`, `type`, `no`) VALUES
            (1, 'mobile',  '245-249697'),
            (2, 'mobile',  '270-235969'),
            (2, 'land',   '325-888885'),
            (3, 'mobile',  '270-684972'),
            (4, 'mobile',  '245-782365'),           
            (4, 'land',   '325-888886'),
            (5, 'mobile',  '245-537891'),
            (6, 'mobile',  '270-359457'),
            (7, 'mobile',  '245-436589'),                           
            (7, 'land',   '325-888887'),
            (8, 'mobile',  '245-279164'),                           
            (8, 'land',   '325-888888');

    設想你需要一個新的叫做Grace Williams雇員，並帶有他的電話號碼信息。你可能會執行下面兩句sql： 
 

INSERT INTO `employee` (`id`, `first_name`, `last_name`,
`job_title`, `salary`) VALUES (9, 'Grace', 'Williams',
'Softwaree Engineer', 5000);
 
INSERT INTO `telephone` (`id`, `employee_id`, `type`,
`no`) VALUES (13, 9, 'mobile', '270-598712');

讓我們看看第二個語句，在第一個語句中，employee_id是在第一條語句中指定的，設想一下，當第一條語句失敗，而第二條語句成功的狀況。在這種狀況下，telephone表中就會有一條employee_id為9的記錄，而employee表中並沒有id為9的記錄，而如果將這兩個語句放在MySQL事務中，如果第一條語句失敗，那麼第二條語句也將回滾，從而不會造成這種問題。

在PHP（PHP參考文檔）中我們可以使用如下的方式啟用事務：
 

<?php
 
//$salary = 5000;
$salary = '$5000';
 
/* Change database details according to your database */
$dbConnection = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');
 
mysqli_autocommit($dbConnection, false);
 
$flag = true;
 
$query1 = "INSERT INTO `employee` (`id`, `first_name`, `last_name`, `job_title`, `salary`) VALUES (9, 'Grace', 'Williams', 'Softwaree Engineer', $salary)";
$query2 = "INSERT INTO `telephone` (`id`, `employee_id`, `type`, `no`) VALUES (13, 9, 'mobile', '270-598712')";
 
$result = mysqli_query($dbConnection, $query1);
 
if (!$result) {
  $flag = false;
  echo "Error details: " . mysqli_error($dbConnection) . ". ";
}
 
$result = mysqli_query($dbConnection, $query2);
 
if (!$result) {
  $flag = false;
  echo "Error details: " . mysqli_error($dbConnection) . ". ";
}
 
if ($flag) {
 
  mysqli_commit($dbConnection);
  echo "All queries were executed successfully";
 
} else {
 
  mysqli_rollback($dbConnection);
  echo "All queries were rolled back"; 
 
}
 
mysqli_close($dbConnection);
 
?>

    當你執行mysqli_query函數的時候，結果被立即提交到了數據庫。使用mysqli_autocommit函數，可以關閉自動提交，執行結果只有當你想提交的時候才提交。
    如果任何語句執行失敗我們都可以設置$flag變量為false。如果有很多語句要執行，可以考慮將他們放在for循環中。
    最後，如果flag是true（也就是沒有錯誤發生），我們使用mysqli_commit提交事務。否則我們使用mysqli_rollback回滾事務。

所以，事務可以在某種程度上幫助我們維護數據的完整和正確，另外，為了保證數據無誤，我們還推薦使用外鍵。

並不是所有的語句都是支持事務的，例如，如果使用CREATE TABLE或者ALTER TABLE語句，需要了解更多可以參考MySQL手冊查看哪些語句不能回滾。