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