SQL Server Lock Types

Locking is handled automatically in SQL Server. The Lock Manager chooses the type of lock, based on the type of transaction (such as SELECT, INSERT, UPDATE, or DELETE). Lock Manager uses the following types of locks:

• Shared locks

• Update locks

• Exclusive locks

• Intent locks

• Schema locks

• Bulk update locks

In addition to choosing the type of lock, the Lock Manager in SQL Server 2008 automatically adjusts the granularity of the locks (for example, row, page, table), based on the nature of the statement that is executed and the number of rows that are affected.

Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial