Database deadlock issue

, ,
1

We have a clustered IS environment and have several scheduler tasks. Just curious if anyone has experienced deadlock on the database that’s caused by conflict in the scheduler tasks.

2

Nothing noticed for schedulers…But we have seen this for some of our interfaces with deadlock issues (inserts/updates) especially with connecting to DB MS-SQL server.

What is your IS version and DB?

HTH,
RMG

3

RMG,

We’re running IS v7.1.2 with MS SQL Server as the backend.

4

Yes this is a very known issue with no proper resolution:

As far the deadlock issues we also had before reported the same to our DB team and no resolution from them or neither SAG has better solution for the root cause.Unless re-designing interfaces with using multiple/over lap of adapter services in the main/sub flows especially when dealing with high volumes of concurrent transactions (inserts/updates) mainly causing this…

Bottom line is no resolution found on this yet:

As this as affecting your IS environment rather than interfaces, alternatively try changing selectMethod = direct (instead of cursor) and see if this works better or not:

5

Thanks RMG. The DB team are asking us how webMethods is interacting with the DB as there should not be any deadlock issue occuring in the MS SQL Server DB.

6

OK…please let forum know the outcome/investigation:

7

What DB objects are involved in the deadlock? Is it wM-supplied objects? Or objects that are part of your integrations?

8

Reamon,

I’ll get this information once we experience another deadlock issue. I know that we have bunch of jdbc adapter services (insert/update, select, etc.) created to interact with the MS SQL Server DB. I wonder if we should convert to stored procedure instead of the direct sql calls. Of course, that would require tremendous amount of effort to do so.

9

Also, I’m thinking it could be related to the MS SQL database class that we have defined in the jdbc properties. We’re using “com.microsoft.sqlserver.jdbc.SQLServerXADataSource” instead of the JDBC Type 4 driver.

10

Deadlock isn’t likely related to which driver is used. Using stored procs also would not address the issue. I wouldn’t change anything at all until you know exactly which DB entities are involved. You’d just be shooting in the dark.

11

Agreed Rob but no resolution for this yet as it occurs intermittently in our interfaces:

12

Has there been any resolution on this topic for deadlock errors when IS tries to update MS SQL server database table?

We have IS 10.5 version and Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) where in the deadlock errors occur when there are concurrent updates on the table.

Transaction (Process ID 72) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Transaction (Process ID 72) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

13

Hi Naveen

The root cause for this , can only be identified from the Database side, if there are multiple applications accessing the database object.

If it only happens when Integration Server is only application accessing the Database, then you might have to re-look at all the Database interfaces and try to identify overlapping’s .

The below link has some examples on “How to Avoid Deadlocks in SQL Server”. Please take a look and see if it helps.

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Msg 1205) - Sql Server Backup Academy.

1 Like