Sql server lock table while updating
EDIT: Suppose the second query is This is really just the tip of the ice berg. In your example, nobody can answer your question about what is being actually locked because it will depend on many factors. These are bad practices because, among other things, they will lead exactly to lock contention. write locks you need to look into row versioning and snapshot isolation. So as originally posted: The quick and simple is "Yes, the first query will block the second query unless a specific index hint is specified ( NOLOCK, sometimes called a "dirty read" ) or the second query's transaction isolation level is set to IF NOT EXISTS ( SELECT 1 FROM sys.objects WHERE name = 'Foo' AND type = 'U' ) BEGIN --DROP TABLE dbo. Foo ( Foo_PK BIGINT IDENTITY( 1, 1 ) NOT NULL, PRIMARY KEY ( Foo_PK ), Bar BIT, x BIT, y BIT, z BIT ); CREATE NONCLUSTERED INDEX IX_Foo_x ON dbo. Foo ( Bar, x, y, z ) VALUES ( 1, 1, 1, 1 ), ( 0, 0, 0, 0 ); END; GO BEGIN TRANSACTION; UPDATE dbo.Read Understanding Row Versioning-Based Isolation Levels. Foo SET y = 0 WHERE x = 1; -- COMMIT TRANSACTION; lock is range-specific, it also prevents other transactions from inserting or updating the affected columns by altering the data contained therein so that it could fall within that range of the original query. 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.This is a hot topic in the SQL Server DBA community.Some people say that a statistics update blocks transactional activity.
While the statistics update is running, open a new session and execute the following query against sys.dm_tran_locks Dynamic Management View in order to analyze the locked resources by the statistics update.Visit Stack Exchange Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Sign up to join this community Does the first query prevent the second query from running until the first query is done?If so, does the first query prevent the second query from running on all of the rows or just the rows involved in the WHERE clause?As to why you (apparently) have a table lock, this can be caused by no index on ID.The table is being locked because all rows have to be looked at.