>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