34,838
社区成员




begin transaction
begin try
declare @count int
select @count = 1 from [table] where name = @name
if @count = 1
begin
update [table] set xx=xx where name=@name
end
else
begin
insert into [table]([xx],[xxx],[xxxx])values(@xx,@xxx,@xxxx);
end
commit transaction
end try
begin catch
rollback transaction
end catch
-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable
(ExistingCode nchar(3),
ExistingName nvarchar(50),
ExistingDate datetime,
ActionTaken nvarchar(10),
NewCode nchar(3),
NewName nvarchar(50),
NewDate datetime
);
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar(3),
@Name nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
MERGE Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';
SELECT * FROM #MyTempTable;
-- Cleanup
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');
DROP TABLE #MyTempTable;
GO
-- 直接拿#2语句(也只能这样写)
INSERT INTO [table]([xx],[xxx],[xxxx])
SELECT @xx,@xxx,@xxxx
WHERE NOT EXISTS(SELECT 1 FROM [table] WHERE name=@name)
IF @@ROWCOUNT = 0
BEGIN
update [table] set xx=xx where name=@name
END
INSERT INTO [table]([xx],[xxx],[xxxx])
SELECT @xx,@xxx,@xxxx
WHERE NOT EXISTS(SELECT 1 FROM [table] WHERE name=@name)
情况一:
select @count = 1 from [table] where name = @name
update [table] set xx=xx where name=@name
首先是查询,查询肯定占用共享锁,接着再更新,锁必定升级.
并发量大的时候,2个以上的用户同时读取 [table] 表上的数据,多个用户对改行数据占用共享锁。但更新只能有一个,其中一个将进行锁升级,而其他又不释放共享锁,都等着升级锁,这样就导致死锁发生。如果name为聚集索引,那样会好一些,但是这种发生的可能还是存在的,只要更大并发。
情况二:
select @count = 1 from [table] where name = @name
insert into [table]([xx],[xxx],[xxxx])values(@xx,@xxx,@xxxx);
这种情况也是同上面的说明一样,只是不发生死锁,但可能重复数据。如多个用户同时并发,都查询了表[table],那判断的时候,这部分并发的用户都能获取到查询返回值,所以对用户来说都没有获取到数据,都将插入数据,这就重复了。
(虽然楼主的脚本看不出名字是怎样获取的,就按正常的思路解释吧)
这种情况的解决办法都这样:
对于第一条查询时(select @count = 1 from [table] where name = @name),给它增加一个锁,并保持到事物结束。如果查询的这个值 name = @name 是对所有用户来说的,那就只能序列化操作了!!~直接加表锁吧 ,如:
select @count = 1 from [table] WITH(TABLOCKX) where name = @name
具体加什么锁楼主按实际情况斟酌一下吧