Hi,
Does Select query Lock the table or only Insert,Update and Delete use to lock the table.
Technology Tips and News
Hi,
Does Select query Lock the table or only Insert,Update and Delete use to lock the table.
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 .
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
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.