lock mechanism for SQL Server (i)--Overview (types and scopes of locks)

Lock mechanism series for SQL Server:

lock mechanism for SQL Server (i)--Overview (types and scopes of locks)

lock mechanism for SQL Server (ii)--Overview (lock compatibility and resources that can be locked)

lock mechanism for SQL Server (iii)--Overview (Locks and transaction isolation levels)

lock mechanism for SQL Server (iv)--Overview (impact of various transaction isolation levels)

Lock: Popular speaking is to add lock. Locking is a mechanism that the Microsoft SQL Server database engine uses to synchronize access to the same block of data by multiple users at the same time.

Definition: When there are transactional operations, the database engine requires different types of locks, such as related data rows, data pages, or entire data tables, which, when locked, prevent other transactions from manipulating data rows, data pages, or data tables that are already locked. The current transaction frees its locked resources for use by other transactions only if it is not needed for the resource that it is locking.

I. Types and ranges of locks (table below)

Lock type

Description

Share (S)

Used for read operations that do not change or do not update data, such as a SELECT statement.

Update (U)

Used in resources that can be updated. Prevents common forms of deadlocks that occur when multiple sessions are read, locked, and subsequent resource updates are possible.

Exclusive (also known as exclusive) (X)

Used for data modification operations, such as INSERT, UPDATE, or DELETE. Make sure that you do not make multiple updates to the same resource at the same time.

Intention

The hierarchy used to establish the lock. An intent lock consists of three types: intent sharing (IS), intent Exclusive (IX), and intent exclusive sharing (SIX).

Architecture

Used when performing operations that depend on the table schema. Schema locks consist of two types: schema modification (SCH-M) and schema Stability (sch-s).

Bulk Update (BU)

Used when bulk data replication is made to a table and TABLOCK hints are specified.

Key Range

Protects the range of rows read by a query when using the SERIALIZABLE transaction isolation level. Make sure that other transactions cannot insert rows that conform to a serializable transaction query when you run the query again.

(i) Shared lock

A shared lock (S-Lock) allows concurrent transactions to read (SELECT) resources under closed concurrency control.

When you query (SELECT) a record, SQL SERVER tries to get a shared lock (S Lock) on that record, or cannot get it, you must wait for someone else to release a lock that is mutually exclusive to the shared lock in that record to get the record after the shared lock is set.

For example, when someone queries a record on a table, a shared lock is placed on that record, and while others are querying this record for the table, shared locks can also be placed again because they are not mutually exclusive, meaning that SQL Server allows different connections to read the same data at the same time. If someone wants to update this record at this time, because an exclusive lock is mutually exclusive with a shared lock, the exclusive lock cannot be placed until all the people reading the record have finished reading it, the shared lock is released, the person updating the data can set an exclusive lock on the record, and further update the data. In general, under the default transaction isolation level, when the data is read, SQL Server frees the shared lock unless there is a special setting.

(ii) Update lock

An update lock is a relay lock. When the same resource is converted from the original query operation to an update operation, the locking mechanism changes from a shared lock to an update lock, which becomes an exclusive lock.

In a repeatable read or serializable transaction, this transaction reads the data [gets the shared lock (S Lock) of the resource (page or row)] and then modifies the data [this operation requires the lock to be converted to an exclusive lock (X Lock)]. If two transactions acquire a shared mode lock on the resource and then attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive lock (X lock). The conversion of a shared mode to an exclusive lock must wait for a period of time, because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; a lock wait occurs. The second transaction attempted to acquire an exclusive lock (X lock) to update. A deadlock occurs because two transactions are converted to exclusive locks (X locks), and each transaction waits for another transaction to release the shared-mode lock.

To avoid this potential deadlock problem, use an update lock (U lock). Only one transaction at a time can obtain an update lock (U Lock) for a resource. If the transaction modifies the resource, the update lock (U Lock) is converted to an exclusive lock (X lock).

For example, when a record is queried, the update (UPDATE statement plus the Where condition) will be looked up first, a shared lock will be placed on the related record during the lookup, and when the corresponding record is found, SQL SERVER will first place the update lock on the record to avoid a deadlock. Because shared locks and update locks are not mutually exclusive, if two people put a shared lock on the same record at the same time, the person who first updates, can continue to place the update lock while others have placed a shared lock on the same record, but because the update lock is mutually exclusive, when another person wants to place the update lock again, it cannot be set and enters the stop

(iii) exclusive lock (also known as an exclusive lock)

Exclusive locks (exclusive locks) (X locks) prevent concurrent transactions from accessing resources. When an exclusive lock (X lock) is used, no other transaction can modify the data, and the read operation is performed only when the NOLOCK hint or uncommitted read isolation level is used.

When you add, modify, or delete data, such as INSERT, UPDATE, and delete, the statement performs the read operation first to obtain the data before performing the required operation. Therefore, you need to first place an exclusive lock on the resource you are in to ensure that the above operation is not interrupted, and that the exclusive lock remains until the transaction ends after the transaction is opened. For example, an UPDATE statement might modify a row in another table based on a join with one table. In this case, in addition to requesting an exclusive lock on the update row, the UPDATE statement will also request a shared lock on the rows read in the Join table.

(d) Intent lock

Before you place a shared lock on a record, you need to set an intent lock on the larger scope of the record, such as a data page or data table, to prevent other connections from placing an exclusive lock on the page.

The database engine uses intent locks to secure shared locks (S locks) or exclusive locks (X locks) on the underlying resources of the lock hierarchy. Intent locks are named intent locks because they are available at lower-level locks, so they are notified of intent to place locks at lower levels.

There are two uses of intent locks:

· Prevents other transactions from modifying higher-level resources in such a way that they invalidate the lower-level locks.

· Improves the efficiency of the database engine to detect lock collisions at a high level of granularity.

For example, a shared intent lock is requested at the table level (or page level) before a shared lock (S Lock) is requested on the table's data page or data row to prevent another transaction from subsequently attempting to place an exclusive lock (X Lock) on the table containing that page. Intent locks can improve performance because the database engine only checks for intent locks at the table level to determine whether a transaction can safely get locks on the table. You do not need to check each row in the table or lock on each page to determine whether the transaction can lock the entire table. Such as.

Intent locks include intent-sharing (IS), intent-Exclusive (IX), and intent-exclusive sharing (SIX), and so on. Descriptions of various intent locks, such as the following table.

Lock type

Description

Intent Sharing (IS)

Protects shared locks that are requested or acquired for some, but not all, low-level resources in the hierarchy.

Intent Exclusive (IX)

Protects exclusive locks that are requested or acquired for some, but not all, low-level resources in the hierarchy. IX is a superset of IS, and it also protects shared locks for low-level resource requests.

Intent exclusive Share (SIX)

Protects shared locks that are requested or acquired for some, but not all, low-level resources in the hierarchy, as well as intentional exclusive locks that are requested or obtained for some, but not all, low-layer resources. A top-level resource allows concurrent is locks to be used. For example, getting a SIX lock on a table will also get an intent exclusive lock on the page being modified and an exclusive lock on the modified row. Although each resource can have only one SIX lock for a period of time to prevent other transactions from updating the resource, other transactions can read low-level resources in the hierarchy by getting the IS locks at the table levels.

Intent Update (IU)

Protects the update locks that are requested or acquired for all low-level resources in the hierarchy. Only IU locks are used on page resources. If an update operation is made, the IU lock is converted to an IX lock.

Share Intent Update (SIU)

The combination of S-lock and IU-lock, as the result of acquiring these locks separately and holding two locks at the same time. For example, a transaction executes a query with a paglock hint and then performs an update operation. A query with the PAGLOCK hint gets the S lock, and the update operation gets the IU lock.

Update Intent Exclusive (UIX)

The combination of U-lock and IX-lock, as the result of acquiring these locks separately and holding two locks at the same time.

Here's a practical example.

Copy Code
--Example code one:

SET TRANSACTION Isolation Level repeatable READ

BEGIN TRAN

SELECT * FROM [book] WHERE [bookid]=1

WAITFOR DELAY ' 00:00:10 '

COMMIT TRAN
Copy Code

Sp_lock can be executed by another connection to see the execution results of the above code, such as. An intent shared lock is placed on the data sheet and on the data page, and a shared lock is placed on the locked record.

Copy Code
--Example code two:

SET TRANSACTION Isolation Level repeatable READ

BEGIN TRAN

SELECT * FROM [wbk_pde_list] WHERE [wbook_no]= ' BE404942450020 ' and cop_g_no= ' 60217445 '

WAITFOR DELAY ' 00:00:10 '

COMMIT TRAN
Copy Code

Sp_lock can be executed by another connection to see the execution results of the above code, such as. Because [wbk_pde_list] in the preceding code is a heap table, a shared lock is added directly to the data table.

Add an index to the table wbk_pde_list in the example above.

Copy Code
CREATE nonclustered INDEX [ix_wbk_pde_list_wbookno] on [dbo]. [Wbk_pde_list]

(

[Wbook_no] Asc

[Cop_g_no] Asc

) with (Pad_index = off, Statistics_norecompute = off, sort_in_tempdb = off, Ignore_dup_key = off, drop_existing = Off

, ONLINE = OFF, allow_row_locks = on, allow_page_locks = on) on [PRIMARY]

GO
Copy Code

Then execute code example two again, open a new query parser, execute sp_lock in Query Analyzer to see the execution results of the above code, such as. An intent shared lock is placed on the data table and the data page, and a shared lock is placed on the index and the data row.

(v) Architecture lock

The database engine uses schema modification (SCH-M) locks during table data definition language (DDL) operations, such as adding columns or dropping tables. During the lock, the SCH-M lock prevents concurrent access to the table. This means that the SCH-M lock will block all peripheral operations before it is released.

Some data manipulation language (DML) operations, such as table truncation, use sch-m locks to prevent concurrent operations from accessing the affected tables.

The database engine uses schema stability (sch-s) locks when compiling and executing queries. Sch-s locks do not block certain transaction locks, including exclusive (X) locks. As a result, other transactions, including those that use X locks on the table, will continue to run during the compilation of the query. However, concurrent DDL operations and concurrent DML operations that obtain SCH-M locks cannot be performed on the table.

(vi) Large capacity update lock

When the database engine is bulk-copying data into a table, specifying the TABLOCK hint or using the sp_tableoption option (set the data table to table lock on bulk load), the bulk update lock (BU) is used. A bulk update lock (BU Lock) allows multiple threads to load data concurrently into the same table to reduce lock contention for data tables while preventing other processes that do not bulk load data from accessing the table.

(vii) Key range lock

When using the serializable transaction isolation level, protect the range of data rows that the user reads when querying, to ensure that other transactions cannot insert data rows that are protected by key-range locks. Key-range locks are placed on the index, specifying the start and end index key values. These operations first acquire locks on the index, which can block any data rows that attempt to insert, modify, or delete index key values in key-range locks. For example: Place a key-range lock in the index key-value "AAA" to "czz" range to prevent other transactions from inserting data rows that contain index key values anywhere in the range, for example: "ABC", "BCD", "CEF". In addition, when the UPDATE statement is paired with a WHERE clause, the key-range lock may also be set when SQL Server is still looking for data.

lock mechanism for SQL Server (i)--Overview (types and scopes of locks)