updlock实例分析

先给出一个场景，然后围绕这个场景展开讨论。
场景：商品限购，商家在发布活动时，要求活动内的每个商品每位用户限购一个。

假设现在有一张表活动订单表，记录了用户参与活动的情况。
活动订单表（主键，活动主键，用户主键，商品主键）

-- sql server 2008
CREATE TABLE [dbo].[tbl_activity_order] (
  [id] bigint NOT NULL,
  [activity_id] bigint NOT NULL,
  [user_id] bigint NOT NULL,
  [goods_id] bigint NOT NULL,
  PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

EXEC sp_addextendedproperty
'MS_Description', N'活动主键',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'activity_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'用户主键',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'user_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商品主键',
'SCHEMA', N'dbo',
'TABLE', N'Untitled',
'COLUMN', N'goods_id'

拿到了表结构，下面我们就开始想如何实现限购这个功能吧。最先进入脑海的应该就是在用户下单前先判断下用户是否有过购买记录，如果已经有了购买记录，则触发限购，反之可以正常购买。

步骤	事务1
1	BEGIN TRANSACTION;
2	select count(*) from tbl_activity_order where activity_id = ? and user_id = ? and goods_id = ?;
3	insert into tbl_activity_order(id, activity_id, user_id, goods_id) values(?, ?, ?, ?);

仔细想想是不是这样的流程就能够保证用户不会超买的问题呢？
现在我们开启两个事务来看看吧

操作步骤	事务1	事务2
1	BEGIN TRANSACTION;	BEGIN TRANSACTION;
2	select count(*) from tbl_activity_order where activity_id = 1 and user_id = 1 and goods_id = 1;	select count(*) from tbl_activity_order where activity_id = 1 and user_id = 1 and goods_id = 1;
3	insert into tbl_activity_order(id, activity_id, user_id, goods_id) values(1, 1, 1, 1);
4		insert into tbl_activity_order(id, activity_id, user_id, goods_id) values(2, 1, 1, 1);
5	commit;
6		commit;

当我们执行完上面这个步骤之后，我们会发现tbl_activity_order表中多了两条记录，且这两条记录的activity_id，user_id，goods_id是一样的，说明限购失效了。有人会说我加个唯一索引idx_activity_user_goods不就可以了吗？保证数据库中这三个字段的组合不会出现重复。想法是不错，可以如果下次活动限购不是1个，而是2个或者更多怎么办，那就允许用户多次下单了，唯一索引就不可取了。

问1：想想为什么上面的步骤不能满足限购？
         因为两个事务的执行步骤出现了交叉。

问2：那么怎样才能防止此类现象的出现呢？
         不让事务在此处的执行出现交叉。

问3：那么怎样才能不让事务在此处执行出现交叉呢？
         如果我们能够在select的时候就锁住表是不是就好了。此时updlock就派上了用场。

问4：现在想想数据库在接收到select命令时，它是怎么查询到我们想要的结果的？
         我们知道关系型数据库一般都是使用树的方式存放索引的，此时你就需要在你的脑海里想象出一颗树。我们在建tbl_activity_order表时除了默认的主键索引外，并没有新建其他的索引。因此当我们执行

select count(*) from tbl_activity_order where activity_id = 1 and user_id = 1 and goods_id = 1;

语句时，使用的是聚集索引扫描，也就是说依次扫描聚集索引树上的每个叶子节点，然后从磁盘上把每个主键对应的记录值读出来，再判断activity_id，user_id，goods_id是否满足查找条件，如果满足条件结果值就+1，直到遍历到最后一个节点的最后一条记录才会把结果展示给我们（其实学过数据结构的都知道这个查找的过程就是在做树的遍历查找）。

问5：我们知道锁的类型大体上就两种：互斥锁、共享锁；锁粒度可分为：表锁、页锁、行锁。那么我们这里的select应该选择什么锁？
         因为要防止事务交叉，只能使用互斥锁。同时我们说要在实现功能的前提下使锁的粒度尽可能的小，因此这里如果可以我们应该选择行锁。

问6：我们怎么实现这个锁呢？
         在问题4中我们分析了select的执行步骤，知道了它是借助聚集索引实现的全表扫描。那么我们能够在现有条件下实现行锁呢？这个恐怕是无法实现的，因为聚集索引是按照id的顺序来存储的，所以全局来看只有id是有序的，其余字段都是无序的。我们可以执行下面的步骤验证下我们的猜想

-- 清空测试数据
delete from tbl_activity_order;

-- 插入两组活动数据
insert into tbl_activity_order(id, activity_id, user_id, goods_id) values
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 2, 1, 1),
(4, 2, 2, 1);

步骤	事务1	事务2
1	BEGIN TRANSACTION;	BEGIN TRANSACTION;
2	select * from tbl_activity_order WITH(updlock, INDEX(PK__tbl_acti__3213E83F51900108)) where activity_id = 1 and user_id = 1 and goods_id = 1;
3		select * from tbl_activity_order WITH(updlock, INDEX(PK__tbl_acti__3213E83F51900108)) where activity_id = 1 and user_id = 2 and goods_id = 1;
4	commit;
5		commit;

注：PK__tbl_acti__3213E83F51900108是聚集索引的名称，大家需要替换成自己的聚集索引去执行。

执行到步骤3时发现卡住了，必须把步骤4执行之后，步骤3才能放行。由于两个select的查找谓词不同，所以他们返回的行不同，如果这里是行锁的话，步骤3不应该被卡住，这里步骤3被卡住也就说明了这里并不是行锁。

现在我们新建一个索引idx_activity_user_goods，然后再使用新建的索引替换聚集索引

CREATE NONCLUSTERED INDEX [idx_activity_user_goods]
ON [dbo].[tbl_activity_order] (
  [activity_id],
  [user_id],
  [goods_id]
)

步骤	事务1	事务2
1	BEGIN TRANSACTION;	BEGIN TRANSACTION;
2	select * from tbl_activity_order WITH(updlock, INDEX(idx_activity_user_goods)) where activity_id = 1 and user_id = 1 and goods_id = 1;
3		select * from tbl_activity_order WITH(updlock, INDEX(idx_activity_user_goods)) where activity_id = 1 and user_id = 2 and goods_id = 1;
4	commit;
5		commit;

执行上述步骤发现并没有像使用聚集索引那样出现锁等待的情况。

现在我们已经解决了select加锁的问题，但是到现在都只是select操作，还没有进行update操作，下面我们还是使用2个事务来测试吧。

-- 清空测试数据
delete from tbl_activity_order;

步骤	事务1	事务2
1	BEGIN TRANSACTION;	BEGIN TRANSACTION;
2	select * from tbl_activity_order WITH(updlock, INDEX(idx_activity_user_goods)) where activity_id = 1 and user_id = 1 and goods_id = 1;
3		select * from tbl_activity_order WITH(updlock, INDEX(idx_activity_user_goods)) where activity_id = 1 and user_id = 1 and goods_id = 1;
4	insert into tbl_activity_order(id, activity_id, user_id, goods_id) values(5, 1, 1, 1);
5	commit;
6		insert into tbl_activity_order(id, activity_id, user_id, goods_id) values(6, 1, 1, 1);
7		commit;

执行上述步骤后发现我们会在步骤3卡住，然后执行步骤4/5之后，步骤3才会被放行，此时查询到表中已经存在一条记录，则跳过步骤6，执行步骤7。

至此限购的功能就此完成，现在回头看下我们的改动

1. 增加一个索引idx_activity_user_goods
2. 改写原先的select语句，加上WITH(updlock, INDEX(idx_activity_user_goods)) ，从而实现阻塞同一个用户在同一个活动下购买同一个商品的流程（注意这段话有三个同一个：同一个用户，同一个活动，同一个商品。这样能够保证我们锁粒度最小，对并发的影响降到最低。）