我自己写了一段存储过程,主要是控制并发的,也不知道行不行得通。求高手们指点一二
CREATE PROCEDURE Savejbxx
@save varchar(20), --判断是添加还是修改标志
@cysy varchar(50),
@cydid varchar(50),
@ypid varchar(50),
@cpmc varchar(50),
@ggxh varchar(50),
@sb varchar(50),
@dwlb varchar(50),
@cpdj varchar(50),
@cysl varchar(50),
@cyjs varchar(50),
@cyman varchar(50),
@cyadress varchar(50),
@chuchangid varchar(50),
@cclb varchar(50),
@cydate varchar(50),
@sydate varchar(50),
@ywshi varchar(50),
@ydbgdate varchar(50),
@fyzt varchar(50),
@jyadress varchar(50),
@sjdw varchar(50),
@sjdwadress varchar(50),
@sjdwfr varchar(50),
@tel varchar(50),
@scdw varchar(50),
@scdwadress varchar(50),
@scdwfw varchar(50),
@bzname varchar(50),
@jyxm varchar(50),
@ip varchar(50) --客户端标志
as
set nocount on--设置游标
set transaction isolation level serializable--设置事务级别
set LOCK_TIMEOUT 2000--设定超时时间
begin transaction
select * from jbxx with (HOLDLOCK)--加锁
select * from jybg with (HOLDLOCK)--加锁
if @save='1' --修改记录并删除现有检验项目
begin
update jbxx set cysy=@cysy,ypid=@ypid,cmpc=@cmpc,ggxh=@ggxh,sb=@sb,dwlb=@dwlb,cpdj=@cpdj,cysl=@cysl,cyjs=@cyjs,cyman=@cyman,cyadress=@cyadress,chuchangid=@chuchangid,cclb=@cclb,cydate=@cydate,sydate=@sydate,ywshi=@ywshi,ydbgdate=@ydbgdate,fyzt=@fyzt,jyadress=@jyadress,sjdw=@sjdw,sjdwadress=@sjdwadress,sjdwfr,tel=@tel,scdw=@scdw,scdwadress=@scdwadress,scdwfw=@scdwfw,bzname=@bzname,jyxm=@jyxm where cydid=@cydid
delete from jybg where cydid=@cydid
insert into jybg (cydid,id,jyxm,jldw,bzyq,fangshi,hege,nohege,trueorfalse,bzhao) select @cydid,id,bzxm,dw,bzyq,fangshi,hege,nohege,lk ,bzhao from lsjy where ip=@ip
end
else if @save='2' --修改记录但不删除现有检验项目
begin
update jbxx set cysy=@cysy,ypid=@ypid,cmpc=@cmpc,ggxh=@ggxh,sb=@sb,dwlb=@dwlb,cpdj=@cpdj,cysl=@cysl,cyjs=@cyjs,cyman=@cyman,cyadress=@cyadress,chuchangid=@chuchangid,cclb=@cclb,cydate=@cydate,sydate=@sydate,ywshi=@ywshi,ydbgdate=@ydbgdate,fyzt=@fyzt,jyadress=@jyadress,sjdw=@sjdw,sjdwadress=@sjdwadress,sjdwfr,tel=@tel,scdw=@scdw,scdwadress=@scdwadress,scdwfw=@scdwfw,bzname=@bzname,jyxm=@jyxm where cydid=@cydid
end
else --添加记录
begin
insert into jbxx(cysy,cydid,ypid,cpmc,ggxh,sb,dwlb,cpdj,cysl,cyjs,cyman,cyadress,chuchangid,cclb,cydate,sydate,ywshi,ydbgdate,fyzt,jyadress,sjdw,sjdwadress,sjdwfr,tel,scdw,scdwadress,scdwfw,bzname,jyxm) values (@cysy,@cydid,@ypid,@cpmc,@ggxh,@sb,@dwlb,@cpdj,@cysl,@cyjs,@cyman,@cyadress,@chuchangid,@cclb,@cydate,@sydate,@ywshi,@ydbgdate,@fyzt,@jyadress,@sjdw,@sjdwadress,@sjdwfr,@tel,@scdw,@scdwadress,@scdwfw,@bzname,@jyxm)
insert into jybg (cydid,id,jyxm,jldw,bzyq,fangshi,hege,nohege,trueorfalse,bzhao) select @cydid,id,bzxm,dw,bzyq,fangshi,hege,nohege,lk ,bzhao from lsjy where ip=@ip
end
if @@error<>0
begin
rollback tran
end
else
begin
commit tran
endset transaction isolation level read committed
set nocount off
return -1
GO
CREATE PROCEDURE Savejbxx
@save varchar(20), --判断是添加还是修改标志
@cysy varchar(50),
@cydid varchar(50),
@ypid varchar(50),
@cpmc varchar(50),
@ggxh varchar(50),
@sb varchar(50),
@dwlb varchar(50),
@cpdj varchar(50),
@cysl varchar(50),
@cyjs varchar(50),
@cyman varchar(50),
@cyadress varchar(50),
@chuchangid varchar(50),
@cclb varchar(50),
@cydate varchar(50),
@sydate varchar(50),
@ywshi varchar(50),
@ydbgdate varchar(50),
@fyzt varchar(50),
@jyadress varchar(50),
@sjdw varchar(50),
@sjdwadress varchar(50),
@sjdwfr varchar(50),
@tel varchar(50),
@scdw varchar(50),
@scdwadress varchar(50),
@scdwfw varchar(50),
@bzname varchar(50),
@jyxm varchar(50),
@ip varchar(50) --客户端标志
as
set nocount on--设置游标
set transaction isolation level serializable--设置事务级别
set LOCK_TIMEOUT 2000--设定超时时间
begin transaction
select * from jbxx with (HOLDLOCK)--加锁
select * from jybg with (HOLDLOCK)--加锁
if @save='1' --修改记录并删除现有检验项目
begin
update jbxx set cysy=@cysy,ypid=@ypid,cmpc=@cmpc,ggxh=@ggxh,sb=@sb,dwlb=@dwlb,cpdj=@cpdj,cysl=@cysl,cyjs=@cyjs,cyman=@cyman,cyadress=@cyadress,chuchangid=@chuchangid,cclb=@cclb,cydate=@cydate,sydate=@sydate,ywshi=@ywshi,ydbgdate=@ydbgdate,fyzt=@fyzt,jyadress=@jyadress,sjdw=@sjdw,sjdwadress=@sjdwadress,sjdwfr,tel=@tel,scdw=@scdw,scdwadress=@scdwadress,scdwfw=@scdwfw,bzname=@bzname,jyxm=@jyxm where cydid=@cydid
delete from jybg where cydid=@cydid
insert into jybg (cydid,id,jyxm,jldw,bzyq,fangshi,hege,nohege,trueorfalse,bzhao) select @cydid,id,bzxm,dw,bzyq,fangshi,hege,nohege,lk ,bzhao from lsjy where ip=@ip
end
else if @save='2' --修改记录但不删除现有检验项目
begin
update jbxx set cysy=@cysy,ypid=@ypid,cmpc=@cmpc,ggxh=@ggxh,sb=@sb,dwlb=@dwlb,cpdj=@cpdj,cysl=@cysl,cyjs=@cyjs,cyman=@cyman,cyadress=@cyadress,chuchangid=@chuchangid,cclb=@cclb,cydate=@cydate,sydate=@sydate,ywshi=@ywshi,ydbgdate=@ydbgdate,fyzt=@fyzt,jyadress=@jyadress,sjdw=@sjdw,sjdwadress=@sjdwadress,sjdwfr,tel=@tel,scdw=@scdw,scdwadress=@scdwadress,scdwfw=@scdwfw,bzname=@bzname,jyxm=@jyxm where cydid=@cydid
end
else --添加记录
begin
insert into jbxx(cysy,cydid,ypid,cpmc,ggxh,sb,dwlb,cpdj,cysl,cyjs,cyman,cyadress,chuchangid,cclb,cydate,sydate,ywshi,ydbgdate,fyzt,jyadress,sjdw,sjdwadress,sjdwfr,tel,scdw,scdwadress,scdwfw,bzname,jyxm) values (@cysy,@cydid,@ypid,@cpmc,@ggxh,@sb,@dwlb,@cpdj,@cysl,@cyjs,@cyman,@cyadress,@chuchangid,@cclb,@cydate,@sydate,@ywshi,@ydbgdate,@fyzt,@jyadress,@sjdw,@sjdwadress,@sjdwfr,@tel,@scdw,@scdwadress,@scdwfw,@bzname,@jyxm)
insert into jybg (cydid,id,jyxm,jldw,bzyq,fangshi,hege,nohege,trueorfalse,bzhao) select @cydid,id,bzxm,dw,bzyq,fangshi,hege,nohege,lk ,bzhao from lsjy where ip=@ip
end
if @@error<>0
begin
rollback tran
end
else
begin
commit tran
endset transaction isolation level read committed
set nocount off
return -1
GO
select * from jbxx with (HOLDLOCK)--加锁
select * from jybg with (HOLDLOCK)--加锁set transaction isolation level read committed
set nocount off
return -1
这一段会不会造成死锁?因为我锁了两个表。而且加锁的语句对不对呢?