数据库存在即更新的高并发处理 - 转
這篇文章的主要內(nèi)容，來自與其他人的討論。
軟件系統(tǒng)的開發(fā)或設(shè)計時，容易遇到有并發(fā)的情況。有時候需要刻意去避免，防止數(shù)據(jù)錯誤。比如超市賣商品，可能兩個柜臺同時賣出一款礦泉水，如果軟件系統(tǒng)后臺需要跟蹤每個商品的庫存，此時就需要特別考慮。如果兩個柜臺，同時采取＂讀當(dāng)前庫存，減一，得到最新庫存，保存＂的設(shè)計，則可能會導(dǎo)致數(shù)據(jù)錯誤。比如，兩個柜臺，讀當(dāng)前庫存，都得到 100, 減一，都得到99，作為最新數(shù)據(jù)保存，保存99。最后，盡管同時賣出了兩瓶礦泉水，最后系統(tǒng)的庫存確是99。無疑是有問題的。
一個簡單的解決辦法，就是再設(shè)計一個接口表。對于有可能并發(fā)的操作，統(tǒng)一插入一條"待處理的操作指令"到此接口表中，然后單獨起一個線程，逐個處理此接口表中待處理數(shù)據(jù)。
大致步驟如下：
1. 并發(fā)處理，統(tǒng)一插入一條待處理的操作指令到此接口表中，只 insert:
insert into ti_xxx ....; --process_flag = 0
2. 單獨起一個線程，逐個讀 : ti_xxx 中未處理的數(shù)據(jù).
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 數(shù)據(jù)為已處理:
update ti_xxx set process_flag = 1 where increase_key = xxx;
其中，ti_xxx 表使用自增長主鍵，或使用 uuid 做主鍵。
?
如果只是單純的超市軟件系統(tǒng)，它的庫存計算，其實不用很實時。讓管理員人員，看當(dāng)前時間的庫存，與看5分鐘之前的庫存，從純粹的管理層面，并沒有大的區(qū)別。實際上，絕大多數(shù)系統(tǒng)，數(shù)據(jù)的實時性要求，都沒有高到需要完全實時。另一方面，此類系統(tǒng)對數(shù)據(jù)的最終準(zhǔn)確性，要求卻是非常高的。比如，客戶不太在意，9:05 分賣出一款礦泉水，只能在 9:10看到庫存減少。但客戶在意的是，9:05 分時刻賣出一款礦泉水，至少在下班后(21:00)，能看到結(jié)果。
如果我們將以上所述"單獨起一個線程"，做成每 0.5秒 運行一次的定時任務(wù)，則對于客戶來說，完全看不到影響。
?
-------------------------------
2017/6/3 補充，（2017/6/4發(fā)現(xiàn)，以下測試步驟中有不當(dāng)?shù)牡胤?#xff0c;請忽略).
有人提到，可以用純 SQL 來處理并發(fā)，使用適當(dāng)?shù)?lock 。但這樣有時并不管用。比如按如下測試，則測試出問題:
測試環(huán)境: Windows 8.1 64位 + SQL Server 2014 Express.
測試步驟:?
step_1,?創(chuàng)建數(shù)據(jù)庫 test_db1。
step_2,?運行 SQL 更改數(shù)據(jù)庫屬性:
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,創(chuàng)建表,
CREATE TABLE [dbo].[Test](
[Id] [bigint] NULL,
[Name] [varchar](50) NULL,
[Counter] [bigint] NULL
) ON [PRIMARY];
step_4,創(chuàng)建存儲過程:
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, 參數(shù)分別為:
step_5_1:?
id=1,
name='A',
step_5_2:?
id=1,
name='B',
step_6,?驗證最后數(shù)據(jù):
SELECT TOP 1000 * FROM [test_db1].[dbo].[Test];
得到兩行數(shù)據(jù):
Id Name Counter
1 A 2
1 B 1
結(jié)論:
純SQL 代碼不能起到期望的結(jié)果。
---------------------------------------------
2017/6/4 補充更正
重新測試，結(jié)論是?UPDATE...WITH(HOLDLOCK)...?可以鎖住表的 update 操作，起到"并發(fā)時順序處理"的期望結(jié)果。但并不需要使用?SET [Counter] = [Counter] + 1?這樣的語句。
測試環(huán)境: Windows 8.1 64位 + SQL Server 2014 Express.
測試步驟:?
step_1,?創(chuàng)建數(shù)據(jù)庫 test_db1。
step_2,?運行 SQL 更改數(shù)據(jù)庫屬性:
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,創(chuàng)建表,
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,創(chuàng)建存儲過程:
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 中的數(shù)值，暫時保存起來。因為 WAITFOR DELAY 之后，@@ROWCOUNT 中的數(shù)值會變。
?? ?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, 參數(shù)分別為:
step_5_1:?
id=1,
name='A',
step_5_2:?
id=1,
name='B',
step_6,?驗證最后數(shù)據(jù):
SELECT TOP 1000 * FROM [test_db1].[dbo].[Test];
得到一行數(shù)據(jù):
Id?? ?Name?? ?Counter0?? ?created_time?? ?updated_time
1?? ?B?? ?NULL?? ?2017-06-04 14:59:46.517?? ?2017-06-04 14:59:53.520
從調(diào)試運行執(zhí)行存儲過程 SQL 的消息日志中，可以看到第二次存儲過程的 update 的執(zhí)行，確實是在第一次執(zhí)行的 commit 之后。
結(jié)論:
UPDATE...WITH(HOLDLOCK)... 可以鎖住表的 update 操作，起到"并發(fā)時順序處理"的期望結(jié)果。但并不需要使用?SET [Counter] = [Counter] + 1?這樣的語句。
?
很抱歉之前的錯誤結(jié)論，可能誤導(dǎo)了一些朋友。
實測結(jié)果，數(shù)據(jù)庫屬性中，增加 :
ALTER DATABASE test_db1 SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE test_db1 SET READ_COMMITTED_SNAPSHOT ON;
可以在 UPDATE...WITH(HOLDLOCK)... 的數(shù)據(jù)庫事務(wù)執(zhí)行過程中，select 表 Test 數(shù)據(jù)。
而不加?ALLOW_SNAPSHOT_ISOLATION?+?READ_COMMITTED_SNAPSHOT，則此時 select 也堵塞。但 update?Test 表都堵塞。
轉(zhuǎn)載于:https://www.cnblogs.com/EasyLive2006/p/7630163.html
總結(jié)
以上是生活随笔為你收集整理的数据库存在即更新的高并发处理 - 转的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： C#.NET常见问题(FAQ)-命名空间
- 下一篇： java登录界面