存在即更新反之插入的三种防唯一键冲突和死锁的写法
[转http://www.cnblogs.com/nzperfect/archive/2011/09/27/2193143.html]
存在即更新,反之插入的需求是很常见的,很多人没有注意并发的问题,高并发下为了避免唯一键冲突和死锁情况,下面提供三种写法,最后一个是sql server 2008及以后版本适用。
示例表为:
use tempdb
go
create table tb_1 (id int identity primary key,a varchar(50),dt datetime default getdate())
go
go
create table tb_1 (id int identity primary key,a varchar(50),dt datetime default getdate())
go
写法一:
begin tran
if exists (select * from tb_1 with (updlock,serializable) where id=100)
begin
update tb_1 set a='a100' where id=100
end
else
begin
insert tb_1 (a) values ('a100')
end
commit tran
if exists (select * from tb_1 with (updlock,serializable) where id=100)
begin
update tb_1 set a='a100' where id=100
end
else
begin
insert tb_1 (a) values ('a100')
end
commit tran
写法二:
begin tran
update tb_1 with (serializable) set a='a100' where id = 100
if @@rowcount = 0
begin
insert tb_1 (a) values ('a100')
end
commit tran
update tb_1 with (serializable) set a='a100' where id = 100
if @@rowcount = 0
begin
insert tb_1 (a) values ('a100')
end
commit tran
写法三:
begin tran
merge tb_1 with(serializable) as a
using(select 100 as id) as b on a.id=b.id
when matched then update set a='a100' when not matched then insert (a) values ('a100');
commit tran
merge tb_1 with(serializable) as a
using(select 100 as id) as b on a.id=b.id
when matched then update set a='a100' when not matched then insert (a) values ('a100');
commit tran