I was putting together a procedure to audit login events, the basic components are provided below. During testing, I found that the when dbRemote runs the database would deadlock, consistently. The fix was to add a commit statement on the line before CALL sp_login_environment in the procedure ECO.UsersLogin().
My question is..... Why?
CREATE TABLE ECO.UsersLogins ( LoginId CHAR(128) NOT NULL, LoginTimestamp TIMESTAMP NOT NULL, LoginLocation INTEGER NULL, PRIMARY KEY CLUSTERED ( LoginId, LoginTimestamp ) ); CREATE PROCEDURE ECO.UsersLogin() BEGIN DECLARE @LoginId CHAR(128); DECLARE @LoginTimestamp TIMESTAMP; DECLARE @LoginLocation INTEGER; SET @LoginId = CONNECTION_PROPERTY( 'Userid' ); SET @LoginTimestamp = CURRENT UTC TIMESTAMP; SET @LoginLocation = DBA.ReplicationIdentifier(); -- this provides unique database identifier INSERT INTO ECO.UsersLogins( LoginId, LoginTimestamp, LoginLocation ) ON EXISTING UPDATE VALUES ( @LoginId, @LoginTimestamp, @LoginLocation ); CALL sp_login_environment; END
SQL Anywhere 12 contains deadlock detection and reporting functionality. See the LOG_DEADLOCKS option. Also take a look at the deadlock detection and correction tutorial here.
What activity is performed by this connection after the login procedure completes? What isolation level are you using?
answered 30 Sep '12, 14:08
A bit more information this only occurs when -w > 1 or more precisely with -w 5. Below is what I am seeing. dbremote hangs when this occurs. No deadlocks are reported using SELECT * FROM sa_report_deadlocks () after setting LOG_DEADLOCKS.
My dbremote config is as follows -r -b -o "E:Application Log FilesSybase ASA 12.0XXXXX.log" -os 1M -c "eng=Emprise-DB7-ASA12; dbn=XXXXX; uid=dbRemote; pwd=XXXXX" -dl -k -w 5 -m 50M "E:Sybase ASA 12.0Database Mirror Log FilesXXXXX"
answered 01 Oct '12, 08:14