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.developersdex.com ***
Don't just participate in USENET...get rewarded for it!