I am using SQL Server 2008.
-
What is the default lock behaviour with
UPDATE?NOLOCK?ROWLOCK?PAGLOCK?
-
How can I tell what the current level of lock is for a table?
Thanks,
Bruce
Sql-server – Default SQL Server 2008 locking behaviour
sql serversql-server-2008
Related Question
- Sql-server – Checking lock granularity
- Sql-server – migrating from SQL server 2005 to 2008
- Sql-server – Changes the default database owner in SQL Server 2008
- Sql-server – Why isn’t partition-level lock escalation the default
- Sql-server – SQL Server 2008 R2 – Access Failure in a query that uses Linked server
- SQL Server 2008 R2 Memory Allocation – Best Practices
- SQL Server – NOLOCK or Read Uncommitted Locking and Latching Behaviors
Best Answer
It really depends on how much you're updating. Locks will escalate as the size of the query increases. If many rows within the same page will be modified, SQL Server will escalate to a
PAGLOCK. If many pages will be modified, it will escalate to aTABLOCK.