SQL Server Performance Forum – Threads Archive

Select statements and locks

Assuming the default locking hints are in place, and no locking hints are used in a select statement, would a select statement ever produce a lock?

What kind of a lock? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] The default of SQL Server is shared lock for all read-only operations. So all SELECT statements, with no insert or update operations involved, would produce a shared lock. If you set the transaction isolation mode to READ UNCOMMITTED or use WITH(NOLOCK) on the select, then it uses what’s called an isolation level 0 lock (which I still haven’t figured out [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]). The isolation level 0 lock ONLY prevents reading of physically corrupted data. I have no idea how it does that. The shared locks of course prevent changes until released; however, they are only for the life of the physical read. SQL Server 2005 has snapshot isolation which just throws a wrench right into the middle of the whole ballgame. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>