Deadlock on SQL Server
Posted: 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 ?
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 ?