Understanding SQL Server Locks

Understanding SQL Server Locks SQL Server uses locks to implement transaction isolation and to ensure the information stored in a database is consistent.
Nội dung trích xuất từ tài liệu:
Understanding SQL Server LocksUnderstanding SQL Server LocksSQL Server uses locks to implement transaction isolation and to ensure the informationstored in a database is consistent. Locks prevent one user from reading or changing a rowthat is being changed by another user. For example, when you update a row, a row lock isplaced on that row to prevent another user from updating the row at the same time.Types of SQL Server LocksSQL Server uses many types of locks, some of which are shown in Table 14.5. This tableshows the locks in ascending order of locking granularity, which refers to the size of theresource being locked. For example, a row lock has a finer granularity than a page lock. Table 14.5: SQL Server Lock TypesLOCK DESCRIPTIONTYPERow (RID) Placed on a row in a table. Stands for row identifier. Used to uniquely identify a row.Key (KEY) Placed on a row within an index. Used to protect key ranges in serializable transactions.Page (PAG) Placed on a page, which contains 8KB of row or index data.Extent Placed on an extent, which is a contiguous group of 8 data or index pages.(EXT)Table Placed on a table and locks all the rows and indexes in that table.(TAB)Database Used to lock the whole database when the database administrator puts it(DB) into single user mode for maintenance.SQL Server Locking ModesSQL Server uses different locking modes that determine the level of locking placed on theresource. These locking modes are shown in Table 14.6. Youll see these locking modesin the next section. Table 14.6: SQL Server Locking ModesLOCKING DESCRIPTIONMODEShared (S) Indicates that a transaction is going to read from the resource using a Table 14.6: SQL Server Locking ModesLOCKING DESCRIPTIONMODE SELECT statement. Prevents other transactions from modifying the locked resource. A shared lock is released as soon as the data has been read-unless the transaction isolation level is set to REPEATABLE READ or SERIALIZABLE.Update (U) Indicates that a transaction intends to modify a resource using an INSERT, UPDATE, or DELETE statement. The lock must be escalated to an exclusive lock before the transaction actually performs the modification.Exclusive (X) Allows the transaction to modify the resource using an INSERT, UPDATE, or DELETE statement. No other transactions can read from or write to a resource on which an exclusive lock has been placed.Intent shared Indicates that the transaction intends to place a shared lock on some of(IS) the resources with a finer level of granularity within that resource. For example, placing an IS lock on a table indicates that the transaction intends to place a shared lock on some of the pages or rows within that table. No other transactions may place an exclusive lock on a resource that already has an IS lock on it.Intent exclusive Indicates that the transaction intends to place an exclusive lock on a(IX) resource with a finer level of granularity. No other transactions may place an exclusive lock on a resource that already has an IX lock on it.Shared with Indicates that the transaction intends to read all of the resources thatintent exclusive have a finer level of granularity and modify some of those resources.(SIX) For example, placing a SIX lock on a table indicates that the transaction intends to read all the rows in that table and modify some of those rows. No other transactions may place an exclusive lock on a resource that already has a SIX lock on it.Schema Indicates that a Data Definition Language (DDL) statement is going tomodification be performed on a schema resource, for example, DROP TABLE. No(Sch-M) other transactions may place a lock on a resource that already has a Sch-M lock on it.Schema stability Indicates that a SQL statement that uses the resource is about to be(Sch-S) performed, such as a SELECT statement for example. Other transactions may place a lock on a resource that already has a Sch-S lock on it; only a schema modification lock is prevented.Bulk update Indicates that a bulk copy operation to load rows into a table is to be(BU) performed. A bulk update lock allows other processes to bulk-copy Table 14.6: SQL Server Locking ModesLOCKING DESCRIPTIONMODE data concurrently into the same table, but prevents other processes that are not bulk-cop ...