>The following procedure causes a deadlock when the user on the web page
>navigates rapidly through a javascript object on the web page. Please
>give me some advice on how best to deal with this. I've thought about
>changing it using DATEDIFF so they update only once a minute, but I
>would appreciate advice on if there is a better way to fix this
>
>CREATE PROCEDURE uspValidateSession
> (
> guid varchar(40)
> , newsessiontimeout int
> )
>RETURNS
> (
> subscriberid int
> , testerid int
> , subscriberloginid int
> )
>AS
>DECLARE VARIABLE sessionlogid int;
>DECLARE VARIABLE lastaccessed timestamp;
>DECLARE VARIABLE currenttimeout int;
>BEGIN
> SELECT b.SessionLogId, b.LastAccessed, b.CurrentSessionTimeout,
>COALESCE(a.SUBSCRIBERID, 0), COALESCE(a.TESTERID, 0),
>>COALESCE(a.SUBSCRIBERLOGINID, 0)
> FROM tblLoginEvent a
> JOIN tblSessionLog b ON a.LOGINEVENTID = b.LOGINEVENTID
> WHERE a.EVENTGUID = :guid
> INTO :sessionlogid, :lastaccessed, :currenttimeout, :subscriberid,
>:testerid, :subscriberloginid;
>
> IF (DATEADD(:currenttimeout MINUTE TO :lastaccessed) >= current_timestamp)
>THEN
> BEGIN
> UPDATE tblSessionLog SET
> LastAccessed = current_timestamp
> , CurrentSessionTimeOut = :newsessiontimeout
> WHERE SessionLogId = :sessio nlogid;
> END
< ELSE
> BEGIN
> subscriberid = 0;
> testerid = 0;
> subscriberloginid = 0;
> END
>
> SUSPEND;
>END^
In your particular case (reading your reply to Thomas), I would expect that
taking explicit control of your transactions would be the best solution. I
don't think updates within the same transaction would cause lock conflicts (I
think you have a lock conflict, not a deadlock), just updates between separate
transactions.
Below is an answer I started before reading your reply to Thomas sufficiently
carefully, that can also be a possible solution, but it is far worse than
controlling your transactions.
An alternative would be to INSERT into a table rather than UPDATE and then
occasionally delete records that doesn't contain the highest value (UPDATE can
be bad for concurrency). E.g. you could have
INSERT INTO tblSessionLogTimer(SessionLogID, LatAccessed,
CurrentSessionTimeOut)
VALUES(:sessionlogid, current_timestamp, :newsessiontimeout);
and then have a separate procedure that you ran e.g. every night that did:
DELETE FROM tblSessionLogTimer TOld
WHERE EXISTS(SELECT * FROM tblSessionLogTimer TNew
WHERE (TOld.LastAccessed < TNew.LastAccessed
OR (TOld.LastAccessed = TNew.LastAccessed
AND TOld.PrimaryKey < TNew.PrimaryKey))
AND TOld.SessionLogId = TNew.SessionLogId);
Theoretically, you could then update tblSessionLog, but it wouldn't make too
much sense since you anyway have to use
SELECT LastAccessed, SessionLogId
FROM tblSessionLogTimer TMax
WHERE NOT EXISTS(SELECT * FROM tblSessionLogTimer TNew
WHERE TMax.SessionLogId = TNew.SessionLogId
AND (TMax.LastAccessed < TNew.LastAccessed
OR (TMax.LastAccessed = TNew.LastAccessed
AND TMax.PrimaryKey < TNew.PrimaryKey)))
if you wanted to know when the last update was done (at least for records that
have been updated since last time you deleted). You have to decide for yourself
whether this is a better solution than what you currently have or not, I expect
that could vary from system to system.
HTH,
Set
