NOLOCK | SQL with Manoj

Difference between NOLOCK and READPAST table hints

October 4, 2013 4 comments

AS per MS BOL:

– NOLOCK: Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. NOLOCK is equivalent to READUNCOMMITTED.

– READPAST: Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped.

Thus, while using NOLOCK you get all rows back but there are chances to read Uncommitted (Dirty) data. And while using READPAST you get only Committed Data so there are chances you won’t get those records that are currently being processed and not committed.

Let’s do a simple test:

–> Open a Query Editor in SSMS and copy following code:

-- Creating a sample table with 100 records:
SELECT TOP 100 * 
INTO dbo.Person
FROM [Person].[Person]

-- Initiate Transaction to verify the behavior of these hints:
BEGIN TRANSACTION

UPDATE dbo.Person
SET MiddleName = NULL
WHERE BusinessEntityID >= 10 
AND BusinessEntityID < 20

–> Now open a second Query Editor in SSSM and copy following code:

-- NOLOCK: returns all 100 records
SELECT * 
FROM dbo.Person (nolock)
-- this includes 10 records that are under update and not committed yet.

-- READPAST: returns only 90 records
SELECT * 
FROM dbo.Person (readpast)
-- because other 10 are under update and are no committed yet in the 1st Query Editor:

–> Now go back to the 1st Query Editor window and run following query to Rollback the Transaction:

-- Issue a Rollback to rollback the Transaction:
ROLLBACK

-- Drop the Sample table:
DROP TABLE dbo.Person

Note:

– Using READPAST avoids locking contention when implementing a work queue that uses a SQL Server table.

– Using NOLOCK may lead to read uncommitted (dirty) data and/or may read a row more than once due to page-splitting.

Both of them avoids locking, but on the cost of incorrect/dirty data. So one should carefully use them depending on their business scenario.

Categories: Differences Tags: NOLOCK, READPAST, READUNCOMMITTED, Table Hints, transactions

Informatica | Thread: WRITER_1_*_1, Message Code: WRT_8229, Native Error: 3621, Violation of PRIMARY KEY constraint

May 30, 2013 2 comments

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