> 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;

"Deadlock" might be misleading here. Possibly two or more transactions
are trying to update the same record in parallel.

* What is the semantic of SessionLogId? Is this something highly unique
across your web requests?
* Are you committing the transaction calling the stored procedure?


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.