April 27, 2012 at 9:58 am
I have a stored proc in production which authenticates users by checking credentials passwords etc and returns the user results. problem is that when the same user is made to authenticate several times like in a load test there are blocking issues in production.
proc code looks like this...
select
userid
...
...
..
...
from table
inner join tabl2
inner join tab2
where
loginid = @login
and password = @password (decryption by key etc..)
then I update the last modified date of my table in a transaction.
begin tran
update table
set lastmodified = getdate()...
commit
begin catch
rollback
end catch
this proc is fine when called normally and returns results in split seconds
its only when you do a load test you can see blocking issues
could the transaction be locking the table?
April 27, 2012 at 10:02 am
How bad is the blocking? If the transaction just has that one statement in it it'll be fairly short lived and shouldn't block anything for long. What's the full update statement?
April 27, 2012 at 10:04 am
update [dbo].table
set
LastModified = @utcnow,
LastModifiedBy = @User_ID,
ActivationDate = @utcnow,
ExpirationDate = case
when field1 is null and field2 is not null then DateAdd(d,ExpirationOffsetDays,@utctoday)
else ExpirationDate
end,
LastLoginDate = @utcnow,
Status = case when status = 8 then 1 else Status end
where
User_ID = @User_ID
and Application_ID = @Application_ID
and ActivationDate is null
April 27, 2012 at 10:05 am
blocking is bad enough to raise alarm. but like I said its only when some service user tries to authenticate 100 times in few seconds.
April 27, 2012 at 10:24 am
I'm assuming the update is doing a seek and only updating one row. If not you want to take a look at the query and see what you can do to speed it up. Is the alarm just an automated thresh hold alarm or is it causing issues with the process?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply