Archive
Informatica | Thread: WRITER_1_*_1, Message Code: WRT_8229, Native Error: 3621, Violation of PRIMARY KEY constraint
Today while working with an Informatica mapping, I faced a strange issue. The image below shows the design of the INFA mapping:
Here I’m Acquiring a new table with ~500k records and the table is also very fat with PK as a GUID column. Its just a plain data pull with a simple SELECT query, no JOINs, UNIONs, etc. But while running the Workflow it gave me PK Violation error, as shown below:
Severity: ERROR Timestamp: 5/30/2013 7:22:54 PM Node: INFA_NODE_SERVERNAME Thread: WRITER_1_*_1 Process ID: 8216 Message Code: WRT_8229 Message: Database errors occurred: Microsoft OLE DB Provider for SQL Server: The statement has been terminated. SQL State: 01000 Native Error: 3621 State: 1 Severity: 0 SQL Server Message: The statement has been terminated. Microsoft OLE DB Provider for SQL Server: Violation of PRIMARY KEY constraint 'PK_tblTableName'. Cannot insert duplicate key in object 'dbo.tblTableName'. The duplicate key value is (2ea8b6b9-e505-4ef1-a385-0cf9143d2cfd). SQL State: 23000 Native Error: 2627 State: 1 Severity: 14 SQL Server Message: Violation of PRIMARY KEY constraint 'PK_tblTableName'. Cannot insert duplicate key in object 'dbo.tblTableName'. The duplicate key value is (2ea8b6b9-e505-4ef1-a385-0cf9143d2cfd). Database driver error... Function Name : Execute Multiple SQL Stmt : INSERT INTO tblTableName
This was strange because the error was for the GUID PK column, and there is no reason for duplicate values here. As I’ve already taken care of INSERTs & UPDATEs by ROUTER Transformation, which can be seen in the image above. I tried to find the root cause of the error, BINGed/GOOGLEd a lot, but no luck.
Then I checked with an experienced person in my team about this error. He immediately asked me if I’ve added any “WITH (NOLOCK)” option in my query with the Source Table. I said yes, so he asked me to remove it and try, as they had faced similar issues with NOLOCK option before. I removed it and the Workflow ran successfully 🙂 .
I knew that with NOLOCK option I’m doing Dirty Reads, and had added it just to avoid locking/blocking at the Source end. But I was not aware that these Dirty Reads means any kind of data, which can also be duplicate. Thus by adding NOLOCK option with the Table, which is also going through lot of changes, we may allow data to be read more than once. This may be due to Data Movement, Uncommitted Data or Page Splits on the Source table during our reads, where we may be reading the data before and after the Page-Splits.
So, be careful while using NOLOCK option while designing your queries.
For more information check following blog: http://sqlmag.com/database-development/quaere-verum-clustered-index-scans-part-iii
- Spark: The Definitive Guide
- Learning Spark 2Ed
SQL Server - Certification exam books:
- TSQL: 70-761 , 70-762
- DBA: 70-764 , 70-765
- DW/BI: 70-767
SQL Tags
Categories
- Big Data (14)
- Apache Spark (5)
- Databricks (3)
- Hadoop (5)
- DBA Stuff (34)
- Interview Q (8)
- Microsoft Azure (25)
- MS BI (9)
- Analysis Services (1)
- DW and BI (1)
- Integration Services (1)
- Power BI (4)
- Reporting Services (2)
- Tabular Model (1)
- Others (196)
- Blockchain (1)
- Certifications (9)
- Cloud Computing (1)
- Cpp (32)
- Cpp Graphics (21)
- Excel (10)
- Informatica (5)
- Java (56)
- Linux (8)
- Microsoft (14)
- MS.net (2)
- Oracle (1)
- Powershell (3)
- Python (1)
- Reviews (5)
- Security (1)
- Spark SQL (2)
- SQL Server (7)
- SQLwithManoj (11)
- Uncategorized (7)
- VBA Macro (1)
- Visual Studio (1)
- Windows (6)
- SQL Server Conferences (1)
- SQL Server Internals (85)
- Datatypes (5)
- DB Concepts (28)
- Indexes (11)
- JOINS (4)
- ML Python (2)
- SQL DB Engine (8)
- SQL Server Migration (1)
- SQL Server Upgrade (1)
- Stored Procedures (11)
- tempdb (3)
- XML (16)
- SQL Server Questions (41)
- SQL Server Versions (135)
- SQL Azure (4)
- SQL Server 2005 (7)
- SQL Server 2008 (5)
- SQL Server 2012 (33)
- SQL Server 2014 (18)
- SQL Server 2016 (49)
- SQL Server 2017 (20)
- SQL Server 2019 (2)
- SQL Server Express (1)
- T SQL (182)
- Differences (31)
- JSON (6)
- Misconception (7)
- Optimization Performance (13)
- SQL Basics (15)
- SQL Built-in Functions (6)
- SQL Errors (44)
- SQL Tips (67)
- SQL Trivia (9)
Archives
Top Posts
- Getting started with SQL Server 2014 | Download and Install Free (Express) or Full version
- SQL Error - The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE
- Reading JSON string with Nested array of elements | SQL Server 2016 - Part 3
- Using IDENTITY function with SELECT statement in SQL Server
- SQL Server 2016 RTM full and final version available - Download it now !!!
- SQL Jokes!!!
- SQL DBA - Windows could not start the SQL Server... refer to service-specific error code 17051 - SQL Server Evaluation period has expired
- SQL Error - SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs'
- Recursive CTE error - The maximum recursion 100 has been exhausted before statement completion
- SQL Error - The operation cannot be performed on a database with database snapshots or active DBCC replicas
Blog Stats, since Aug 2010
- 4,449,703 hits
StatCounter …since April 2012

Leisure blog: Creek & Trails
- I got full refund of my flight tickets during COVID lockdown (AirIndia via MakeMyTrip)
- YouTube – Your Google Ads account was cancelled due to no spend
- YouTube latest update on its YPP (YouTube Partner Program) which may affect your channel
- Starting your own blog !!!
- How to file ITR (Income Tax Return) online AY 2017-18 (for simple salaried)
Disclaimer
This is my personal blog site.
The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet.
My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.