MS Sql Server 2005 locking
This is a bit of a short post, just to put to the record some of my experiences with SQL locks / nolock / readpast / transactions I've been having lately so i (and maybe others) can refer to it later...
NOLOCK
select * from table(nolock)
This is useful in that if another connection has that table locked, this select statement won't get blocked. It will still read the locked rows, and return whatever they have been updated to by the other connection so far, just as if the other connection wasn't using a transaction.
READPAST
select * from table(readpast)
Just like readpast, if another connection has that table locked, this select statement won't get blocked. It will simply skip reading the locked rows. Unlike nolock, this will put a share lock while this select is returning its rows. If you're in a transaction, this share lock will disappear when the select finishes, rather than when the transaction is finished. I've decided that this locking isn't much of an issue, because readpast select statements will always run quickly because they won't get blocked by locks.
And unfortunately you can't do both readpast and nolock together. It'd be nice to do that: don't lock anything, and skip other connection's locked rows.
Checking for locks
Here's some code i use to check up on locks, i start it running in SSMS and then run my potentially locking code, then stop it running. By looking at the execution times, i can tell if things were locked and for how long:
abc:
set statistics time on
select status,* from outbox where medium = 'F'
set statistics time off
waitfor delay '0:0:0.2'
goto abc
Thanks for reading! And if you want to get in touch, I'd love to hear from you: chris.hulbert at gmail.
Chris Hulbert
(Comp Sci, Hons - UTS)
Software Developer (Freelancer / Contractor) in Australia.
I have worked at places such as Google, Cochlear, Assembly Payments, News Corp, Fox Sports, NineMSN, FetchTV, Coles, Woolworths, Trust Bank, and Westpac, among others. If you're looking for help developing an iOS app, drop me a line!
Get in touch:
[email protected]
github.com/chrishulbert
linkedin
