While Trading Network Deployment some tables locking

, , ,
1

Hello everyone,
I am working on deploying trading network assets. All IS and TN composites are rendered successfully. However, I am getting a Read timed out error on TN composites while IS entities are deployed successfully during deployment. When I examine the source of the problem, I can see from the query below that the Contact, PartnerContact, Destination PartnerDestination tables are locked.

SELECT blocking_session_id AS BlockingSessionID, session_id AS VictimSessionID,    
[text] AS VictimQuery, wait_time/1000 AS WaitDurationSecond
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

When I kill the locked queries, I get the

[SoftwareAG][SQLServer JDBC Driver]Object has been closed

error on only Enterprise partner.

webMethods Version: 10.3
SQL Server Database
Windows Server 2019

what is your opinion about this issue?

Thanks,

1 Like
2

Have you checked if enough connections persists.

3

I had repeated TN database lock issue for past few months. We implemented Transaction isolation at database level which resolved our problem.

SET TRANSACTION ISOLATION LEVEL (Transact-SQL) - SQL Server | Microsoft Learn text**

1 Like
4

Hi Vivek,
Thank you for your reply. Please could you give me more detail which one level did you prefer and how to implement it?
thanks.

5

We implemented transaction-isolation level on the database to READ_COMMITTED_SNAPSHOT

to find what snapshot is enabled , use the below query.

Change DB name according to your actual db in the below query and execute below query:

SELECT name,is_read_committed_snapshot_on, snapshot_isolation_state_desc FROM sys.databases WHERE name= ‘<TN_DATABASE_NAME>’

To implement this , you need to work with your database administrator. This will need a outage.

1 Like
6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.