Deadlock on SQL Server

palmeal · Post by **palmeal** » Fri Dec 23, 2005 6:16 am

I have a Multi Instance Job Sequence (10 wide) that is started concurrently. The first thing the Job Sequence does is to run an underlying Server Job (again multi instance) which attaches to a SQL Server selecting between 50 - 500 rows per instance. Each result set is written back to a different hash file (file name based on unique parameters). This normally works with no problems but occasionally (no pattern) 1, 2 or 3 of the instances fall over wirh database deadlock.

As far as I am concerned the deadlocks should not be happening as each instance is merely SELECTing data into its own hash file. I have an order by which may cause tempoary work files to be created but I have played with this and have not been able to emulate the deadlock problem. The rows are unique to each hash file and SQL Server by default has row level locking rather than page level. I am assured by the DBA's/developers that no other processes (apart from Transaction Log Backups) run at this time.

Rather than try to look for the proverbial needle in a haystack I want to find a method within datastage where I can trap deadlocks and re-run the part of the process that failed. SQL Server by default returns a code of 1205 when deadlock occurs so the Server Job Log looks like (smiley faces should be 8's)

deadlock_test..Get_WH_Portfolio.DW_Funds: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:
select
Internal_Portfolio_Code,
convert(varchar(26),latest_extract_date),
convert(char(12),convert(char(8),latest_extract_date,112)+ substring(convert(char(8),latest_extract_date,108),1,2) + substring(convert(char(8),latest_extract_date,108),4,2)),
convert(varchar(26),getdate()),
convert(char(12),convert(char(8),getdate(),112)+ substring(convert(char(8),getdate(),108),1,2) + substring(convert(char(8),getdate(),108),4,2))
from v_SpectrumPortfolios
where
InclusionID = 1
and Server_Name = 'ST_PPSA'
order by latest_extract_date asc
SQLSTATE=40001,
DBMS.CODE=1205
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The Job Sequence Log merely states that the underlying Server Job Aborted so there is nothing to hook onto there specific to deadlock.

How can I somehow trap the 1205 error and rerun the underlying Server Job ?

I Know that I can add an Abort trigger on the Server Job Activity in the Job Sequence to then search through the Server Job Log.instance to look for 1205. From there I could then loop back into the process again via a sequencer and retry the Server Job Activity. Is there an easier way to do this ?

jeredleo · Post by **jeredleo** » Fri Dec 23, 2005 7:29 am

Have you asked your DBA's to find out who or what is locking the table? This would just be as a double check to make sure that is is definately caused by your multiple instanced jobs. One job holding it from the other. Also, double check to make sure that your isolation level in the stage is Read uncommitted even though the DB is set up for Row locks and not page locks. And as a last resort, I would add the uncommitted read statement to you SQL.

palmeal · Post by **palmeal** » Fri Dec 23, 2005 7:53 am

Thanks for the reply. I have been over at the DBA's desks and have gone through logs/housekeeping etc all of which have shown nothing up. My area of expertise is in the Database area so I have (in my mind) exhausted all areas of the database. It is doubtful that there were adhoc queries running as it was 3am and there were no users logged in. From that I assume that it is the Datastage side of things that is causing the problem.

This is why I was looking for a workaround from within the Datastage side of things.

My stage is set to Read Committed - I don't know if I want to turn on 'dirty reads' in any process that I have.

vsi · Post by **vsi** » Fri Dec 01, 2006 11:27 am

[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I am getting the same error when I am trying to update and insert the data into SQL server...

It will be great help for me if you tell me what is the solution for this

my job pattern is like this

seqfile ---->XFM--------->update
|
------------>insert(insert after update)

Krazykoolrohit · Post by **Krazykoolrohit** » Fri Dec 01, 2006 12:13 pm

How are you ensuring that insert is after update as shown in your design?

vsi · Post by **vsi** » Fri Dec 01, 2006 3:10 pm

i selected insert after update in the SQL enterprise....

I am not able to know why this is happening

palmeal · Post by **palmeal** » Mon Dec 04, 2006 4:00 am

I'm afraid that I never ever sorted this problem - my jobs run almost every night without any deadlocks and it is not too important if they fail as the missed data will be picked up the following error. Still don't understand how there can be deadlock on a select statement - it maybe on a worktable created on the fly on the SQL Server for my sort (order by) clause. I guess you could look at your sql statements to see if there is anything you can take out to stop worktables being created.

If it had been a problem from a business point of view then I would have written some basic or unix code to look through the log for 1205 - not hard but something that DataStage should handle.

My experience with Datastage and SQL Server has not been a good one - not set up (properly) to run stored procedures (transaction control), can't run bulk insert whereas Datastage supports BCP which last worked for SQL Server for version 6. Not sure if these things have been addressed in the new version of Datastage (Hawk).