会发生死锁

[kaisichen]

请教各位高手:如果同时几个线程执行下面的存储过程就会发生死锁,请各位指点,急~~~~!谢谢

create procedure [dbo].[p_OperationSQLprocedure]
@recordid int,
@cardid bigint,
@datetime varchar(50),
@readerid int,
@backbyte int,
@money bigint,
@calcid int,
@machineid int,
@recordtype int,
@PeerIP varchar(20),
@PeerPort int,
@msgid int output,
@msg varchar output
as
declare
@retrecordid int,
@retbackbyte int,
@retmoney real,
@retcardid bigint,
@msg01 varchar(16),
@msg02 varchar(16),
@state int


declare @days smalldatetime,
@startday smalldatetime,
@endday smalldatetime,
@time datetime,
@xftimes int

set @xftimes=1
set @days=cast(convert(varchar(10),getdate(),120) as smalldatetime)
set @time=cast(substring(convert(varchar(19),getdate(),114),1,8) as datetime)



if @recordtype=7
begin
declare @addrstr varchar(5),
@init_data1 varchar(16),
@init_data2 varchar(16),
@len int
set @addrstr=cast(@machineid as varchar(5))
set @len=len(@addrstr)
if @len=0 set @addrstr = '00000'
if @len=1 set @addrstr = '0000'+@addrstr
if @len=2 set @addrstr = '000'+@addrstr
if @len=3 set @addrstr = '00'+@addrstr
if @len=4 set @addrstr = '0'+@addrstr
if @addrstr=''set @addrstr='00000'

select @retrecordid=0,@retbackbyte=0,@retmoney=0,@retcardid=0
set @init_data1=right(replace(replace(replace(convert(varchar(20), getdate(), 120),'-',''),':',''),' ',''),12)+left(@addrstr,4)
set @init_data2=right(@addrstr,1)+'0'
select @state =0,@msg01 =@init_data1,@msg02=@init_data2

select @state as state,@retrecordid as retrecordid,@retbackbyte as retbackbyte,@retmoney as retmoney,@retcardid as retcardid,@msg01 as msg01,@msg02 as msg02
end else
if @recordtype=6
begin
select @retrecordid=0,@retbackbyte=0,@retmoney=0,@retcardid=0
select @state =0,@msg01 = '工号=',@msg02='姓名:'
select @state as state,@retrecordid as retrecordid,@retbackbyte as retbackbyte,@retmoney as retmoney,@retcardid as retcardid,@msg01 as msg01,@msg02 as msg02
end else
if @recordtype in (0,1,2,3)
begin
declare @bh int,@bc char(2),@bmmc char(32),@bmbh int,@xm char(20),@empno varchar(10),@kh varchar(20)
declare @min int
select @bh=a.bh,@xm=a.bz,@bmmc=a.bm,@empno=a.xm,@bmbh=b.bmbh,@kh=isnull(b.xingqu,'000000')
from xf_fk a join employee b
on a.xm=b.xm
where a.idbh=@CardID and (a.kzt='1' or a.kzt='4')
if @bh<=0 or @bh is null
begin
SELECT @STATE =4,@MSG01 = ' 无权 ',@MSG02='未注册 '
end
else
Begin
set @bc=isnull((select dh from kqjda where jh = @MachineID),'01')
set @min=isnull((select buf from kqjda where jh=@MachineID),10)
if not exists(select * from yskq with(nolock) where xm=@empno and rq=left(@Datetime,10) and abs(datediff(minute,sj,substring(@Datetime,11,10)))<@min and bc=@bc)
begin
insert into yskq with(rowlock)(bh,rq,sj,bc,xm,bmmc,bmbh,user1,jh)
values(@bh,left(@Datetime,10),substring(@Datetime,11,10),@bc,@empno,@bmmc,@bmbh,@kh,convert(varchar(10),@MachineID))


SELECT @STATE =0,@MSG01 =@xm ,@MSG02=CONVERT(varchar(20), getdate(),120)
end
else
SELECT @STATE =4,@MSG01 = ' 无权 ',@MSG02='重复

End
select @state as state,@retrecordid as retrecordid,@retbackbyte as retbackbyte,@retmoney as retmoney,@retcardid as retcardid,@msg01 as msg01,@msg02 as msg02

end

[dawugui]

那就要注意加锁解锁.

1 如何锁一个表的某一行

A 连接中执行

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

begin tran

select * from tablename with (rowlock) where id=3

waitfor delay '00:00:05'

commit tran

B连接中如果执行

update tablename set colname='10' where id=3 --则要等待5秒

update tablename set colname='10' where id<>3 --可立即执行

2 锁定数据库的一个表

SELECT * FROM table WITH (HOLDLOCK) 


注意: 锁定数据库的一个表的区别

SELECT * FROM table WITH (HOLDLOCK) 
其他事务可以读取表，但不能更新删除

SELECT * FROM table WITH (TABLOCKX) 
其他事务不能读取表,更新和删除

[kaisichen]

前台程序是用多线程来调用这个存储过程,就会发生死锁,导致前台程序接收不了反馈信息!那这个要怎样加锁.请指点!谢谢