Subject Deadlock
Author
Post date 2014-03-15T18:53:16Z
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 = :sessionlogid;
    END
    ELSE 
    BEGIN 
        subscriberid = 0;
        testerid = 0;
        subscriberloginid = 0;
    END 
    
  SUSPEND;
END^

SET TERM ; ^

COMMIT;