| 知乎專欄 ｜ 多維度架構 | 微信號 netkiller-ebook | QQ群：128659835 請註明“讀者” |
這裡主要講述有關開發中遇到的資料庫並行問題
id | user | sn | status ----------------------------------- 1 | neo | x001 | new 2 | jam | x002 | new 3 | sam | x003 | new 4 | tom | x004 | new 5 | ann | x005 | new 6 | leo | x006 | new 7 | ant | x007 | new 8 | cat | x008 | new
正常情況只要是多人一起打開訂單頁面就會顯示上面的訂單，並且每個人顯示的內容都相同。
CREATE TABLE `orders` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`sn` INT(10) UNSIGNED ZEROFILL NOT NULL,
`status` ENUM('New','Pending','Processing','Success','Failure') NOT NULL DEFAULT 'New',
PRIMARY KEY (`id`),
UNIQUE INDEX `sn` (`sn`)
)
COMMENT='訂貨單'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
INSERT INTO `orders` (`id`, `name`, `sn`, `status`) VALUES (1, 'neo', 0000000001, 'New'), (2, 'jam', 0000000002, 'New'), (3, 'sam', 0000000003, 'New'), (4, 'tom', 0000000004, 'New'), (5, 'ann', 0000000005, 'New'), (6, 'leo', 0000000006, 'New'), (7, 'ant', 0000000007, 'New'), (8, 'cat', 0000000008, 'New');
表 21.1. 工作流模擬
| 操作 | 訂單審核員 A | 訂單審核員 B |
|---|---|---|
| 顯示未處理訂單,這裡模擬兩個人同時點開頁面的情景 |
begin; select id from orders where status='New' limit 5 for update; update orders set status='Pending' where status='New' and id in (1,2,3,4,5); select * from orders where status='Pending' and id in (1,2,3,4,5) order by id asc limit 5; commit; 首先查詢出資料庫中的前五條記錄，然後更新為Pending狀態，防止他人搶佔訂單。 |
begin; select id from orders where status='New' limit 5 for update; update orders set status='Pending' where status='New' and id in (6,7,8); select * from orders where status='Pending' and id in (6,7,8) order by id asc limit 5; commit; select的時候會被行級所掛起，直到被commit後才能查詢出新數據，這是顯示的數據是剩下的後5條 |
| 處理訂單，模擬兩個人點擊審批通過按鈕是的情景 |
begin; select * from orders where status='Pending' and id='1' for update; update orders set status='Processing' where status='Pending' and id=1; commit; 更新狀態Pending到Processing |
begin; select * from orders where status='Pending' and id='6' for update; update orders set status='Processing' where status='Pending' and id=6; commit; 更新狀態Pending到Processing |
| 處理成功與失敗的情況 |
begin; select * from orders where status='Processing' and id='1' for update; update orders set status='Success' where status='Processing' and id=1; commit; |
begin; select * from orders where status='Processing' and id='6' for update; update orders set status='Failure' where status='Processing' and id=6; commit; |
| 處理Pending狀態的訂單，可能產生衝突，不用擔心有行鎖，防止重複處理。 |
begin; select * from orders where status='Processing' and id='5' for update; update orders set status='Failure' where status='Processing' and id=5; commit; |
begin; select * from orders where status='Processing' and id='5' for update; update orders set status='Failure' where status='Processing' and id=5; commit; |
有一種情況，用戶查看了列表並未及時處理訂單，就會有很多Pending狀態的訂單，這是需要有人處理這些訂單，但查詢Pending時，可能同一時刻有人在審批訂單，我們通過排他鎖避免重複處理。
上面以MySQL為例，每次都需要使用for update 查出要處理的訂單，如果是PostgreSQL 可以使用update + returning 來返回修改的數據，更為方便。