Use indexes to reduce lock "go" caused by concurrent transactions

Often, a thread from a different connection reads/updates the same table, which causes blocking and SQL Server handles it automatically to prevent dirty reads. However, there is a common scenario where the rows that are read/updated for each connection are mutually exclusive, in other words, the rows that each connection reads/updates do not intersect. In this article, we will show you how to use indexes appropriately to reduce the occurrence of blocking so that multiple read/update operations can operate on the same table at the same time.

Create the test table as follows:

1. SET ANSI_NULLS on
2. GO
4. SET QUOTED_IDENTIFIER ON
5. GO
7. CREATE TABLE [dbo]. [TEST] (
8. [ID] [int] IDENTITY (*) Not NULL,
9. [NAME] [nvarchar] () NULL,
10. [TEL] [varchar] () NULL
11. ) on [PRIMARY]
13. GO

Plug in two records:

1. INSERT TEST (Name,tel)
2. SELECT N ' A three ', ' 12345678901 '
3. UNION All
4. SELECT N ' John Doe ', ' 23456789012 '

Open two queries and execute the following T-SQL, respectively:

1. BEGIN TRAN
2. UPDATE TEST with (Rowlock)
3. SET TEL = ' 11111111111 '
4. WHERE id=1

1. BEGIN TRAN
2. UPDATE TEST with (Rowlock)
3. SET TEL = ' 22222222222 '
4. WHERE id=2

This is what we can find after the first T-SQL is executed as follows:

However, the second T-SQL executes when pending is there:

View by sp_lock:

As can be seen, the resource 1:498424:0 is locked (x) by the SPID (56), forcing the SPID (52) to wait (wait) for its update (U), so that is what we see when the second update transaction is executed, waiting because the exclusive lock (x) is not released.

The description of the lock mode is as follows, more information can be found on the official website (http://technet.microsoft.com/zh-cn/library/ms175519.aspx).

Lock Mode

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 (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.

For more information on locking resources, refer to (http://technet.microsoft.com/zh-cn/library/ms189849 (v=sql.105). aspx)

Resources

Description

RID
The row identifier used to lock a single row in the heap.

KEY
The row lock in the index that is used to protect the range of keys in a serializable transaction.

PAGE
A 8-KB page in the database, such as a data page or index page.

EXTENT
A contiguous set of eight pages, such as a data page or index page.

HoBT
Heap or B-tree. A lock that is used to protect a B-tree (index) or heap data page in a table that does not have a clustered index.

TABLE
The entire table that includes all the data and indexes.

FILE
Database files.

Application
Application-specific resources.

METADATA
Meta data locks.

Allocation_unit
Allocation unit.

DATABASE
The entire database.

For this kind of concurrency, we can improve the lock by indexing:

Create a primary key index as follows:

View Plain

1. ALTER TABLE [dbo]. [TEST] ADD CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED
2. (
3. [ID] ASC
4. ) with (Pad_index = Off,statistics_norecompute =off, ignore_dup_key = OFF, allow_row_locks =on,allow_page_locks =ON) on [ PRIMARY]
5. GO

When we perform the two update transactions again, we find that both can execute successfully and there are no updates (U) waiting (wait) cases.

Execute the first one:

Execute the second one:

View sp_lock, in the Type column, there is no RID, and there is a key, and key resources are different, the state of the exclusive lock is grant, the basic non-impact.

As you can see from the illustration above, the correct indexing settings help reduce the locks caused by concurrent transactions.

Use indexes to reduce lock "go" caused by concurrent transactions