Transaction was deadlocked on lock resources with another process [on DeleteTracker]

Did anybody ever came across following issue while deploying content through datadeploy?

"DBD: SQLException occured in TDeleteTracker
DBD: Exception Message: Transaction (Process ID 1001) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

We have been facing this issue many times on datadeploy.

We are using datadeploy task in workflow to deploy this through database. Moreover as stated in error about rerun, If I rerun transaction it goes through fine.
Any solutions/pointers?

Appreciate your help.

Find more posts tagged with

Comments

ISCBorisB

Did anybody ever came across following issue while deploying content through datadeploy?

"DBD: SQLException occured in TDeleteTracker
DBD: Exception Message: Transaction (Process ID 1001) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

We have been facing this issue many times on datadeploy.

We are using datadeploy task in workflow to deploy this through database. Moreover as stated in error about rerun, If I rerun transaction it goes through fine.
Any solutions/pointers?

Appreciate your help.

What OS? What TeamSite? What DataBase?
Do you run several deployments simultaneously? Do you run DD in DAS Mode?

DevIntervowen

Oops.. sorry, I missed it. Here's TS configuration

TS 7.2.1 and OD 7.2.1 on windows 2008
SQL Server 2008

Yes, we do run many datadeploys [through dd task in workflow] simulataneously. Sometimes task can have 300+ records to deploy at a time.

Do you run DD in DAS Mode? ==> No we dont use it in DAS mode. Can we use it through DD task by setting some config. variable [I know there was iwsyncdb perl command]?

Also, does datadeploy[or OD] has some timeout configuration so that it waits for some more time rather than throwing deadlock exception?

Adam Stoller

You should have the DBA check for locks on the DB - I've seen a number of cases where someone (usually a developer) is doing something with the DB through a GUI and ends up (unknowingly) placing a lock on the DB because they haven't committed some change that they made - and DD won't work because of that lock being there.

DevIntervowen

Thanks for looking at it guys.

I talked to DBA and he provided following information from his side.

Locks - Blocked Processes: Session 701 has been blocked by session 1059 for the last 323 seconds.

Blocking Details

Blocking session:

Database name:
Application name: Microsoft SQL Server JDBC Driver
Host name: PRODUCTION
OS User name:
Database User name: teamsite_ddl_user
First 2KB of SQL: (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))INSERT INTO IWDELTRACKER( PATH , AREA , KEYCOLNAME , KEYCOLVALUE ) VALUES (@P0,@P1,@P2,@P3)

It looks like iwdeltracker gets locked because of concurrency datadeployment through workflow. Sometimes particular dd task will have 400+ big chunk of data/records to deploy takes around 3-4 times to datadeploy. Does that cause other deployment [we have too many jobs going on at a time so too many deployments too] to wait for sometime and eventually timeout in deadlock?
Can we configure DAS on DD task? and will it help here?

ISCBorisB

Thanks for looking at it guys.

I talked to DBA and he provided following information from his side.

Locks - Blocked Processes: Session 701 has been blocked by session 1059 for the last 323 seconds.

Blocking Details

Blocking session:

Database name:
Application name: Microsoft SQL Server JDBC Driver
Host name: PRODUCTION
OS User name:
Database User name: teamsite_ddl_user
First 2KB of SQL: (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))INSERT INTO IWDELTRACKER( PATH , AREA , KEYCOLNAME , KEYCOLVALUE ) VALUES (@P0,@P1,@P2,@P3)

It looks like iwdeltracker gets locked because of concurrency datadeployment through workflow. Sometimes particular dd task will have 400+ big chunk of data/records to deploy takes around 3-4 times to datadeploy. Does that cause other deployment [we have too many jobs going on at a time so too many deployments too] to wait for sometime and eventually timeout in deadlock?
Can we configure DAS on DD task? and will it help here?

I'm not aware of OD/DD settings that you can tweak.

In SQL Server this behavior is controlled by the "LOCK_TIMEOUT" Variable. The default is "-1",
ie "wait indefinitely - no timeout". As far as I recall, it used to be that you could modify
"LOCK_TIMEOUT" per connection session only, not globally. It may or may not be the case still...

The way I see it, you have couple options.
1. Whatever "LOCK_TIMEOUT" is in your case, it is not a default. Try to find where it is set.
It is possible that DD generates SQL Session settings on the fly, you may want to contact Support
to see if they can provide some insight.

2. Increasing "LOCK_TIMEOUT" is in any case a palliative. It *seems* that you have to limit the number
of concurrent sessions that are hitting the same Database. How you do it depends on your implementation,
deployment workflows and whatnot.

As to DAS - whatever it may be, it is not a mechanism to synchronize very long concurrent DB transactions.

jagadesh

Thanks for looking at it guys.

I talked to DBA and he provided following information from his side.

Locks - Blocked Processes: Session 701 has been blocked by session 1059 for the last 323 seconds.

Blocking Details

Blocking session:

Database name:
Application name: Microsoft SQL Server JDBC Driver
Host name: PRODUCTION
OS User name:
Database User name: teamsite_ddl_user
First 2KB of SQL: (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))INSERT INTO IWDELTRACKER( PATH , AREA , KEYCOLNAME , KEYCOLVALUE ) VALUES (@P0,@P1,@P2,@P3)

It looks like iwdeltracker gets locked because of concurrency datadeployment through workflow. Sometimes particular dd task will have 400+ big chunk of data/records to deploy takes around 3-4 times to datadeploy. Does that cause other deployment [we have too many jobs going on at a time so too many deployments too] to wait for sometime and eventually timeout in deadlock?
Can we configure DAS on DD task? and will it help here?

Either you should go by increasing LOCK_TIMEOUT as suggested by Boris or else just write some functionality to check table lock on task before deployment or on deployment tuple generation and control the deployment as per the requirement