编程经验点滴----使用接口表保存可能并发的业务,然后顺序处理

2017-06-01 15:11

 

分享一点自己多年编程的经验,抛砖引玉吧。期望与大家交流。

 

这篇文章的主要内容,来自与其他人的讨论。

软件系统的开发或设计时,容易遇到有并发的情况。有时候需要刻意去避免,防止数据错误。比如超市卖商品,可能两个柜台同时卖出一款矿泉水,如果软件系统后台需要跟踪每个商品的库存,此时就需要特别考虑。如果两个柜台,同时采取"读当前库存,减一,得到最新库存,保存"的设计,则可能会导致数据错误。比如,两个柜台,读当前库存,都得到 100, 减一,都得到99,作为最新数据保存,保存99。最后,尽管同时卖出了两瓶矿泉水,最后系统的库存确是99。无疑是有问题的。

一个简单的解决办法,就是再设计一个接口表。对于有可能并发的操作,统一插入一条"待处理的操作指令"到此接口表中,然后单独起一个线程,逐个处理此接口表中待处理数据。

 

大致步骤如下:

1. 并发处理,统一插入一条待处理的操作指令到此接口表中,只 insert:


insert into ti_xxx ....; --process_flag = 0
		

 

2. 单独起一个线程,逐个读 : ti_xxx 中未处理的数据.

2.1 查询未处理数据


select top 1 from ti_xxx where process_flag = 0 order by increase_key,created_time;
		

 

2.2. insert/update 到 tt_xxx :


if exists(select 1 from tt_xxx where ....)
  update tt_xxx ....
else
  insert into tt_xxx...
		

 

2.3 更新 ti_xxx 数据为已处理:


update ti_xxx set process_flag = 1 where increase_key = xxx;
		

其中,ti_xxx 表使用自增长主键,或使用 uuid 做主键。

 

如果只是单纯的超市软件系统,它的库存计算,其实不用很实时。让管理员人员,看当前时间的库存,与看5分钟之前的库存,从纯粹的管理层面,并没有大的区别。实际上,绝大多数系统,数据的实时性要求,都没有高到需要完全实时。另一方面,此类系统对数据的最终准确性,要求却是非常高的。比如,客户不太在意,9:05 分卖出一款矿泉水,只能在 9:10看到库存减少。但客户在意的是,9:05 分时刻卖出一款矿泉水,至少在下班后(21:00),能看到结果。

如果我们将以上所述"单独起一个线程",做成每 0.5秒 运行一次的定时任务,则对于客户来说,完全看不到影响。

 

-------------------------------

2017/6/3 补充,(2017/6/4发现,以下测试步骤中有不当的地方,请忽略).

有人提到,可以用纯 SQL 来处理并发,使用适当的 lock 。但这样有时并不管用。比如按如下测试,则测试出问题:

测试环境: Windows 8.1 64位 + SQL Server 2014 Express.

测试步骤:

step_1, 创建数据库 test_db1。

 

step_2, 运行 SQL 更改数据库属性:


ALTER DATABASE test_db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE test_db1 SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE test_db1 SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE test_db1 SET MULTI_USER;
		

 

step_3,创建表,


CREATE TABLE [dbo].[Test](
[Id] [bigint] NULL,
[Name] [varchar](50) NULL,
[Counter] [bigint] NULL
) ON [PRIMARY];
		

 

step_4,创建存储过程:


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_test]
@Id [bigint],
@Name [varchar](50)

AS
BEGIN
BEGIN TRANSACTION
--先尝试更新记录占坑
UPDATE Test WITH(HOLDLOCK)
SET [Counter] = [Counter] + 1
WHERE Id = @Id;

WAITFOR DELAY '00:02:00';


--如果更新操作没有影响行,证明记录不存在,则插入
IF @@ROWCOUNT<1
BEGIN
INSERT Test
( Id, Name, [Counter] )
VALUES ( @Id, @Name, 1 );
END
COMMIT
END


GO
		

中间加了暂停。

 

step_5. 开两个 SQL Server Management studio, 分别运行 sp_test, 参数分别为:

step_5_1:

id=1,name='A',

 

step_5_2:

id=1,name='B',

 

step_6, 验证最后数据:


SELECT TOP 1000 * FROM [test_db1].[dbo].[Test];
		

得到两行数据:

Id Name Counter

1 A 2

1 B 1

 

结论:

纯SQL 代码不能起到期望的结果。

 

 

---------------------------------------------

2017/6/4 补充更正

重新测试,结论是 UPDATE...WITH(HOLDLOCK)... 可以锁住表的 update 操作,起到"并发时顺序处理"的期望结果。但并不需要使用 SET [Counter] = [Counter] + 1 这样的语句。

测试环境: Windows 8.1 64位 + SQL Server 2014 Express.

测试步骤:

step_1, 创建数据库 test_db1。

 

step_2, 运行 SQL 更改数据库属性:


ALTER DATABASE test_db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE test_db1 SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE test_db1 SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE test_db1 SET MULTI_USER;
		

 

step_3,创建表,


CREATE TABLE [dbo].[Test](
    [Id] [bigint] NULL,
    [Name] [varchar](50) NULL,
    [Counter0] [bigint] NULL,
    [created_time] [datetime] NULL,
    [updated_time] [datetime] NULL
) ON [PRIMARY];
		

 

step_4,创建存储过程:


CREATE PROCEDURE [dbo].[sp_test]
    @Id  [bigint],
    @Name [varchar](50)

AS
BEGIN
    BEGIN TRANSACTION
    --先尝试更新记录占坑
    print 'a0:'+ convert(varchar(255), getdate(), 121) + ','

    UPDATE  Test WITH(HOLDLOCK)
    SET   --  [Counter] = [Counter] + 1,
        Name=@Name, updated_time = getdate()
    WHERE   Id = @Id;

    --需要在 WAITFOR DELAY 之前,将 @@ROWCOUNT 中的数值,暂时保存起来。因为 WAITFOR DELAY 之后,@@ROWCOUNT 中的数值会变。
    DECLARE @v_ROWCOUNT bigint
    set @v_ROWCOUNT = @@ROWCOUNT
    print 'a1:'+ convert(varchar(255), getdate(), 121)  + ',ROWCOUNT='+ cast( @v_ROWCOUNT as varchar(255))
    print 'a1.5:'+ convert(varchar(255), getdate(), 121)  + ',ROWCOUNT='+ cast( @@ROWCOUNT as varchar(255))

    WAITFOR DELAY '00:00:20';

    print 'a2:'+ convert(varchar(255), getdate(), 121) + ',ROWCOUNT='+ cast( @@ROWCOUNT as varchar(255))

    --如果更新操作没有影响行,证明记录不存在,则插入
    IF @v_ROWCOUNT < 1
    BEGIN
        INSERT  Test
                ( Id, Name
                --, [Counter]
                ,created_time,updated_time )
        VALUES  ( @Id, @Name
            --, 1
            , getdate(), getdate() );
        print 'a3:'+ convert(varchar(255), getdate(), 121) + ',ROWCOUNT='+ cast( @@ROWCOUNT as varchar(255))
        
        WAITFOR DELAY '00:00:05';
    END
        print 'a4:'+ convert(varchar(255), getdate(), 121)
        WAITFOR DELAY '00:00:02';
        print 'a4.5:'+ convert(varchar(255), getdate(), 121)
    COMMIT
        print 'a5:'+ convert(varchar(255), getdate(), 121)
END


GO
		

中间加了暂停。

 

step_5. 开两个 SQL Server Management studio, 分别运行 sp_test, 参数分别为:

step_5_1:

id=1,name='A',

 

step_5_2:

id=1,name='B',

 

step_6, 验证最后数据:


SELECT TOP 1000 * FROM [test_db1].[dbo].[Test];
		

得到一行数据:

Id Name Counter0 created_time updated_time

1 B NULL 2017-06-04 14:59:46.517 2017-06-04 14:59:53.520

从调试运行执行存储过程 SQL 的消息日志中,可以看到第二次存储过程的 update 的执行,确实是在第一次执行的 commit 之后。

 

结论:

UPDATE...WITH(HOLDLOCK)... 可以锁住表的 update 操作,起到"并发时顺序处理"的期望结果。但并不需要使用 SET [Counter] = [Counter] + 1 这样的语句。

很抱歉之前的错误结论,可能误导了一些朋友。

实测结果,数据库属性中,增加 :


ALTER DATABASE test_db1 SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE test_db1 SET READ_COMMITTED_SNAPSHOT ON;
		

可以在 UPDATE...WITH(HOLDLOCK)... 的数据库事务执行过程中,select 表 Test 数据。

而不加 ALLOW_SNAPSHOT_ISOLATION + READ_COMMITTED_SNAPSHOT,则此时 select 也堵塞。但 update Test 表都堵塞。

 

 

欢迎转载,转载请注明出处: https://www.zheguisoft.com/staff_blogs/jacklondon_chen/2017, 及 https://www.cnblogs.com/jacklondon/p/programming_experience_concurrent.html