MSSQL locks

I had an interview question on Monday in which I was asked some SQL questions. The one I couldn't answer had to do with locking for concurrency which I really wasn't yet familiar with. I can't say I have a comprehensive understanding of it now either, but I would like to blog what I've learned just this morning in doing some research. This...

SELECT * FROM People

 
 

...might, I suppose, have a concurrency-friendly sister which looks like this...

SELECT * FROM People WITH (UPDLOCK, HOLDLOCK)

 
 

A way to sidestep a lock might be...

SELECT * FROM People WITH (NOLOCK)

 
 

Helpful stuff I have bumped into this morning includes:

1. http://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx
2. http://technet.microsoft.com/en-us/library/aa213026(v=sql.80).aspx
3. http://stackoverflow.com/questions/1483725/select-for-update-with-sql-server
4. http://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements
5. http://stackoverflow.com/questions/7843733/confused-about-updlock-holdlock

 
 

The last of these links has this cheatsheet in it:

	(UPDLOCK)	(HOLDLOCK)	(UPDLOCK, HOLDLOCK)	(TABLOCKX)
SELECT	not blocked	not blocked	not blocked	blocked
INSERT	not blocked	blocked	blocked	blocked
UPDATE	blocked	blocked	blocked	blocked
DELETE	blocked	blocked	blocked	blocked