|
|
探讨存在就更新七种方案
首先我们来创建测试表
IF OBJECT_ID('Test') IS NOT NULL
DROP TABLE TestCREATE TABLE Test
(
Id int,
Name nchar(100), [Counter] int,primary key (Id), unique (Name)
);GO
解决方案一（开启事务）
我们统一创建存储过程通过来SQLQueryStress来测试并发情况，我们来看第一种情况。
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
BEGIN TRANSACTION
IF EXISTS ( SELECT 1
FROM Test WHERE Id = @Id ) UPDATE Test SET [Counter] = [Counter] + 1
WHERE Id = @Id; ELSE
INSERT Test
( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMITGO
同时开启100个线程和200个线程出现插入重复键的几率比较少还是存在。
解决方案二（降低隔离级别为最低隔离级别UNCOMMITED）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
IF EXISTS ( SELECT 1
FROM Test WHERE Id = @Id ) UPDATE Test SET [Counter] = [Counter] + 1
WHERE Id = @Id; ELSE
INSERT Test
( Id, Name, [Counter] ) VALUES ( @Id, @name, 1 ); COMMITGO
此时问题依旧和解决方案一无异（如果降低级别为最低隔离级别，如果行记录为空，前一事务如果未进行提交，当前事务也能读取到该行记录为空，如果当前事务插入进去并进行提交，此时前一事务再进行提交此时就会出现插入重复键问题）
解决方案三（提升隔离级别为最高级别SERIALIZABLE）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
IF EXISTS ( SELECT 1
FROM dbo.Test WHERE Id = @Id ) UPDATE dbo.Test SET [Counter] = [Counter] + 1
WHERE Id = @Id; ELSE
INSERT dbo.Test
( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMITGO
在这种情况下更加糟糕，直接到会导致死锁
此时将隔离级别提升为最高隔离级别会解决插入重复键问题，但是对于更新来获取排它锁而未提交，而此时另外一个进程进行查询获取共享锁此时将造成进程间相互阻塞从而造成死锁，所以从此知最高隔离级别有时候能够解决并发问题但是也会带来死锁问题。
解决方案四（提升隔离级别+良好的锁）
此时我们再来在添加最高隔离级别的基础上增添更新锁，如下：
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
IF EXISTS ( SELECT 1
FROM dbo.Test WITH(UPDLOCK) WHERE Id = @Id ) UPDATE dbo.Test SET [Counter] = [Counter] + 1
WHERE Id = @Id; ELSE
INSERT dbo.Test
( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMITGO
运行多次均未发现出现什么异常，通过查询数据时使用更新锁而非共享锁，这样的话一来可以读取数据但不阻塞其他事务，二来还确保自上次读取数据后数据未被更改，这样就解决了死锁问题。貌似这样的方案是可行得，如果是高并发不知是否可行。
解决方案五（提升隔离级别为行版本控制SNAPSHOT）
ALTER DATABASE UpsertTestDatabaseSET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE UpsertTestDatabaseSET READ_COMMITTED_SNAPSHOT ONGO IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
BEGIN TRANSACTION
IF EXISTS ( SELECT 1
FROM dbo.Test WHERE Id = @Id ) UPDATE dbo.Test SET [Counter] = [Counter] + 1
WHERE Id = @Id; ELSE
INSERT dbo.Test
( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMITGO
上述解决方案也会出现插入重复键问题不可取。
解决方案六（提升隔离级别+表变量）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) DECLARE @updated TABLE ( i INT );
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION
UPDATE Test SET [Counter] = [Counter] + 1
OUTPUT DELETED.Id INTO @updated
WHERE Id = @Id;
IF NOT EXISTS ( SELECT i FROM @updated ) INSERT INTO Test
( Id, Name, counter ) VALUES ( @Id, @Name, 1 ); COMMITGO
经过多次认证也是零错误，貌似通过表变量形式实现可行。
解决方案七（提升隔离级别+Merge）
通过Merge关键来实现存在即更新否则则插入，同时我们应该注意设置隔离级别为 SERIALIZABLE 否则会出现插入重复键问题，代码如下：
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) SET TRAN ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
MERGE Test AS [target]
USING
( SELECT @Id AS Id
) AS source ON source.Id = [target].Id WHEN MATCHED THEN
UPDATE SET
[Counter] = [target].[Counter] + 1
WHEN NOT MATCHED THEN
INSERT ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMITGO
多次认证无论是并发100个线程还是并发200个线程依然没有异常信息。
总结
本节我们详细讨论了在并发中如何处理存在即更新，否则即插入问题的解决方案，目前来讲以上三种方案可行。
解决方案一（最高隔离级别 + 更新锁）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
BEGIN TRANSACTION;
UPDATE dbo.Test WITH ( UPDLOCK, HOLDLOCK ) SET [Counter] = [Counter] + 1
WHERE Id = @Id;
IF ( @@ROWCOUNT = 0 ) BEGIN
INSERT dbo.Test
( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); END
COMMITGO
解决方案二（最高隔离级别 + 表变量）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) DECLARE @updated TABLE ( i INT );
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION
UPDATE Test SET [Counter] = [Counter] + 1
OUTPUT DELETED.id INTO @updated
WHERE id = @id;
IF NOT EXISTS ( SELECT i FROM @updated ) INSERT INTO Test
( Id, Name, counter ) VALUES ( @Id, @Name, 1 ); COMMITGO
解决方案三（最高隔离级别 + Merge）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) SET TRAN ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
MERGE Test AS [target]
USING
( SELECT @Id AS Id
) AS source ON source.Id = [target].Id WHEN MATCHED THEN
UPDATE SET
[Counter] = [target].[Counter] + 1
WHEN NOT MATCHED THEN
INSERT ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMITGO
暂时只能想到这三种解决方案，个人比较推荐方案一和方案三， 请问您有何高见，请留下您的评论若可行，我将进行后续补充。
2017-06-03更新
本博文的评论非常精彩，同时对于小菜的我又重新学习了下存在即更新反之则插入的解决方案。本文重新更新已经过了两天，期间我是一直在看这方面的东西更加深入的理解有些基础方面的东西还是说的太笼统并且是我自身不是很理解而导致，菜不可怕，可怕的是还不深入学习自认为自己的是对的，你说呢。
首先我们得理解UPDLOCK和HOLDLOCK锁的作用是什么，HOLDLOCK类似于SERIALIZABLE隔离级别，对于共享锁我们是可以读，但是不能进行更新和删除和插入直到当前并发事务完成，而UPDLOCK园中博文的解释：是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。当我们用它来读取记录时可以对取到的记录加上更新锁，从而加上锁的记录在其它的线程中是不能更改的只能等本线程的事务结束后才能更改。通俗易懂点说，它不会阻塞并发的查询和插入操作，但是会阻塞更新或者删除对于当前事务查询出的数据，当查询到该数据存在时则有更新锁切换到排它锁。所以对于上述结尾总结的三种解决方案，我们再来阐述下。
解决方案一（HOLDLOCK）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
BEGIN TRANSACTION;
UPDATE dbo.Test WITH ( HOLDLOCK ) SET [Counter] = [Counter] + 1
WHERE Id = @Id;
IF ( @@ROWCOUNT = 0 ) BEGIN
INSERT dbo.Test
( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); END
COMMITGO
如果我们未加上HOLDLOCK锁提示，虽然UPDATE会获取排它锁，但是排它锁不会持续到事务结束一直保持着所以会导致插入重复键的问题，当我们加上HOLDLOCK锁提示上述也说到类似悲观并发中的最高隔离级别，该锁提示一直会持续到事务结束，当有并发请求过来时，若此时查询到数据存在则会进行更新操作但是事务还未进行提交，此时其他请求将会也查到该行记录存在，但是会被当前的事务更新操作锁阻塞，若此时查询到数据不存在时同理如此。
解决方案二（UPDLOCK + HOLDLOCK）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
BEGIN TRANSACTION
IF EXISTS ( SELECT 1
FROM dbo.Test WITH(UPDLOCK, HOLDLOCK) WHERE Id = @Id ) UPDATE dbo.Test SET [Counter] = [Counter] + 1
WHERE Id = @Id; ELSE
INSERT dbo.Test
( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMITGO
对于上述查询对比第一种解决方案我们加上了UPDLOCK更新锁代替SELECT的共享锁，目的是当所传递的变量Id所查询的行记录不存在时不会导致阻塞，让其进行插入，也就是说不阻塞其他事务的插入并确保自上次以来行记录未被修改过，对于HOLDLOCK为了确保一直到事务释放锁，从而达到我们的期望。总结起来一句话，如果查询期间行记录存在则锁定的资源则查询存在的行记录上，如果查询期间行记录不存在，那么通过HOLDLOCK来获取主键上的范围锁来防止在释放锁之前插入重复键，所以UPDLOCK为了解决并发更新不阻塞其他事务查询，HOLDLOCK防止并发插入重复键。
解决方案三（SERIALIZABLE + Merge）
IF OBJECT_ID('TestPro') IS NOT NULL
DROP PROCEDURE TestPro;GO
CREATE PROCEDURE TestPro ( @Id INT )AS
DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100)) BEGIN TRANSACTION
MERGE Test WITH(SERIALIZABLE ) AS [target]
USING
( SELECT @Id AS Id
) AS source ON source.Id = [target].Id WHEN MATCHED THEN
UPDATE SET
[Counter] = [target].[Counter] + 1
WHEN NOT MATCHED THEN
INSERT ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); COMMITGO
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2018-10-20 09:37:11
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡