Deleted shared lock

Deleted shared lock

Original article address:

Http://sqlblog.com/blogs/paul_white/archive/2010/10/31/read-committed-shared-locks-and-rollbacks.aspx

Translator:

In SQL Server, shared locks are also obtained at different transaction isolation levels:

1) read uncommitted does not apply for a shared lock.

2) You need to apply for a shared lock under Read committed. When the statement is executed, the shared lock is released immediately after it is used.

3) apply for a shared lock under Repeatable read. The shared lock must be released after the transaction ends.

4) apply for a key range lock under serialization.

The default transaction isolation level of SQL Server is read committed. During transaction execution, the shared locks are applied for and released continuously at a high cost. Therefore, SQL Server optimizes the shared lock:In some cases, the application and release of the shared lock will be skipped.. Note: Since SQL Server has not described this shared lock Optimization in any documentation (at least I have not seen it), everything is speculation and summary.

The previous translated Article crazy "exclusive" locks should actually belong to this shared lock optimization. The author of this article attempts to describe this shared lock optimization behavior in a more comprehensive manner.

Translation:

In this article, I will describe a lock optimization behavior that is rarely known at present, and it will give an unexpected answer to the following question:

"If I have an exclusive lock in a data row, can other transactions running at the default readcommitted isolation level still read this row ?"

Based on previous knowledge: Read operations are blocked when trying to apply for a shared lock. Therefore, most people answer "no ". Some people may say: This depends on whether the read_committed_snapshot database option is enabled. In this article, we assume that we do not need to consider this situation. We simply use the default read committed (LOCK) isolation level.

An unexpected answer

To conduct the investigation, we create a test table and add a row of data.

Create Table
DBO. Demo
(
Some_key integer primary key,
Some_value integer not null,
);
Go
Insert
DBO. Demo (some_key, some_value)
Values (1, 1,100 );
Go
If db_name () = n 'tempdb'
Checkpoint;

Now, start a transaction, get an exclusive lock, and list the locks owned by the transaction.

Begin transaction;
Select D. some_key,
D. some_value
From DBO. Demo d with (xlock );
Select L. resource_type,
L. request_mode,
L. request_status,
L. resource_description,
L. resource_associated_entity_id
From SYS. dm_tran_current_transaction t
Join SYS. dm_tran_locks L
On L. request_owner_id = T. transaction_id;

As expected, a row has an exclusive lock, and each table and page has an exclusive lock.

In another independent connection, we try to read the content of this table:

Select D. some_key,
D. some_value
From DBO. Demo d with (rowlock, readcommittedlock );
Different from what you expected, this query is not blocked-although exclusive locks exist, it returns immediately.

(Note: The readcommittedlock table prompts that read operations use readcommitted (LOCK) isolation-Level Rules regardless of the read_committed_snapshot option)

Lock Optimization

SQL Server includes an optimization that allows you to avoid applying for Row-level shared locks in some cases. Especially if these locks are not available and there is no risk of reading non-committed transaction data, it will skip the application for shared locks.

The reason why the preceding SELECT query is not blocked is very simple: no shared lock is applied. We can use profiler to check the lock application:

There is an intention share lock (is) at the table and page levels, which is compatible with the intention exclusive lock of the first query. Because there is no row-level shared lock that conflicts with the exclusive lock, it is not blocked.

Only row-level shared locks

This optimization only applies to row-level shared locks. If we apply for a lock of another granularity, the query will be blocked:

Select D. some_key,
D. some_value
From DBO. Demo d with (paglock, readcommittedlock );
Looking at the lock below, we can see that a table-level intention share lock (is) has been successfully applied. The attempt to obtain the page-level shared lock is blocked because the shared lock conflicts with the existing page-level intent exclusive lock.

No unsubmitted changes

If the page contains uncommitted changes, SQL server does not use this lock optimization. This makes sense: if there is an uncommitted change in the page, dirty reads may occur without using the shared lock. Dirty reads are not allowed at the Read committed isolation level.

SQL Server uses this lock Optimization Based on pages. Therefore, a read operation may apply for Row-level shared locks in some pages (for example, uncommitted changes in these pages) on the other page, you do not need to apply for these locks (because they are secure enough to skip these locks ).

For demonstration, we use the transaction connection that is still open above and insert the second data in the table:

Insert
DBO. Demo (some_key, some_value)
Values (1, 2,200 );
Now both rows are exclusive locks, and there is an uncommitted change in the page:

If we run the SELECT query again, we will find that, as we expected, it is blocked when trying to obtain an exclusive lock. If we add a readpast prompt (skip the locked row), we will immediately return the query result-No rows.

Select D. some_key,
D. some_value
From DBO. Demo d with (rowlock, readcommittedlock, readpast );
The two rows on the page are skipped: one is because the xlock table prompt is used; the other is to protect the exclusive lock of newly inserted rows (but not submitted. The Profiler trace shows that the two row-level shared lock applications have timed out:

You may be surprised to find that your database has many exclusive locks unrelated to the changed data. This is because when SQL server processes the update command, it will obtain the exclusive lock no matter whether the data is modified or not ,. For more details, refer to my previous article: theimpact
Of updates that don't change data. Just submit the changes.

If you submit data, we can skip the row-Level Lock again:

Commit transaction;
Begin transaction;
Select D. some_key,
D. some_value
From DBO. Demo d with (xlock );
In the second connection, running the SELECT query does not obtain any shared locks, and both rows of data are returned.

Select D. some_key,
D. some_value
From DBO. Demo d with (rowlock, readcommittedlock );

Transaction rollback is very different

If we modify data on the page and then roll back these changes, sqlserver cannot use lock Optimization on the page, until the page is submitted or written to a disk (for example, checkpoint ).

Using the transaction opened in the above connection, we insert the third row and roll back the modification immediately:

Insert
DBO. Demo (some_key, some_value)
Values (1, 3,300 );

We will find that select applies for a shared lock on the affected page even if there is no exclusive lock on the page.

Select D. some_key,
D. some_value
From DBO. Demo d with (rowlock, readcommittedlock );
The Profiler trace displays row-level shared locks:

Insert the third row of data again and submit the changes:

Begin transaction;
Insert
DBO. Demo (some_key, some_value)
Values (1, 3,300 );
Commit transaction;
Our SELECT query returns all three rows of data, and only applies for the intention to share lock at the table and page level:

We can also manually execute the checkpoint re-enabled lock optimization.

Summary

When SQL Server reads data, it determines whether to apply for a lock at the row, page, or table level. The lock optimization described in this article has an interesting side effect: a query may block page locks, the use of rowlock in the same query may not be blocked (I said "may not" because rowlock is a prompt rather than a command, so the engine may ignore it at all ).

For example, a query that requires applying for a page-level shared lock will be blocked due to the intention exclusive lock (ix). However, if rowlock is specified for this query, only the page-level intent shared lock (is, it is compatible with the IX lock) and does not obtain any row-Level Lock. This does not mean that you should immediately add the rowlock prompt to all select queries-this may be a very bad idea-but you should know that such a lock optimization method exists.

The readcommitted isolation level ensures that there are no dirty writes or dirty reads. But I didn't explain how this guarantee was implemented. SQL Server uses SHARED locks as part of the default isolation level-but these locks do not always exist.