The READ UNCOMMITTED isolation level, which the NOLOCK table hint activates, is not to be trusted when it comes to writing data. You are not always protected by error 1065 in cases where writes meet NOLOCK.
Error 1065 states "The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements." However, NOLOCK can still dangerously be used as the source of the write. (NOLOCK can also fail with error 601 on even basic SELECT statements, but coders just aren't always scared off by that.)
Here's a very elementary example to share at parties in order to scare developers away from using NOLOCKs adventurously:
--Connection 1
DROP TABLE IF EXISTS dbo.testnolock1
GO
CREATE TABLE dbo.testnolock1
(id int not null IDENTITY (1,1) PRIMARY KEY,
text1 varchar(10) not null
)
GO
INSERT INTO dbo.testnolock1 (text1) VALUES (1);
GO
Now we have a table with 1 row. Let's begin a transaction to insert another row, but not commit it.
--Connection 1
BEGIN TRAN
INSERT INTO dbo.testnolock1 (text1) VALUES (2);
And in a second table, let's do an INSERT based on a SELECT statement with the NOLOCK table hint.
--Connection 2
DROP TABLE IF EXISTS dbo.testnolock2
GO
CREATE TABLE dbo.testnolock2
(id int not null IDENTITY (1,1) PRIMARY KEY,
text1 varchar(10) not null
)
GO
INSERT INTO testnolock2
SELECT TEXT1 FROM testnolock1 (NOLOCK)
Without the NOLOCK hint, would be blocked by the INSERT in progress in Connection 1.
With the NOLOCK hint, the INSERT statement in Connection 2 completes immediately... however, back in Connection 1:
--Connection 1
ROLLBACK TRAN
What have we got now? From any connection:
SELECT * FROM testnolock1;
SELECT * FROM testnolock2;
 |
| One row in testnolock1, two rows in testnolock2 |
While the INSERT into testnolock1 rolled back (because of one of many different transient errors or failures, not just due to a ROLLBACK), the INSERT with the doomed row where text1=2 succeeded just fine in testnolock2. This is bad! Should Connection 1 retry its INSERT, we could have duplicated data, primary key violations, etc.
If this seems basic to you, great! You have a healthy understanding and fear of the chaos that NOLOCK can unleash.
