Does Select query Lock the table

Hi,

Does Select query Lock the table or only Insert,Update and Delete use to lock the table.

July 17th, 2013 6:20am

Select query wont lock the table. It would take a shared lock while update takes an exclusive lock on rows/pages and the update could lock the table incase of lock escalation under read committed
Free Windows Admin Tool Kit Click here and download it now
July 17th, 2013 6:23am

yup. .depends on the Isolation level of the database (read more here -> http://msdn.microsoft.com/en-us/library/ms189122(v=sql.105).aspx)

In default isolation level which is READ COMMITTED.

It put a shared lock...In shared lock other select query can still read the data, in other to update,delete or insert the query need to put a exclusive lock, which prevent the data to be read .

July 17th, 2013 6:26am

SELECT query also issue a lock on resources. Under pessimistic concurrency control, read operations such as a SELECT query issue a Shared (S) lock that prevent other transactions from modifying data. Once the read operation completes, the shared lock will release.

http://msdn.microsoft.com/en-us/library/ms175519(v=sql.105).aspx

Free Windows Admin Tool Kit Click here and download it now
July 17th, 2013 6:27am

Each operation has its own lock mode and we have a list of conflicting lock mode. Yes, select locks the table until reads completes which conflicts with Insert/Delete/Updates lock mode.

Generally Select should be used with WITH (NOLOCK) to avoid blocking the dml operations but it will result in dirty reads. You will need to weigh between concurrency and data consistency.  suggest to make your self familiar with ACID property, Locking mechanism in Sql server.

July 17th, 2013 6:30am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics