真人真事改編-訂票系統問題解決實戰

jeffluo

转一个本ID认为不错的项目给大家看看，来源https://www.facebook.com/groups/222546864546011/

用waitfor delay '00:00:01.50'来摸拟DB慢时会出现超卖的情况
为了解决这个超卖的问题加入對超賣的判斷：

在"可用票量"表中加一個字段用來記錄已售票量
create table 可用票量
(ticket_id        int identity primary key, --代號
event_code        nvarchar(36) not null unique, --活動代碼
event_name nvarchar(125), --活動名稱
event_amt int --可用量
event_buy int --已售量
)
go
DECLARE @dml TABLE
( before varchar(10),
after varchar(10)
)
然后在[USP_索票]的存儲過程中加入是否超賣的判斷，如超賣就rollback
update [dbo].[可用票量] set [event_buy] = [event_buy]+@request_ticket
OUTPUT deleted.event_buy,inserted.event_buy into @DML
where event_code=@event_code
if (SELECT [after] FROM @dml)>@total_ticket
begin
rollback transaction
return
end





该项目是设计一个简单的订票系统，SQL如下：
--HKDB是一个空的DB
use HKDB
go
--0可用 1已用
drop table if exists 索票記錄
if exists(select * from sys.tables where name='索票記錄') drop table 索票記錄;
drop table if exists 可用票量
if exists(select * from sys.tables where name='可用票量') drop table 可用票量;
drop proc  if exists [USP_索票]
if exists(select * from sys.objects where name='USP_索票') drop proc USP_索票;
go
--可用票資料表
create table 可用票量
(ticket_id                int identity primary key, --代號  
event_code                nvarchar(36) not null unique, --活動代碼
event_name     nvarchar(125), --活動名稱
event_amt      int --可用量
)
go
--新增驗證票
insert into 可用票量(event_code,event_name,event_amt)
select '20160805001','售票範例之簡易版',400
go
--------------------------------
--可用票量-->APP--PROC-->索票記錄--
--------------------------------

--訂購票記錄資料表
create table 索票記錄
(reserve_id                int identity        primary key, --索票記錄
event_code                nvarchar(36) references 可用票量(event_code), --活動代碼
reserve_amt        int ,        --索票數量
reserve_for        nvarchar(36) --索票人員
)
go
----------------------------------------
--建立預存程序，根據活動代碼、使用者索取指定票量
-----------------------------------------
create proc [dbo].[USP_索票]
    --活動代碼                   --索票人員
        --索票數量                   --可用量
    @event_code nvarchar(36),        @user_name nvarchar(20),
        @request_ticket int     ,        @total_ticket int
AS
begin try
begin transaction
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  
        declare @sale_ticket int  --已經賣出量
        declare @free_ticket int  --計算可用量
        set     @sale_ticket = (select isnull(sum(reserve_amt),0)
                                from 索票記錄 where event_code=@event_code)       
        --可能發生資料庫緩慢       
        waitfor delay '00:00:01.50'
        if      @sale_ticket >= @total_ticket                 --已經超賣
                set @free_ticket= 0
        else if @sale_ticket+@request_ticket <= @total_ticket --數量足夠
                set @free_ticket = @request_ticket
        else
                set @free_ticket = @total_ticket-@sale_ticket     --省下零星
    --新增索票記錄資訊
        insert 索票記錄(event_code,reserve_amt,reserve_for)
        values (@event_code,@free_ticket,@user_name)

        select (@sale_ticket+@free_ticket) as [已取量],@free_ticket as [可用量]
commit transaction
end try
begin catch
  rollback transaction
  ;throw
end catch
GO

--驗證--
select * from 可用票量
select * from 索票記錄

select sum(reserve_amt) as [已經賣出量],
       isnull(sum(reserve_amt),0) as [已經賣出量處理NULL]
from 索票記錄

declare @code nvarchar(36)='20160805001'
declare @total int = (select event_amt
                      from 可用票量 where  event_code= @code)
exec [dbo].[USP_索票] @event_code= @code ,@user_name='LEWIS',
                     @request_ticket=2 , @total_ticket=@total


select * from 可用票量
select * from 索票記錄

zhuangzhenlve