SQL Server lock details

1. Handle deadlocks and set deadlock priority
A deadlock occurs when multiple users apply for different blockages. The applicant has part of the blockages and waits for the blockages of other users.
Set deadlock_priority can be used to control the session response mode when a deadlock occurs.
Syntax:
Set deadlock_priority {LOW | normal}
Here, low indicates that the session priority of the process is low. In the case of a deadlock, the transaction of the process can be interrupted first.
2. Process timeout and set the lock timeout duration.
@ Lock_timeout returns the current lock timeout settings for the current session, in milliseconds
Set lock_timeout allows the application to set the maximum time for the statement to wait for resource blocking. When the waiting time of a statement is greater than the lock_timeout setting, the system automatically cancels the blocking statement and Returns Error 1222 to the application that "exceeds the lock request timeout period ".
Example
1) set the lock timeout period to 1,800 milliseconds.
Set lock_timeout 1800
2) configure the index lock Granularity
You can use the sp_indexoption system stored procedure to set the locking granularity for indexes.
3) set the transaction isolation level
SET transaction isolation level

5. View lock Information
1. Execute exec sp_lock to report lock information.
2. Press Ctrl + 2 in the query analyzer to view the lock information.

6. Strange SQL statements
Java code
Begin tran
Update titles set title_idid = title_id where 1 = 2
If (selectavg (price) fromtitles)> $15
Begin
Update titles set price = price * 1.10
Where price <(select AVG (price) from titles)
End
Commit tran

Begin tran
Update titles set title_idid = title_id where 1 = 2
If (selectavg (price) fromtitles)> $15
Begin
Update titles set price = price * 1.10
Where price <(select AVG (price) from titles)
End
Commit tran

Update titles set title_idid = title_id where 1 = 2. This condition will never be true. What is the meaning of this write?
The where clause here looks strange, although the calculated result is always false. When the optimizer processes this query, because it cannot find any valid Sarg, its query plan will force an exclusive lock to scan the table. When the transaction is executed, the where clause immediately obtains a false value, so it does not perform an actual scan, but the process still gets an exclusive table lock.
Because this process now has an exclusive table lock, it can ensure that no other transaction will modify any data rows and can perform repeated read, and avoid potential deadlocks caused by holdlock.
However, when table locking is used to minimize deadlocks, contention for table locking is also increased. Therefore, before implementing this method, you need to consider whether to avoid deadlocks is more important than allowing concurrent access to tables.
Therefore, in this transaction, no other process modifies the price of any row in the table.

7. How to avoid deadlocks
1. When using a transaction, try to shorten the logical processing process of the transaction and commit or roll back the transaction as soon as possible;
2. Set the deadlock timeout parameter to a reasonable range, for example, 3 minutes to 10 minutes. If the timeout period is exceeded, the operation is automatically abandoned to avoid process suspension;
3. All SP servers must handle errors (via @ error)
4. Do not modify the default transaction level of SQL Server. Force lock not recommended
5. Optimize the program and check and avoid deadlock;
1) Reasonably arrange the table access sequence
2) Avoid user intervention in transactions as much as possible, and minimize the number of tasks processed by a transaction.
3) use dirty read technology. Dirty reads avoid lock conflicts because they do not lock the accessed table. In the Client/Server application environment, some transactions are often not allowed to read dirty data, but under specific conditions, we can use dirty read.
4) Time-Domain discretization for data access. The time-domain Discretization Method for Data Access refers to various control measures used in the Client/Server structure to control the access time periods for objects in the database or database. The following methods are used to reasonably arrange the execution time of background transactions and manage background transactions using workflows. When a workflow manages a task, it restricts the number of threads of the same type of tasks (usually one) to prevent excessive resource occupation. On the other hand, it rationally schedules the time series and times of running different tasks, avoid executing multiple background tasks at the same time, and avoid running background tasks at the front-end transaction peak hours.
5) data storage space discretization method. The data storage space discretization method is used to distribute data in a logical table to several discrete spaces, so as to improve the table access performance. The following methods are used: first, a large table is divided into several small tables by row or column. Second, it is divided by different user groups.
6) Use a minimum isolation level. Isolation level refers to the degree of multi-user transaction isolation to ensure the integrity and consistency of database data. sql92 defines four isolation levels: uncommitted read, committed read, Repeatable read, and serializable. If a high isolation level is selected, such as serializability, although the system can guarantee data integrity and consistency to a greater extent for better isolation, however, the chance of deadlocks due to conflicts between transactions is greatly increased, which greatly affects the system performance.
7) Use bound connections. Bound connections allows two or more transactions to connect to share transactions and locks, and any transaction connection must apply for locks as if another transaction wants to apply for locks, therefore, you can allow these transactions to share data without locking conflicts.
8) Consider using Optimistic Locking or giving the transaction an exclusive lock first.

8. How to lock rows, tables, and databases
1. How to lock a row in a table
Java code
SET transaction isolation level read uncommitted
Select * From Table1 rowlock where a = 'a1'

SET transaction isolation level read uncommitted
Select * From Table1 rowlock where a = 'a1'
2. Lock a table in the database
Select col1 from table (tablockx) where 1 = 1;
No one else can operate after the lock, until the locked user is unlocked and unlocked with commit or rollback
3. Instance
Create a table
Java code
Create Table Table1 (A varchar (50) not null, B varchar (50), c varchar (50 ));
Create Table Table2 (d varchar (50), E varchar (50 ))
Insert Table1 (a, B, c) values ('a1', 'b1', 'c1 ');
Insert Table1 (a, B, c) values ('a2 ', 'b2', 'c2 ');
Insert Table1 (a, B, c) values ('a3 ', 'b3', 'c3 ');
Insert Table2 (d, e) values ('d1 ', 'e1 ');
Insert Table2 (d, e) values ('d2 ', 'E2 ');

Create Table Table1 (A varchar (50) not null, B varchar (50), c varchar (50 ));
Create Table Table2 (d varchar (50), E varchar (50 ))
Insert Table1 (a, B, c) values ('a1', 'b1', 'c1 ');
Insert Table1 (a, B, c) values ('a2 ', 'b2', 'c2 ');
Insert Table1 (a, B, c) values ('a3 ', 'b3', 'c3 ');
Insert Table2 (d, e) values ('d1 ', 'e1 ');
Insert Table2 (d, e) values ('d2 ', 'E2 ');
1) exclusive lock
Java code
-- Transaction a first updates table 1 and excludes other transactions during the update.
Begin tran
Update Table1 set a = 'A' where B = 'b2 ';
Waitfor delay '00: 00: 30'; -- wait 30 seconds
Commit tran
-- Transaction a first updates table 2
Begin tran
Select * From Table1 where B = 'b2 ';
Commit tran

-- Transaction a first updates table 1 and excludes other transactions during the update.
Begin tran
Update Table1 set a = 'A' where B = 'b2 ';
Waitfor delay '00: 00: 30'; -- wait 30 seconds
Commit tran
-- Transaction a first updates table 2
Begin tran
Select * From Table1 where B = 'b2 ';
If commit Tran executes the preceding two transactions at the same time, the SELECT query must wait until the update operation is complete before execution, that is, 30 seconds.
2) shared lock
Java code
-- Transaction a first queries the table 1 Table and adds a shared lock during the query to prevent other transactions from modifying the table.
Begin tran
Select * From Table1 holdlock where B = 'b2 ';
-Holdlock: artificially locked
Waitfor delay '00: 00: 30'; -- wait 30 seconds
Commit tran
-- Transaction a first queries table 1 and then changes table 1
Begin tran
Select a, c from Table1 where B = 'b2 ';
Update Table1 set a = 'A' where B = 'b2 ';
Commit tran

-- Transaction a first queries the table 1 Table and adds a shared lock during the query to prevent other transactions from modifying the table.
Begin tran
Select * From Table1 holdlock where B = 'b2 ';
-Holdlock: artificially locked
Waitfor delay '00: 00: 30'; -- wait 30 seconds
Commit tran
-- Transaction a first queries table 1 and then changes table 1
Begin tran
Select a, c from Table1 where B = 'b2 ';
Update Table1 set a = 'A' where B = 'b2 ';
If commit Tran executes the preceding two transactions concurrently, the SELECT query in transaction B can be executed, and update can only be executed after the first transaction releases the shared lock and converts it to the exclusive lock.
3) deadlock
Java code
-- Transaction a updates table 1 with a delay of 30 seconds, and then updates table 2;
Begin tran
Update Table1 set a = 'A' where B = 'b2 ';
-- This will generate an exclusive row lock in Table1 until the transaction is completed.
Waitfor delay '00: 00: 30 ';
-- Entry latency
Update Table2 set D = 'd5 'where E = 'e1 ';
Commit tran
-- Transaction B First updates table 2, then latencies of 10 seconds, and then updates table 1;
Begin tran
Update Table2 set D = 'd5 'where E = 'e1 ';
-- This will generate an exclusive row lock in Table2 until the transaction is completed.
Waitfor delay '00: 00: 10'
-- Entry latency
Update Table1 set a = 'A' where B = 'b2 ';
Commit tran

-- Transaction a updates table 1 with a delay of 30 seconds, and then updates table 2;
Begin tran
Update Table1 set a = 'A' where B = 'b2 ';
-- This will generate an exclusive row lock in Table1 until the transaction is completed.
Waitfor delay '00: 00: 30 ';
-- Entry latency
Update Table2 set D = 'd5 'where E = 'e1 ';
Commit tran
-- Transaction B First updates table 2, then latencies of 10 seconds, and then updates table 1;
Begin tran
Update Table2 set D = 'd5 'where E = 'e1 ';
-- This will generate an exclusive row lock in Table2 until the transaction is completed.
Waitfor delay '00: 00: 10'
-- Entry latency
Update Table1 set a = 'A' where B = 'b2 ';
If commit Tran executes the preceding two transactions concurrently, both transactions A and B will wait for the other party to release the exclusive lock, resulting in a deadlock.

9. Table-level locks provided by sqlserver
The table-level locking tips specified by sqlserver are as follows:
1. holdlock: Keep the shared lock on the table until the entire transaction ends, instead of releasing the added lock immediately after the statement is executed.
2. nolock: no shared or exclusive locks are added. When this option takes effect, uncommitted data or "dirty data" may be read. This option is only applicable to select statements.
3. paglock: Specify to add a page lock (otherwise, a table lock may be added)
4. readcommitted performs scanning with the same lock semantics as transactions running at the committed read isolation level. By default, SQL Server 2000 operates at this isolation level.
5. readpast: Skip the data rows that have been locked. This option will allow the transaction to skip the data rows that have been locked by other transactions when reading data, rather than blocking until other transactions release the lock, readpast is only applicable to select statement operations in transaction operations at the Read committed isolation level.
6. readuncommitted: equivalent to nolock.
7. repeatableread: Set the transaction to a read-only isolation level.
8. rowlock: Use row-level locks instead of coarse-grained page-level locks and table-level locks.
9. serializable: scan with the same lock semantics as transactions running at the serializable read isolation level. Equivalent to holdlock.
10. tablock: Table-level locks are used instead of Row-level or page-level locks. After the statement is executed, SQL Server releases the lock. If both holdlock is specified, the lock remains until the transaction ends.
11. tablockx: Specifies the use of the exclusive lock on the table. This lock can prevent other transactions from reading or updating data in the table until the statement or the entire transaction ends.
12. updlock: Specify the update lock when reading data in the table, instead of the shared lock. The lock remains until this statement or the entire transaction ends, updlock is used to allow users to read data first (without blocking other users from reading data), and ensure that the data is not modified by other users during this period of time when the data is updated later.
Select * from table with (holdlock) other transactions can read the table, but cannot update or delete the table.
Select * from table with (tablockx) other transactions cannot read, update, and delete tables.

10. Application lock

Application locks are the locks generated by client code, rather than the two system stored procedures generated by SQL Server to process application locks.
Sp_getapplock: Lock application resources
Sp_releaseapplock: Unlock application resources
) Use the isolation level as low as possible. Isolation level refers to the degree of multi-user transaction isolation to ensure the integrity and consistency of database data. sql92 defines four isolation levels: uncommitted read, committed read, Repeatable read, and serializable. If a high isolation level is selected, such as serializability, although the system can guarantee data integrity and consistency to a greater extent for better isolation, however, the chance of deadlocks due to conflicts between transactions is greatly increased, which greatly affects the system performance.
7) Use bound connections. Bound connections allows two or more transactions to connect to share transactions and locks, and any transaction connection must apply for locks as if another transaction wants to apply for locks, therefore, you can allow these transactions to share data without locking conflicts.
8) Consider using Optimistic Locking or giving the transaction an exclusive lock first.

8. How to lock rows, tables, and databases
1. How to lock a row in a table
Java code
SET transaction isolation level read uncommitted
Select * From Table1 rowlock where a = 'a1'

It would be better if there are more detailed descriptions of the lock corresponding to the isolation level