Concurrent access,, Locks, and Deadlocks

We use the following sp in our VB applications.
The basic logic in this sp is 1) read a row, 2)marked the row is
"checked out), 3) write the same row to another table.
This sp will be executed by many users at the same time. The current
logic we built having slow response, but won't lead to any
locking/deadlocks. However, if we add transaction to make sure the row
we read (you do not want others to have same row) is locked, then we
starting have deadlock/lock problem. Any suggestions to improve the
performance without risk of deadlocks (assuming we have good indexes on
the table)?
Thank you very much for your expertise.
CREATE PROCEDURE sp_get_dcn_sql
@.UserID AS char(8),
@.ProfileID as int
AS
Set nocount on
declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
@.sqlstr as nvarchar(3000)
declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
char(5)
declare @.flagGoodDCN as char(1)
create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
tempDept char(5) NULL)
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
tblYZProfileText where ProfileID = @.ProfileID
set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
where ' +
@.tempWhere + ' and (check_out is null or check_out = ''N'' or
check_out <> ''Y'') '
if ltrim(rtrim(@.tempOrderBy)) is not null
set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
set @.flagGoodDCN = ' '
while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
begin
insert into #tempDCN exec sp_executesql @.sqlstr
select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
#tempDCN
if @.tempDCN is not null
begin
update tblYZInventoryDetail set check_out = 'Y'
where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
WorkedUser)
select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
if @.@.error = 0 --catch PK violation
set @.flagGoodDCN = 'Y'
else
truncate table #tempDCN --go loop
end
else
begin
break
end
end
select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
drop table #tempDCN
Set nocount off
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I'm guessing that you're experiencing conversion deadlocks when the read
locks taken by the select later need to be upgraded to exclusive locks for
the update.
One common solution for this problem is to take update locks on the select
which should ease the deadlock problem when you introduce the transaction
statement, eg:
select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy
from tblYZProfileText WITH (UPDLOCK)
where ProfileID = @.ProfileID
You can read up on this locking hint in SQL Server Books Online here:
http://msdn.microsoft.com/library/en...on_7a_1hf7.asp
Take care not to over-use locking hints as they can hurt you more than help
you if you use them when you don't need to..
HTH
Regards,
Greg Linwood
SQL Server MVP
"YZ" <ycz@.dex.com> wrote in message
news:eR5yylNnEHA.3988@.tk2msftngp13.phx.gbl...
> We use the following sp in our VB applications.
> The basic logic in this sp is 1) read a row, 2)marked the row is
> "checked out), 3) write the same row to another table.
> This sp will be executed by many users at the same time. The current
> logic we built having slow response, but won't lead to any
> locking/deadlocks. However, if we add transaction to make sure the row
> we read (you do not want others to have same row) is locked, then we
> starting have deadlock/lock problem. Any suggestions to improve the
> performance without risk of deadlocks (assuming we have good indexes on
> the table)?
> Thank you very much for your expertise.
>
> CREATE PROCEDURE sp_get_dcn_sql
> @.UserID AS char(8),
> @.ProfileID as int
> AS
> Set nocount on
> declare @.tempWhere as varchar(2000), @.tempOrderBy as varchar(1000),
> @.sqlstr as nvarchar(3000)
> declare @.tempDCN as varchar(16), @.tempST as char(2), @.tempDept as
> char(5)
> declare @.flagGoodDCN as char(1)
> create table #tempDCN (tempDCN varchar(16) NULL, tempST char(2) NULL,
> tempDept char(5) NULL)
> select @.tempWhere = ProfileWhere, @.tempOrderBy = ProfileOrderBy from
> tblYZProfileText where ProfileID = @.ProfileID
> set @.sqlstr = 'select top 1 DCN, CO_Cd, Dept from tblYZInventoryDetail
> where ' +
> @.tempWhere + ' and (check_out is null or check_out = ''N'' or
> check_out <> ''Y'') '
> if ltrim(rtrim(@.tempOrderBy)) is not null
> set @.sqlstr = @.sqlstr + ' order by ' + @.tempOrderBy
> set @.flagGoodDCN = ' '
> while @.flagGoodDCN <> 'Y' --loop to find the next untouched DCN
> begin
> insert into #tempDCN exec sp_executesql @.sqlstr
> select @.tempDCN = tempDCN, @.tempST = tempST, @.tempDept = tempDept from
> #tempDCN
> if @.tempDCN is not null
> begin
> update tblYZInventoryDetail set check_out = 'Y'
> where DCN = @.tempDCN and Co_Cd = @.tempST and Dept = @.tempDept
> insert into tblYZWorkedClaims (DCN, StateID, Dept, StartTime,
> WorkedUser)
> select @.tempDCN, @.tempST,@.tempDept,getdate(),@.UserID
> if @.@.error = 0 --catch PK violation
> set @.flagGoodDCN = 'Y'
> else
> truncate table #tempDCN --go loop
> end
> else
> begin
> break
> end
> end
> select tempDCN as DCN, tempST as Co_Cd, tempDept as Dept from #tempDCN
> drop table #tempDCN
> Set nocount off
> GO
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!