Wednesday, August 24, 2022

A simple lab to demonstrate the danger of NOLOCKs in INSERT statements

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. 


Monday, August 01, 2022

SQLSaturday Baton Rouge is back!

Looking forward to jambalaya at #SQLSatBR in THIS SATURDAY! Also looking forward to speaking and seeing all of you there, I guess... Register today: https://sqlsaturday.com/2022-08-06-sqlsaturday1026/

After I stepped down from the SQLSaturday Baton Rouge organizing committee lead in 2019, I had no idea what was to come. A pandemic, the dissolution of SQL PASS, and two missed Augusts later, SQLSaturday Baton Rouge is back! One thing I did get right in 2019: other volunteers deserved the chance to lead and organize and network and they've been doing a great job, from what I can tell so far. I'm happy to see the assets and resources we'd built together over 11 years of SQLSaturdays at LSU continue to be put to use.

I am speaking at:
- 9:45am in the Careers in IT panel
- 11am for Tabletop Role-playing Games at Work for Fun & Profit with Stacy Vicknair and Christine Assaf - Presentation available for download
- 1:30pm for How to Think Like a Certification Exam - Presentation available for download

See you there!


Thursday, June 02, 2022

Speaking on Ethics in Modern Data at Improving Edge 2022


Christine and I are looking forward to speaking at the Improving Edge conference, co-presenting our session on Monday, June 6th. Our presentation on “Ethics in Modern Data” features topics relevant to modern developers and data professionals, using historical and current events to discuss ethics in data collection and analysis. 

This is an important topic that lives at the crossroads of our careers, Christine's career in organizational psychology and human resources, my career in data, our work and volunteerism in civic non-profits, and our joint passion for history and civil rights. It's important to understand that when dealing with bias: outcomes matter, intentions don't. 

Our slide deck, references, and citations are available for download.