| 知乎专栏 ｜ 多维度架构 |
这里主要讲述有关开发中遇到的数据库并行问题
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');
表 4.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 来返回修改的数据，更为方便。