Deadlock after changing connection from Biztalk and ODBC to DELL Boomi and JDBC

[fvestjens]

A customer has switched the communication to a SQL Anywhere database from ODBC and BizTalk to Dell Boomi and a jdbc connector. When they are adding records to the database through the DELL BOomi interface they get deadlocks.

The Dell Boomi interface is written in Java and is using sqljdbc42.jar. The settings for the jdbc driver are:

User : test 
Class Name : com.sybase.jdbc3.jdbc.SybDriver
Connection URL : jdbc:sybase:Tds:DECSYB01:2638/testDCFusion

The other properties that can be set can be found here:
link:(http://help.boomi.com/atomsphere/GUID-56BCB840-4F3E-49FB-8DBA-1B8EDA3BB399.html "Boomi Connector properties")

It looks like the threads are waiting for each other as I can see from the deadlock loggin in the database.

snapshotId,snapshotAt,waiter,who,what,wait_on,owner
1,'2018-08-21 08:12:29',83703,'test',,38659038169,83701
1,'2018-08-21 08:12:29',83701,'test',,4297369914,83703
2,'2018-08-21 08:18:31',83732,'test',,42954005469,83731
2,'2018-08-21 08:18:31',83731,'test',,2402624,83732
3,'2018-08-21 08:18:32',83730,'test',,42954005469,83732
3,'2018-08-21 08:18:32',83732,'test',,2402625,83730
4,'2018-08-21 08:18:32',83734,'test',,42954005469,83733
4,'2018-08-21 08:18:32',83733,'test',,4297369921,83734

Is there anyone having an idea what needs to be changed in the connection properties to solve it or is there a setting on the database that I need to change.

The database is running on SQL Anywhere 9.0 (I know it's very old but still does the job)

TIA

Frank

[Breck_Carter]

SQL Anywhere 9 is one of the Top Three Best SQL Anywhere Versions, along with 5.5 and 16, so enjoy!

snapshotId,snapshotAt,waiter,who,what,wait_on,owner
1,'2018-08-21 08:12:29',83703,'test',,38659038169,83701
1,'2018-08-21 08:12:29',83701,'test',,4297369914,83703

It looks like you are seeing classic inter-connection cyclic deadlocks, also called "deadly embrace", where

connection 83703 gets a lock on row(?) 4297369914,

connection 83701 gets a lock on row 38659038169,

then connection 83703 asks for (and cannot get) a lock on 38659038169,

and connection 83701 asks for (and cannot get) a lock 4297369914.

SQL Anywhere instantly realizes there is no point in waiting (blocking) since neither connection can proceed, so it throws an exception at one of them, thus allowing the victor to proceed.

This is most likely an application design error, and it most likely has nothing directly to do with how they connect.

Perhaps in the olden days, the two connections would single-thread through the transaction logic at fault, and the new connection path allowed for more concurrency.

The solution may be for both connections to obtain locks in the same order, so that one connection will be blocked (in a normal fashion) until the other finishes. It is possible that you have two different pieces of code that update the same two tables in a different order.

If you restart dbsrv9.exe with -zl then you might get the "what" column to tell you the table name.

If you know (or can guess) the table name (let's say it is t) you might be able to find the offending rows with SELECT * FROM DBA.t WHERE ROWID ( t ) = 38659038169;

(at this point I would love to insert a pitch for Foxhound, which works on SQL Anywhere 9 target databases... BUT Foxhound doesn't capture deadlocks, only ordinary blocks, alas... but I did copy the SELECT above out of Foxhound Help 🙂

[fvestjens]

What was ROWID() in the old days?

Feature ROWID() not implemented

[fvestjens]

What happens is that a record is added to 1 table and a trigger fires from that insert action on the table to start a stored procedure.

IF I change the BEGIN statement into BEGIN ATOMIC, could that solve the issue?

[VolkerBarth]

I don't think so because when the procedure is called by the trigger, the whole INSERT statement is an atomic operation by design, i.e. the INSERT must either complety fail or succeed. However, you may try to adapt the isolation level (connection-wise or via table hints) in case the STP's action do a query that acquires an undesired lock.

[Vlad]

I know it is a DB thread, but still. If the connection hangs, means that the application thread hangs as well, meaning that you can make thread dump, meaning that you can find the call stack of non-responsive threads, and hopefully this will give you some clue...

[Breck_Carter]

> What was ROWID() in the old days?

My apologies... I did check the Help for Version 9 BUT clearly I was looking at some other version instead... ROWID was an unannounced new feature that apparently arrived sometime during Version 10's lifetime.

I also missed this line in the Foxhound Help: "Locked Row Query is displayed for target databases running on SQL Anywhere 10 and later, and is based on the sa_locks() procedure."

[Breck_Carter]

The SELECT TRACEBACK ( * ) function does exist in V9, and it may help in this situation if the code receiving the SQLCODE –306 SQLSTATE 40001 deadlock exception can be modified.

However, a deadlock isn't a "hang" in any sense of the word, it is a simple exception that is instantly returned to one of the operations involved in the deadly embrace.

[Breck_Carter]

This wouldn't be a home-grown alternative to DEFAULT AUTOINCREMENT, would it? (just guessing 🙂

Please give us more information about insert/update/delete behavior in the procedure.

[Breck_Carter]

Increasing the isolation level might be a good way to flush out the culprit... or silently solve the problem by eliminating all concurrency 🙂

Jack it up to 3! Woohoo! 🙂

[Breck_Carter]

Did you try this?

> If you restart dbsrv9.exe with -zl then you might get the "what" column to tell you the table name.

[Breck_Carter]

If you don't want to restart the server, you can turn on Last Statement dynamically...

CALL sa_server_option ( 'RememberLastStatement', 'ON' ); -- Version 9

[VolkerBarth]

By "adapting" I did not mean increasing:)

[Vlad]

Oh. Excuse me, Breck. I know this behavior for latest versions, but not for the 9th version. I thought the DB holds the request. That is why I assumed that the call stack will help to identify the program error in Java.

You have got my thumb up.

[Breck_Carter]

AFAIK deadlock resolution works the same way in V17 as in V5.5... at least, the docs read the same, albeit less wordy in V5.5:

Transaction blocking and deadlock

Transaction blocking can lead to deadlock, where a set of transactions get into a state where none of them can proceed.

Reasons for deadlocks

A deadlock can arise for two reasons:

A cyclical blocking conflict Transaction A is blocked on transaction B, and transaction B is blocked on transaction A. Clearly, more time will not solve the problem, and one of the transactions must be canceled, allowing the other to proceed. The same situation can arise with more than two transactions blocked in a cycle.

All active database threads are blocked When a transaction becomes blocked, its database thread is not relinquished. If the database is configured with three threads (see the THREAD_COUNT option in "SET OPTION statement") and transactions A, B, and C are blocked on transaction D which is not currently executing a request, then a deadlock situation has arisen since there are no available threads.

SQL Anywhere automatically cancels the last transaction that became blocked (eliminating the deadlock situation), and returns an error to that transaction indicating which form of deadlock occurred.

[Breck_Carter]

Oh, and thank you for...

> You have got my thumb up

...these days, 2 points is half the audience 🙂

[fvestjens]

What is the correct way to set the isolation level on the client when using the following:

jdbc:sybase:Tds:DECSYBT0:2638/DCFUSIONDR

We tried jdbc:sybase:Tds:DECSYBT0:2638/DCFUSIONDR;isolation_level=0 but that still shows level 1 in the connection.

Is there any documentation on this? Is there a default way to do this?

[Breck_Carter]

It doesn't matter what you set the database-level isolation level to, if every time a connection starts it is sent through the nightmare called sp_tsql_environment which changes the connection-level isolation level by executing SET TEMPORARY OPTION isolation_level='1';

One solution is to add code to change the connection-level isolation level after login.

Or (much better)... you could take control by coding your own login_procedure that does NOT call sp_tsql_environment.

Here is the full story...

The default login_procedure option is this: SET OPTION PUBLIC.login_procedure = 'sp_login_environment';

That forces all your connections to call this, which in turn sends TDS (i.e., jConnect and Open Client) connections to sp_tsql_environment:

ALTER PROCEDURE "dbo"."sp_login_environment"()
begin
  if "connection_property"('CommProtocol') = 'TDS' then
    call "dbo"."sp_tsql_environment"()
  end if
end

This truly evil procedure tries to make SQL Anywhere behave like a Sybase SQL Server database from 1995:

ALTER PROCEDURE "dbo"."sp_tsql_environment"()
begin
  if "db_property"('IQStore') = 'Off' then
    -- SQL Anywhere datastore
    set temporary option "close_on_endtrans" = 'OFF'
  end if;
  set temporary option "ansinull" = 'OFF';
  set temporary option "tsql_variables" = 'ON';
  set temporary option "ansi_blanks" = 'ON';
  set temporary option "chained" = 'OFF';
  set temporary option "quoted_identifier" = 'OFF';
  set temporary option "allow_nulls_by_default" = 'OFF';
  set temporary option "on_tsql_error" = 'CONTINUE';
  set temporary option "isolation_level" = '1';
  set temporary option "date_format" = 'YYYY-MM-DD';
  set temporary option "timestamp_format" = 'YYYY-MM-DD HH:NN:SS.SSS';
  set temporary option "time_format" = 'HH:NN:SS.SSS';
  set temporary option "date_order" = 'MDY';
  set temporary option "escape_character" = 'OFF'
end

If you NEVER want that to happen, try this:

SET OPTION PUBLIC.LOGIN_PROCEDURE = '';

Or substitute your own code:

CREATE PROCEDURE DBA.my_login_procedure()
BEGIN
  -- do whatever you want
END;

SET OPTION PUBLIC.LOGIN_PROCEDURE = 'DBA.my_login_procedure';

[Breck_Carter]

Be sure to let us know if changing the isolation level to 0 stops the deadlocks.

[VolkerBarth]

As stated above, you could also try to use a table hint within the query that tries to read freshly inserted rows... If only one or a few tables are involved, that may be easier than altering the login procedure...

[Breck_Carter]

> easier than altering the login procedure

Easier than one line of code, executed once?

SET OPTION PUBLIC.LOGIN_PROCEDURE = '';

That restores the behavior from before jConnect was introduced.

[VolkerBarth]

Well, possibly "easier" in the sense of "with less side effects", because the login procedure does influence those many options... Apparently, I can't tell whether those options are fitting or not..

[Breck_Carter]

Well, you can take a guess... do you think Dell Boomi needs autocommit (chained off)?

No, neither do I ... NOBODY needs chained off 🙂

Chained off is a relic from Sybase SQL Server 4.2.

I guess my REAL point here is that anyone who suddenly gets sp_tsql_environment shoved up their nose, SHOULD examine the changed settings with a GREAT CARE, if they care about the correct operation of their application. Patching the isolation level isn't enough.

[VolkerBarth]

Well, I certainly agree on the REAL point, particularly when the previous client stack has not used the TDS protocol.

[fvestjens]

When I make the change for the login_procedure to be blank. It will be removed from the database option list. Which is fine for us.

However the application using the JDBC connection then gets an error message 'ASA -143: Column '@p0' not found'

Even if I add my own login procedure we get the same error. My login procedure is blank.

We're running on build 2451.

[Breck_Carter]

This may be a brand new, completely different, symptom... not directly related to the deadlocks or login procedure.

The following queries look inside all the SQL code stored inside the database for the substring @p0

BEGIN
SELECT * FROM SYSTABLE     WHERE view_def     LIKE '%@p0%';
SELECT * FROM SYSPROCEDURE WHERE proc_defn    LIKE '%@p0%';
SELECT * FROM SYSTRIGGER   WHERE trigger_defn LIKE '%@p0%';
SELECT * FROM SYSEVENT     WHERE source       LIKE '%@p0%';
END;

[Breck_Carter]

The name @p0 most likely appears in user-written code somewhere.

You can trace the queries coming from client applications by using the "request logging" feature.

See dbsrv9 -zr and -zo in the Help.

You can also do this...

call sa_server_option ( 'RequestLogFile', 'C:\\\\temp\\\\whatever.txt' );
call sa_server_option ( 'RequestLogging', 'SQL+hostvars' );

and then later

call sa_server_option ( 'RequestLogging', 'NONE' );

[Breck_Carter]

The name @p0 looks like a variable name, not a column name in a table.

However, if the variable name is used inside a SELECT or other kind of query (a DELETE WHERE clause for example), and the variable has not been declared, SQL Anywhere may try (and fail) to find the name among all the columns in the tables... and then issue this confusing message.

[MarkCulp]

The @p0 is a parameter name, often used by TSQL connections. This error could be related to the fact that tsql_variables has not been set to 'ON' (because sp_tsql_environment procedure was not executed) - see http://dcx.sap.com/index.html#sqla170/en/html/81523aa06ce210148cedcced6951b23e.html*loio81523aa06ce2...

[VolkerBarth]

So that behaviour (which I was completely unaware of) might be a reason to leave the login procedure as is and just try to adapt the isolation level via a table hint - or to check what options set by the login procedure should be reset afterwards...

[fvestjens]

Could the jdbc driver be the problem when it is not a sql anywhere 9 version?

Is this driver still available? was it on the original CD?

[VolkerBarth]

Have you checked whether the isolation level is set differently? I'm not sure but the default isolation level is 0 (which allows dirty reads, say against a freshly inserted row) whereas the JDBC driver might use isolation level 1 by default, meaning it would possibly be blocked during such a read...

As Breck has stated, please show us what the INSERT and the according trigger and procedure really do.

[fvestjens]

Regarding the isolation level. Should it be changed on the client side or should it be set at the beginning of the procedure?

[fvestjens]

We'll force the isolation level in the JDBC driver to 0 and see what happens