Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
SQL Deadlocks
 Login/Join
 
Gold member
posted
I am testing Data Migrator Version 7.703 Windows 2008 64 Bit. SQL 2008

I have two etl flows which could write to the same table. I get and SQL Deadlock Error.

I have done the following. I tried with and without.

ENGINE SQLMSS SET ISOLATION RU
ENGINE SQLMSS SET COMMIT WORK

That did not help

I tried to change the confguration of SQL

USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 1
RECONFIGURE WITH OVERRIDE
GO

Still no help

I changed the fex properties
Number of attempts: 25
Restart from: Last Commit

At least the fex does not fail but I do not really like this option.

What can I do to prevent these DEADLOCKS?

Below is my stat log

2011/10/14 06:09:50 (ICM18974) ------ Start of Log Record for fad_etl/x_cert_psc2 ------ fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18122) Request - fad_etl/x_cert_psc2 (Owner: FOLKSRE\jaadmin) submitted. fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18015) DEP_2: procedure irf_summary_date_control_ja started. fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 20111013031501000 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 XXX 1960/06/01 00:00:00.000 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 CURRDATETIME IS 2011/10/14 00:00:00.000 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18039) DEP_2 irf_summary_date_control_ja Return Code = 0 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18742) dt_contract_psc type MS SQL Server Existing target fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18742) dt_contract type MS SQL Server Existing target fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18429) Issuing PREPARE fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:09:50 (ICM18743) Starting Load fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:10:53 (ICM18745) Commit forced at: 500 for 500 row(s) fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (FOC1400) SQLCODE IS 1205 (HEX: 000004B5) XOPEN: 40001 fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 : Microsoft OLE DB Provider for SQL Server: [40001] Transaction (Process I fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 : D 51) was deadlocked on lock resources with another process and has been fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 : chosen as the deadlock victim. Rerun the transaction. fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (FOC1740) EXECUTE ERROR : DT_CONTRACT fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (ICM18745) Commit forced at: 513 for 513 row(s) fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (ICM18041) -- stats for source file fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (ICM18516) 513 : Row(s) processed by job fad_etl x_cert_psc2 20111014060950_179ff611
2011/10/14 06:11:06 (ICM18519) 0 : Row(s) rejected due to format error fad_etl x_cert_


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Hi all,

FYI, John already has a case regarding this issue, and the case is currently in research.

John, when you hear a solution/update from the case, can you please post it here? Thank you in advance for sharing with all.

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
Gold member
posted Hide Post
No solution yet - but I did determine it has nothing to do with the version of Webfocus or SQL. Same error occurs on production server.


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.