数据库并行访问控制之互斥显示
表?9.1.?工作流模拟
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时，可能同一时刻有人在审批订单，我们通过排他锁避免重复处理。
