Wednesday, June 17, 2009

Different types of locks in SQL

SQL Server uses these resource lock modes.

Lock modeDescription
Shared (S)Used for operations that do not change or update data (read-only operations), such as a SELECT statement
Update (U)Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X)Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
IntentUsed to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
SchemaUsed when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU)Used when bulk-copying data into a table and the TABLOCK hint is specified.

No comments:

Post a Comment