Date: Fri, 29 Mar 2024 10:58:18 +0000 (UTC) Message-ID: <1175270322.5721.1711709898540@ip-10-0-1-161.ec2.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_5720_13120357.1711709898539" ------=_Part_5720_13120357.1711709898539 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Microsoft SQL Server uses locking to ensure the integrity of tra= nsactions and consistency in the database. Locking prevents data that users= are reading from being changed by other users, and prevents multiple users= from simultaneously changing the same data. If locking is not used, data w= ithin a database may become incorrect, and queries executed against that da= ta may produce unexpected or invalid results. SQL Server automatically appl= ies locks. However, you can make your applications more efficient by custom= izing or minimizing database locking.
The SQL Server Locks object provides information about locks that are ap= plied to individual resource types. Locks are held on SQL Server resources = such as rows read or rows that were modified during a transaction, and prev= ent the concurrent use of a resource by multiple transactions.
SQL Server can lock the following resources:
Resource | Description |
Database | A database |
Extent | A contiguous group of e= ight data pages or index pages. |
Key | A row lock within an in= dex. |
Page | An eight kilobyte data = page or index page. |
RID | The identifier for a ro= w in the database. |
Table | An entire table in the = database, including all data and indexes. |
SQL Server locks resources using the following resource lock modes:
Prevent a common form of deadlock. = A typical update pattern consists of a transaction:
If two transactions acquire shared-mode locks on a resource and the= n attempt to update data concurrently, one transaction attempts to convert = the lock to an exclusive (X) lock.
The shared-mode-to-exclusive lock = conversion must wait because the exclusive lock for one transaction is not = compatible with the shared-mode lock of the other transaction. In this case= , a lock wait occurs.
The second transaction attempts to acquire an e= xclusive (X) lock for its update. Because both transactions are converting = to exclusive (X) locks, and they are each waiting for the other transaction= to release its shared-mode lock, a deadlock occurs.
Update (U) locks= are used to avoid this problem. Only one transaction can obtain an update = (U) lock to a resource at a time. If a transaction modifies a resource, the= update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock= is converted to a shared-mode lock.
Indicates that SQL Server wants to acqu= ire a shared (S) lock or an exclusive (X) lock on some of the resources low= er in the hierarchy. Intent locks include intent shared (IS), intent exclus= ive (IX), and shared with intent exclusive (SIX).
For example, a shar= ed intent lock placed at the table level means that a transaction intends t= o place shared (S) locks on pages or rows within that table. Setting an int= ent lock at the table level prevents another transaction from acquiring an = exclusive (X) lock on the table containing that page.
Used when a table data definition= language (DDL) operation such as adding a column or dropping a table is be= ing performed. Schema stability (Sch-S) locks are used when compiling queri= es and do not block any transactional locks, including exclusive (X) locks.= Other transactions can continue to run while a query is being compiled, in= cluding transactions with exclusive (X) locks on a table. However, DDL oper= ations cannot be performed on the table.
Bulk Update (BU) Locks
Used when bulk copying=
data into a table and either the TABLELOCK
hint is specified,=
or the Table Lock on Build Load table option is set using sp_Tableop=
tion
. BU locks allow processes to bulk copy data concurrently into t=
he same table while preventing other processes that are not bulk copying da=
ta from accessing the table.
Related Documentation