SQL Server并发处理存在就更新解决方案探讨
前言
本节我们来讲讲并发中最常见的情况存在即更新,在并发中若未存在行记录则插入,此时未处理好极容易出现插入重复键情况,本文我们来介绍对并发中存在就更新行记录的七种方案并且我们来综合分析最合适的解决方案。
探讨存在就更新七种方案
首先我们来创建测试表
IF OBJECT_ID( Test ) IS NOT NULL
DROP TABLE Test
CREATE TABLE Test
(
Id int,
Name nchar(100),
[Counter] int,primary key (Id),
unique (Name)
);
解决方案一(开启事务)
我们统一创建存储过程通过来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 );
COMMIT
GO
同时开启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 );
COMMIT
GO
此时问题依旧和解决方案一无异(如果降低级别为最低隔离级别,如果行记录为空,前一事务如果未进行提交,当前事务也能读取到该行记录为空,如果当前事务插入进去并进行提交,此时前一事务再进行提交此时就会出现插入重复键问题)
解决方案三(提升隔离级别为最高级别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 );
COMMIT
GO
在这种情况下更加糟糕,直接到会导致死锁
此时将隔离级别提升为最高隔离级别会解决插入重复键问题,但是对于更新来获取排它锁而未提交,而此时另外一个进程进行查询获取共享锁此时将造成进程间相互阻塞从而造成死锁,所以从此知最高隔离级别有时候能够解决并发问题但是也会带来死锁问题。
解决方案四(提升隔离级别+良好的锁)
此时我们再来在添加最高隔离级别的基础上增添更新锁,如下:
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 );
COMMIT
GO
运行多次均未发现出现什么异常,通过查询数据时使用更新锁而非共享锁,这样的话一来可以读取数据但不阻塞其他事务,二来还确保自上次读取数据后数据未被更改,这样就解决了死锁问题。貌似这样的方案是可行得,如果是高并发不知是否可行。
解决方案五(提升隔离级别为行版本控制SNAPSHOT)
ALTER DATABASE UpsertTestDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE UpsertTestDatabase
SET READ_COMMITTED_SNAPSHOT ON
GO
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 );
COMMIT
GO
上述解决方案也会出现插入重复键问题不可取。
解决方案六(提升隔离级别+表变量)
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 );
COMMIT
GO
经过多次认证也是零错误,貌似通过表变量形式实现可行。
解决方案七(提升隔离级别+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 );
COMMIT
GO
多次认证无论是并发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
COMMIT
GO
暂时只能想到这三种解决方案,个人比较推荐方案一和方案三, 请问您有何高见,请留下您的评论若可行,我将进行后续补充。
解决方案二(最高隔离级别 + 表变量)
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 );
COMMIT
GO
解决方案三(最高隔离级别 + 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 );
COMMIT
暂时只能想到这三种解决方案,个人比较推荐方案一和方案三, 请问您有何高见,请留下您的评论若可行,我将进行后续补充。
本篇文章到此结束,如果您有相关技术方面疑问可以联系我们技术人员远程解决,感谢大家支持本站!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 SQL Server并发处理存在就更新解决方案探讨
相关文章
- T-SQL教程_sql server 2008使用
- 怎样在SQL Server数据库执行sql脚本?
- SQL手工注入漏洞测试(Sql Server数据库)
- T-sql语句修改SQL Server数据库逻辑名、数据库名、物理名的方法
- SQL Server 提取数字、提取英文、提取中文的sql语句
- Sql Server中通过sql命令获取cpu占用及产生锁的sql
- 复制SQL Server 主从复制:实现数据安全可靠的分布式备份(sqlserver主从)
- Microsoft SQL Server 密码破解技术实现(mssql密码破解)
- SQL Server 运维:保障服务的稳定性(sqlserver运维)
- 电脑中搭载的SQL Server改变着我们的生活(电脑的sqlserver)
- 安装SQL Server 2012确保数据库安全运行(安装sqlserver2012)
- serverOracle 与 SQL Server之间的友谊连接(oracle连接sql)
- SQL Server出现错误0:解决方案(sqlserver错误0)
- SQL Server数据库与事务处理中的回滚操作(sqlserver退回)
- 字符串解决SQL Server中超长字符串的挑战(sqlserver超长)
- SQL Server表锁——实现并发控制的有效方法(sqlserver表锁定)
- SQL Server自增:妙不可言!(sqlserver自增满)
- SQL Server分页技术:有效检索大量数据(sqlserver 翻页)
- SQL Server后缀:启用智能保护(sqlserver的后缀)
- SQL Server:可持续优化的数据库利器(sqlServer的名称)
- 用户SQL Server 新增用户注册指南(sqlserver注册新)
- sql体验SQL Server:本周SQLServer之旅(sqlserver 本周)
- 本安装SQL Server官方版本,快速加速你的执行力(sqlserver官方版)
- 3将SQL Server数据采用TP3框架进行Web应用开发(sqlserver和tp)
- 级技术引领:SQL Server 升级/降级指南(sqlserver升降)
- :SQL Server免安装启动:快速进入计算机世界(sqlserver免安装)
- 使用SQL Server代码实现数据表变更(sqlserver代码表)
- 还原SQL Server一键还原:简易快捷数据恢复(sqlserver 一键)
- SQL Server解决数据库管理问题的最佳方案(sqlserver :r)
- SQL Server新建登录名教程(sqlserver新建登录名)
- serverMySQL 与 SQL Server 的比较与选择(mysql 和 sql)
- Sql学习第一天——SQL练习题(建表/sql语句)