Sql server lock table while updating
Edit 3: I just realized I'm not considering the effect of a background checkpoint which writes an as of yet uncommitted transaction to disk, so yes, my explanation was misleading.
In the second query, the first batch can ( and in this case, will ) return uncommitted data.
Notice that your session_id most likely will be different than the one in the following query.
USE master GO SELECT resource_type , resource_subtype , resource_description , resource_associated_entity_id , request_mode , request_type , request_status , request_session_id FROM sys.dm_tran_locks WHERE request_session_id = 79 Represents a subtype of resource_type.
You as the SQL Server DBA decide that updating database statistics is one possible solution to the problem, but your boss says that it will cause blocking and advises not to update statistics while the database is being used.
In this tip I show that doing a statistics update does not cause blocking.
The table is being locked because all rows have to be looked at.The second batch, running in the default transaction isolation level of will return only after a commit or rollback has been completed in the first session.From here you can look at your query plans and the associated lock levels, but better yet, you can read all about locks in SQL Server here.Rows seven and eight show two schema stability locks on the table.The last row is the only row that shows an Exclusive lock in the table, but it is of subtype UPDSTATS.