Multi instance Job gets Deadlocked on windows platform

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sprasad55
Participant
Posts: 11
Joined: Thu Dec 28, 2006 4:16 pm

Multi instance Job gets Deadlocked on windows platform

Post by sprasad55 »

We have a Multi instance Multi invocation ID Server job wich runs on SQL server 2005, most of the time following SQL gets dead locked. This is a select statement with a LOCK. I have included the SQL server trace. if I execute couple of following SQL select statement through SQL server Managment Studio it runs fine. Any budy has any idea why?

SQL:

SELECT SITE, ltrim(rtrim(cast(customer_no as varchar)))+'~'+ltrim(rtrim(cast(item_div as varchar))), current_salesperson_SK, EFFECTIVE_FROM_DT FROM #CatalogSrc#."dbo".#DTbl# "(nolock)" WHERE SITE = #DSJobInvocationId# order by customer_no desc ,item_div,salesperson_sk,EFFECTIVE_FROM_DT;

SQL SERVER Trace :

StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [CURRENT_IND] AS [SC0] FROM [dbo].[D_CURRENT_SALESPERSON] WITH (READUNCOMMITTED,SAMPLE 2.044260e+000 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

Thanks
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

Hmmm, I don't quite see why a lock occurs. However, multi-instance jobs means each job establishes its own connection to the database. The reason running multiple queries from the same connection may not lock is because its a single connection. I'm just guessing there.

The real question is how are you achieving the lock, and why?
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
sprasad55
Participant
Posts: 11
Joined: Thu Dec 28, 2006 4:16 pm

Post by sprasad55 »

kcbland wrote:Hmmm, I don't quite see why a lock occurs. However, multi-instance jobs means each job establishes its own connection to the database. The reason running multiple queries from the same connection ma ...
Ken,
Even I don't see a reason why this query gets deadlocked, this is a simple Select query, we are running lot of complex quires during our production window, they are not get dead locked. I have included the error message -

"CurrSalesPersonBldHashKey.40.Dimension.From_Src_Prod: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT SITE, ltrim(rtrim(cast(customer_no as varchar)))+'~'+ltrim(rtrim(cast(item_div as varchar))), current_salesperson_SK, EFFECTIVE_FROM_DT FROM EDW.""dbo"".D_Current_Salesperson ""(nolock)"" WHERE SITE = 40 order by customer_no desc ,item_div,salesperson_sk,EFFECTIVE_FROM_DT
SQLSTATE=40001, DBMS.CODE=1205
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

When we ran a trace we found following statement -

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [CURRENT_IND] AS [SC0] FROM [dbo].[D_CURRENT_SALESPERSON] WITH (READUNCOMMITTED,SAMPLE 2.044260e+000 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

I guess above is the statement generated by SQL.

I am wondering may be we need to tune the wait parameters in SQL server, Is there any thing that I can do on Datastage side?, Is there a way we can prepare and submit query through Datastage?
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

sprasad55 wrote:When we ran a trace we found following statement -

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [CURRENT_IND] AS [SC0] FROM [dbo].[D_CURRENT_SALESPERSON] WITH (READUNCOMMITTED,SAMPLE 2.044260e+000 PERCENT) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
This isn't your query, and it's quite weird. It's got an 'order by NULL', it's doing a read uncommitted, but appears to updating the statistics on the table. This implies that a DBA type operation is running against the table. Usually, those types of things take exclusive access of tables during operation. I think you need to get your DBA involved, as the update statistics appear to be running on this table in a sampling mode.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

I have seen this error before. I did nothing, just re-ran the job and it was fine. My DBA couldnt tell what really happened as the error did'nt reproduce the next time.
Try re-running the job again.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Post Reply