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
Multi instance Job gets Deadlocked on windows platform
Moderators: chulett, rschirm, roy
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?
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
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
Ken,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 ...
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?
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.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)
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
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