为了账号安全,请及时绑定邮箱和手机立即绑定

检查是否存在行,否则插入

/ 猿问

检查是否存在行,否则插入

BIG阳 2019-11-21 15:20:02

我需要编写一个T-SQL存储过程来更新表中的一行。如果该行不存在,则将其插入。所有这些步骤都由事务包装。


这是用于预订系统的,因此它必须是原子性和可靠的。如果已提交交易并预订了航班,则必须返回true。


我是T-SQL的新手,不确定如何使用@@rowcount。这就是我到目前为止所写的。我在正确的道路上吗?我相信这对您来说是一个简单的问题。


-- BEGIN TRANSACTION (HOW TO DO?)


UPDATE Bookings

 SET TicketsBooked = TicketsBooked + @TicketsToBook

 WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)


-- Here I need to insert only if the row doesn't exists.

-- If the row exists but the condition TicketsMax is violated, I must not insert 

-- the row and return FALSE


IF @@ROWCOUNT = 0 

BEGIN


 INSERT INTO Bookings ... (omitted)


END


-- END TRANSACTION (HOW TO DO?)


-- Return TRUE (How to do?)


查看完整描述

3 回答

?
largeQ

看一下MERGE命令。你可以做UPDATE,INSERT和DELETE在一个声明。


这是一个使用上的有效实现MERGE

-在进行更新之前检查飞行是否已满,否则进行插入。


if exists(select 1 from INFORMATION_SCHEMA.TABLES T 

              where T.TABLE_NAME = 'Bookings') 

begin

    drop table Bookings

end

GO


create table Bookings(

  FlightID    int identity(1, 1) primary key,

  TicketsMax    int not null,

  TicketsBooked int not null

)

GO


insert  Bookings(TicketsMax, TicketsBooked) select 1, 0

insert  Bookings(TicketsMax, TicketsBooked) select 2, 2

insert  Bookings(TicketsMax, TicketsBooked) select 3, 1

GO


select * from Bookings

然后 ...


declare @FlightID int = 1

declare @TicketsToBook int = 2


--; This should add a new record

merge Bookings as T

using (select @FlightID as FlightID, @TicketsToBook as TicketsToBook) as S

    on  T.FlightID = S.FlightID

      and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)

  when matched then

    update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook

  when not matched then

    insert (TicketsMax, TicketsBooked) 

    values(S.TicketsToBook, S.TicketsToBook);


select * from Bookings


查看完整回答
反对 回复 2019-11-21
?
缥缈止盈

我为每个航班假设一行?如果是这样的话:


IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)

BEGIN

    --UPDATE HERE

END

ELSE

BEGIN

   -- INSERT HERE

END

我假设我说的是,因为您的处事方式可能会超额预定机票,因为当最多容纳10张机票并且您预订20张机票时,它将插入新的一行。


查看完整回答
反对 回复 2019-11-21
?
拉风的咖菲猫

在测试行是否存在时传递updlock,rowlock,holdlock提示。


begin tran /* default read committed isolation level is fine */


if not exists (select * from Table with (updlock, rowlock, holdlock) where ...)

    /* insert */

else

    /* update */


commit /* locks are released here */

updlock提示会强制查询对行进行更新锁定(如果该更新已存在),从而防止其他事务修改该行,直到您提交或回滚为止。


holdlock提示会强制查询进行范围锁定,从而防止其他事务添加符合您的过滤条件的行,直到您提交或回滚为止。


行锁提示会强制将粒度锁定到行级别,而不是默认页面级别,因此您的事务将不会阻止其他事务尝试更新同一页面中不相关的行(但要注意减少争用和增加页之间的权衡)锁定开销-您应该避免在单个事务中使用大量的行级锁定)。


有关更多信息,请参见http://msdn.microsoft.com/zh-cn/library/ms187373.aspx。


请注意,执行锁的语句是执行锁的语句-调用begin tran并不能使您免受在锁定某事物之前发生的另一事务对锁的攻击。您应该通过尽快提交事务(延迟获取,提前释放)来尝试使SQL保持锁定的时间最短。


请注意,如果您的PK是bigint,则行级锁的有效性可能会降低,因为SQL Server上的内部散列会退化为64位值(不同的键值可能会散列到相同的锁id)。


查看完整回答
反对 回复 2019-11-21

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信