Select statements and locks | SQL Server Performance Forums

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=’:)‘ />]<br /><br />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.<br /><br />SQL Server 2005 has snapshot isolation which just throws a wrench right into the middle of the whole ballgame.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.