老天尊的死期: 用PHP實現MySQL Transaction 交易功能

情境：
假設庫存有6隻手機(stock)要賣，總共有8個人要搶購，手機庫存不能小於零，所以只有六個人會買到。把有誰在搶購(action_record)和誰搶購到紀錄(trade_record)到資料表，並更商品新庫存數量。並在交易後執行一些SQL，來確保交易後的SQL可以正常執行。

我在trade資料庫的資料表結構如下，我用下面這三張表做測試：

/*! action_record表是操作紀錄，有連線進來的紀錄都記進去，不受商品庫存影響 */
CREATE TABLE `action_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(255) NOT NULL,
  `ms` varchar(255) NOT NULL,
  `new_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*! stock表是商品資料表，記錄了庫存量，stock欄位 */
CREATE TABLE `stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `stock` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*! trade_record表是交易記錄表，有交易成功才記錄 */
CREATE TABLE `trade_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `stock_id` int(11) NOT NULL,
  `action_id` int(11) NOT NULL,
  `new_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如何用一道指令模擬同時搶購的行為？
我們寫一個簡單的script來跑，print.sh：

#!/usr/bin/bash
echo "test:$1 ms:$(($(date +%s%N)/1000000))" >> print.log

取現在的timestamp的millisecond倒到print.log中，並觀察print.log
$ echo $(($(date +%s%N)/1000000)) # 取現在的timestamp
PHP取現在的timestamp的millisecond
echo round(microtime(true) * 1000);

有兩種方法
1. pipe line ( 不採用 )
$ bash print.sh 1 | bash print.sh 2 # 跑兩次
print.log:
test:1 ms:1426572647622
test:2 ms:1426572647624
test:2 ms:1426572652053
test:1 ms:1426572652057

用pipe line執行多次print.sh後，在print.log中我們可以看到，pipe line幾乎是同時執行的，有時候甚至第二個命令會比第一個快一點。而非第一個執行完才跑第二個

1.1. 證明pipe line同時執行的其他方法（robert）
1.1.1 先產生一個大一點的log檔（1G以上）
$ cat access_log.log >> tmp2.log => 跑多次
$ ls tmp2.log -lh
-rw-rw-r-- 1 bear bear 1.1G Nov 9 11:45 tmp2.log
1.1.2 使用pipe line，並切換screen 用top或ps檢查
$ cat tmp2.log |awk '{print $1}'|sort -n|uniq -c|sort -n
4372760 192.168.x.147
$ top
或
$ ps aux | egrep -e '(awk|sort|cat)'
bear 14113 19.0 0.0 7224 688 pts/7 S+ 14:32 0:00 cat tmp2.log
bear 14114 75.0 0.0 14896 984 pts/7 S+ 14:32 0:00 awk {print $1}
bear 14115 57.0 0.4 15728 8568 pts/7 R+ 14:32 0:00 sort -n
bear 14117 0.0 0.0 15724 2024 pts/7 S+ 14:32 0:00 sort -n
bear 14119 0.0 0.1 9452 2136 pts/6 S+ 14:32 0:00 egrep --color=auto -e (awk|sort|cat)
可以看到在原本tty結果出來之前，top和ps 都列出 pipe line 後面的 cat, awk, sort都已經在執行了

正規式grep： | egrep -e '(awk|sort|cat)'

2. nohup <command> & ( 採用 )
$ nohup bash print.sh 1 & nohup bash print.sh 2 & #跑兩次
print.log:
test:2 ms:1426573615274
test:1 ms:1426573615275
test:1 ms:1426573627275
test:2 ms:1426573627278

與pipe line一樣是同時執行，有時候甚至第二個命令會比第一個快一點。

模擬交易程式 buy.php：

// useage: php buy.php 
$user_id = $argv[1];
$stock_id = 1;
exec("echo \"user_id:$user_id, start_time_ms:$(($(date +%s%N)/1000000))\" >> print.log");

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli("localhost", "username", "password", "trade");

$action = array('user_id' => $user_id, "stock_id" => $stock_id);
$action_record_start_id = save_action_record($db, $action);

$result = $db->query("SELECT * FROM `stock` WHERE id = 1");
$row = $result->fetch_assoc();

try {
 echo "try!\n";
    // First of all, let's begin a transaction
 $db->autocommit(FALSE); // equal START TRANSACTION or BEGIN

    // A set of queries; if one fails, an exception should be thrown

    $db->query("INSERT INTO `trade_record` (`id`, `user_id`, `stock_id`, `action_id`, `new_time`) VALUES (NULL, '$user_id', '$stock_id', '$action_record_start_id', NOW());");

 $db->query("UPDATE stock SET stock=stock-1 WHERE id=1");

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
 $db->commit();
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    echo "Exception!\n";
 $db->rollback();
}

$db->autocommit(TRUE);
$action = array('action_record_start_id' => $action_record_start_id, 'status' => 'END_TRANSACTION');
save_action_record($db, $action);

echo "\n";

function milliseconds() {
    $mt = explode(' ', microtime());
    return $mt[1] * 1000 + round($mt[0] * 1000);
}

function save_action_record($db, $action) {
 $action_json = json_encode($action);
 $ms = milliseconds();
 $sql = "INSERT INTO action_record VALUES (NULL, '$action_json', '$ms', NOW())";
 // echo $sql."\n";
 $db->query($sql);
 return $db->insert_id;
}

只要有執行，就寫入操作記錄(action_record)，交易成功便寫入交易記錄(trade_record)，並在商品(stock)把庫存減一，交易完成後再寫一次操作記錄，記錄這是哪個操作記錄的結束。

讓mysqli會丟出例外( Exceptions )
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

關於交易的四個特性(ACID)
A - Atomicity (原子性、不可分割)：交易內的 SQL 指令，不管在任何情況，都只能是全部執行完成，或全部不執行。若是發生無法全部執行完成的狀況，則會回滾(rollback)到完全沒執行時的狀態。
C - Consistency (一致性)：交易完成後，必須維持資料的完整性。所有資料必須符合預設的驗證規則、外鍵限制...等。
I - Isolation (隔離性)：多個交易可以獨立、同時執行，不會互相干擾。
D - Durability (持久性)：交易完成後，異動結果須完整的保留。

開始進入交易模式
START TRANSACTION 或 BEGIN 或 SET AUTOCOMMIT=1
結束交易模式
交易完成：使用 COMMIT 儲存所有變動，並結束交易。
交易過程異常：使用 ROLLBACK 回滾，取消交易，還原到未進行交易的狀態。(交易過程斷線亦會ROLLBACK)

更多的議題，如儲存點 (SAVEPOINT)、不能 ROLLBACK 的指令、會造成自動終止交易並 COMMIT 的指令、查詢目前 AUTOCOMMIT 的設定值、InnoDB的3種加鎖的類型、InnoDB 實現的鎖定模式、不同 SQL Statements 在 InnoDB 的鎖定、讀取可能產生的異常情況、交易的4種隔離層級、設定交易層級、死結 (dead lock) ... 等更進階議題，請看參考資料中 XYZ的筆記本的連結

使用pipe line 同時搶購
$ php buy.php 1 | php buy.php 2 | php buy.php 3 | php buy.php 4 | php buy.php 5 | php buy.php 6 | php buy.php 7 | php buy.php 8 #1~8號使用者同時搶
結果：

mysql> use trade;
Database changed
mysql> select * from stock;
+----+-------+-------+
| id | name  | stock |
+----+-------+-------+
|  1 | phone |     0 |
+----+-------+-------+
1 row in set (0.00 sec)

mysql> select * from trade_record;
+----+---------+----------+-----------+---------------------+
| id | user_id | stock_id | action_id | new_time            |
+----+---------+----------+-----------+---------------------+
|  1 |       4 |        1 |         1 | 2015-03-17 13:58:52 |
|  2 |       5 |        1 |         2 | 2015-03-17 13:58:53 |
|  3 |       2 |        1 |         5 | 2015-03-17 13:58:53 |
|  4 |       3 |        1 |         6 | 2015-03-17 13:58:53 |
|  5 |       7 |        1 |         8 | 2015-03-17 13:58:53 |
|  7 |       6 |        1 |         4 | 2015-03-17 13:58:53 |
+----+---------+----------+-----------+---------------------+
6 rows in set (0.00 sec)

mysql> select * from action_record;
+----+---------------------------------------------------------+---------------+---------------------+  
| id | action                                                  | ms            | new_time            |  
+----+---------------------------------------------------------+---------------+---------------------+  
|  1 | {"user_id":"4","stock_id":1}                            | 1426571932876 | 2015-03-17 13:58:52 |
|  2 | {"user_id":"5","stock_id":1}                            | 1426571932877 | 2015-03-17 13:58:52 |
|  3 | {"user_id":"1","stock_id":1}                            | 1426571932878 | 2015-03-17 13:58:52 |
|  4 | {"user_id":"6","stock_id":1}                            | 1426571932882 | 2015-03-17 13:58:52 |
|  5 | {"user_id":"2","stock_id":1}                            | 1426571932884 | 2015-03-17 13:58:52 |  
|  6 | {"user_id":"3","stock_id":1}                            | 1426571932886 | 2015-03-17 13:58:52 |  
|  7 | {"user_id":"8","stock_id":1}                            | 1426571932886 | 2015-03-17 13:58:52 |  
|  8 | {"user_id":"7","stock_id":1}                            | 1426571932888 | 2015-03-17 13:58:52 |
|  9 | {"action_record_start_id":1,"status":"END_TRANSACTION"} | 1426571933040 | 2015-03-17 13:58:53 |  
| 10 | {"action_record_start_id":2,"status":"END_TRANSACTION"} | 1426571933072 | 2015-03-17 13:58:53 |  
| 11 | {"action_record_start_id":8,"status":"END_TRANSACTION"} | 1426571933139 | 2015-03-17 13:58:53 |  
| 12 | {"action_record_start_id":5,"status":"END_TRANSACTION"} | 1426571933172 | 2015-03-17 13:58:53 |  
| 13 | {"action_record_start_id":6,"status":"END_TRANSACTION"} | 1426571933239 | 2015-03-17 13:58:53 |  
| 14 | {"action_record_start_id":4,"status":"END_TRANSACTION"} | 1426571933272 | 2015-03-17 13:58:53 |  
| 15 | {"action_record_start_id":7,"status":"END_TRANSACTION"} | 1426571933338 | 2015-03-17 13:58:53 |  
+----+---------------------------------------------------------+---------------+---------------------+  
15 rows in set (0.00 sec)

商品(stock)庫存為0 - 正常
交易記錄(trade_record)有六筆資料 - 正常
交易前的操作記錄(action_record)有8筆資料 - 正常
交易後的操作記錄(action_record)有7筆資料 - 異常

經我反覆測試後，"交易後的操作記錄"會有問題，有時候正常，有時候不足8筆

使用nohup <command> & 同時搶購
$ nohup php buy.php 1 & nohup php buy.php 2 & nohup php buy.php 3 & nohup php buy.php 4 & nohup php buy.php 5 & nohup php buy.php 6 & nohup php buy.php 7 & nohup php buy.php 8 &
經多次測試後 "交易後的操作記錄"(action_record)都有8筆資料 - 正常
其他資料表與pipe line方法相同 - 正常

小結：
同時跑多個進程(process)時，還是用nohup比較靠譜一點

範例程式(含table schema)：
https://github.com/kalecgos0616/tools/tree/master/transaction

參考資料：
http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples PHP + MySQL transactions examples
http://xyz.cinc.biz/2013/05/mysql-transaction.html XYZ的筆記本 - MySQL 交易功能 Transaction 整理(較詳細，主要參考這篇)
http://stackoverflow.com/questions/12091971/how-to-start-and-end-transaction-in-mysqli How to start and end transaction in mysqli?
http://blog.longwin.com.tw/2006/03/innodb_transaction_2006/ MySQL InnoDB 的 Transaction 的簡易用法
http://stackoverflow.com/questions/16548528/linux-command-to-get-time-in-milliseconds Linux command to get time in milliseconds
http://stackoverflow.com/questions/3656713/how-to-get-current-time-in-milliseconds-in-php How to get current time in milliseconds in PHP?
http://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli Turning query errors to Exceptions in MySQLi
http://oss.org.cn/man/database/mysql/inonodb_zh/8.htm InnoDB 事务模式与锁定

老天尊的死期

2015年3月17日星期二

用PHP實現MySQL Transaction 交易功能

沒有留言:

張貼留言

著作人

2015年3月17日 星期二

用PHP實現MySQL Transaction 交易功能

沒有留言:

張貼留言

2015年3月17日星期二