存在即更新反之插入的三种防唯一键冲突和死锁的写法

[转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

写法一：

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

写法二：

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

写法三：

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