SQL Server Learning Note Transaction, Locking, Blocking, Deadlock Usage

Time:2019-6-27

This paper illustrates the transaction, locking, blocking and deadlock usage of learning notes by SQL Server. Share for your reference, as follows:

1. Affairs

Implicit transaction

/*==================================================================
When created, dropped,
 fetch,open,
 revoke,grand,
 alter table,select,insert,delete,update,truncate table
When the statement is first executed, the SQL Server session automatically opens a new transaction.
If the implicit transaction mode is activated in the session, the transaction will remain open all the time.
The transaction does not end until the rollback or commit statement, if you forget to commit the transaction,
At the corresponding isolation level, the locks occupied by transactions may not be released, so try not to use implicit transactions.
====================================================================*/
Conversation 1
set implicit_transactions on
update t
set v = 'ext12'
set implicit_transactions off
Select @@TRANCOUNT -- Output: 1, indicating that the transaction is not released
     - Occupied X exclusive locks will not be released and other sessions will be blocked
Conversation 2, blocked by session 1, does not return any records
select *
from t

If commit is executed in Session 1 to commit a transaction, Session 2 will return to the record immediately.

Now change the execution order of the two sessions:

Conversation 1
Set implicit_transactions -- Opens implicit transactions
select *
from t
set implicit_transactions off
Select @@TRANCOUNT -- Enter: 1 to indicate that the transaction in this session has not been committed
Conversation 2. Conversation 2 is not blocked by session 1.
This is because the default isolation level of the session is read committed.
--Although the transaction in Session 1 is not committed, the select statement is at this isolation level.
- Release the occupied S-share lock after running, so write operations will not be blocked
update t
set v = 'ext'

Display the earliest active transactions in the database

/*==============================================================
If the transaction is always open in the database, it may block the operation of other processes.
Why is it possible rather than certain?
The reason is that the select statement at the default isolation level will release the shared lock immediately after querying the data.
In addition, log backup only truncates the part of the log that is inactive, so active transactions
This will lead to more and more log data.
In order to find uncommitted transactions, the following commands can be used to display the earliest active transactions in a database.
An exception, however, is that the following command does not return: uncommitted transactions that do not occupy lock resources
================================================================*/
Start tran -- Start displaying transactions
select *
From T -- Release shared locks immediately after running
Select @@TRANCOUNT -- Input: 1, indicating that no transaction was committed
DBCC opentran ('wc') -- Displays the earliest active transactions in the database.
       But here it shows "no active transaction in open state"

Query transaction information through session

Because the select statement in the uncommitted transaction above automatically releases the shared lock after execution at the default isolation level,
So the DBCC opentran command does not return the active transaction.
But the following view solves this problem and finds all active transactions.
Finding Activity Business
Select session_id,--the corresponding relationship between session_id and transaction_id
  transaction_id,
  is_user_transaction,
  is_local
From sys.dm_tran_session_transactions -- transactions in a session, identifying all open transactions
where is_user_transaction =1
Finding Activity Business对应的执行语句
Select C. session_id,--the corresponding relationship between session_id and connection_id
  c.connection_id,
  c.most_recent_sql_handle,
  s.text
From sys.dm_exec_connections -- Query information for connection execution and recent execution
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) s
where c.session_id = 361
- Specific information on activities
select t.transaction_id,
  T. name, -- user_transaction is shown here
  t.transaction_begin_time,
  Case t. transaction_type -- transaction type
   When 1 then'Read/Write Transactions'
   When 2 then'read-only transaction'
   When 3 then'System Transaction'
   When 4 then'Distributed Transactions'
  end 'transaction type',
  case t.transaction_state
   When 0 then'transaction has not been fully initialized'
   When 1 then'transaction initialized but not yet started'
   When 2 then'Transaction is active'
   When 3 then'The transaction has ended. This state is used for read-only transactions'
   When 4 then'The commit process has been started for distributed transactions'
   When 5 then'The transaction is ready and waiting for resolution'
   When 6 then'The transaction has been committed'____________
   When 7 then'The transaction is being rolled Back'
   When 8 then'transaction rolled Back'
  end 'transaction state'
From sys.dm_tran_active_transactions -- Active transactions
where transaction_id = 150764485

2. Locking

Concurrency problems arise when a user reads data that another user is modifying, or when a user is modifying data that another user is reading, or when a user is modifying data that another user is modifying. Locking prevents concurrency problems.

The locking mode of resources is called locking mode. The locking mode in SQL Server is shared lock, intentional lock, update lock, exclusive lock, architecture stable lock, architecture modification lock, mass update lock and key range lock. Not all lock modes are compatible. For example, one resource with exclusive locks cannot add other locks, and other transactions must wait until the exclusive locks are released.

Various objects in SQL Server can be locked, and resources that can be locked vary greatly in granularity, from fine-grained (rows, keys) to coarse-grained (databases). Fine-grained locks allow users to query rows that are not locked, with higher concurrency, but require more lock resources (one lock resource per locked row); coarse-grained locks reduce concurrency, but require less lock resources.

Lockable resources in SQL Server:

DB (database)
 Metadata (System Metadata)
 Object (Database Objects: Views, Functions, Stored Procedures, Triggers)
 Table
  Hobt (heap or B tree)
   Allocation Unit (related pages grouped by data type (data, row overflow, large object)
    Extent (8 8KB pages)
     Page (8KB data page)
      Rid (row identifiers correspond to rows of a heap table)
      Key (lock on key range, key in B tree)
File
Application

View Lock Activities

Select resource_type,--resource type
  Resource_database_id,--the database ID where the resource is located
  Resource_associated_entity_id, the ID of the entity associated with the resource in the database.
          The value can be an object ID, a Hobt ID, or an allocation unit ID.
          Depending on the type of resources
  object_name(resource_associated_entity_id,resource_database_id),
  Resource_lock_partition, -- Lock partition ID of partitioned locked resources. The resource value for non-partitioned locks is 0
  Resource_description,--a description of a resource that contains only information that cannot be obtained from other resource columns
  Request_session_id -- Session requesting resources
  Request_type,--request type, which is LOCK
  Request_mode, the mode of request, is the granted mode for granted requests.
       - For waiting requests, the mode of being requested (lock mode)
  Request_status -- the current state of the request,
        - Possible values are GRANTED, CONVERT or WAIT
from sys.dm_tran_locks
WHERE request_session_id = 361

Lock Upgrade of Control Table

Each lock consumes memory resources. As the number of locks increases, the required memory increases and the available memory in the system decreases. If the memory ratio of a lock exceeds a threshold, SQL Server upgrades a fine-grained lock (row lock) to a coarse-grained lock (table lock), which is a lock upgrade process.

The advantage of lock upgrade is that it can reduce the number of locks and memory usage. The disadvantage is that it can lead to blocking and reduce concurrency because it locks up more resources.

-- The default value, whether partitioned or not, will enable lock escalation at the table level?
ALTER TABLE t
SET (lock_escalation = TABLE)
When a table is upgraded, lock upgrade is enabled at the partition level if the table has been partitioned
ALTER TABLE t
SET (lock_escalation = auto)
- Disable lock escalation at the table level, and if you use TabLock prompts or query at the Serilizable isolation level, there will still be table locks
ALTER TABLE t
SET (lock_escalation = disable)

In addition to the locking mode mentioned above, the granularity of locks, and the isolation level of transactions.

The so-called isolation level is actually the degree to which transactions interact with each other. For example, if a transaction modifies data, can other transactions see the modified data, regardless of whether the transaction is committed or not? For the highest isolation level, the changes made by this firm are invisible to any other transaction; for the lowest isolation level, the changes made by this firm can be seen by any other transaction.

SQL Server isolation level:

1. read uncommitted can solve the problem of missing updates, but it can lead to dirty reading.

2. Read committed reads the submitted data, so it solves the problem of dirty reading, but there will be the problem of non-repeatable reading, that is, two reads in a transaction, the same data read for the first time and the second time may have different values, because the select statement in the transaction releases the shared lock immediately after reading, while there is another one at this time. A transaction modifies the data just read by the first transaction, so that the values read by the first transaction and the second transaction will be different.

3. Repatable read solves the problem of non-repeatable read, that is, read twice in a transaction, and read the same data value, but there will be the possibility of hallucination, that is, the first read data is indeed the same as the second read data, but the second read records may be more than the first read records, because the read records are more than the first read records. It does lock the read records, but this table may add new records.

4. Serializable solves the problem of hallucination by locking the range of keys, keys and keys in the query range, such as where id > 5 and ID <== 10. If only two records with ID of 7,9 are added to the table, then the range of 5-6, 7-8 and 9-10 will be locked.

5. The isolation level of snapshot under ALLOW_SNAPSHOT_ISOLATION allows reading the data of the consistent version of transactions, but may not be the latest version. That is to say, only one version can be read in a transaction, for example, two times in a transaction. After the first reading, the data is modified by another transaction and the transaction is submitted. At this time, the second reading is performed. Read the same data as the first read, that is, in a transaction, if the data is modified by other transactions, read the same data. The advantage is that data reading does not block writing and writing does not block reading. In addition, if two transactions modify the same row of data at the same time, it will lead to update conflict errors.

6. The read committed isolation level under READ_COMMITTED_SNAPSHOT allows the submitted data to always be read in the same transaction, and data reading does not block writing, writing does not block reading, nor does it cause update conflicts.

The above is about the concept of locking, so the next step is how to find the blocking process and solve the blocking problem.

Session 1, modifying data, but not committing transactions
BEGIN TRAN
Select @@SPID -- Output: 287
UPDATE t
SET v = '88888'
WHERE idd = 1
Conversation 2, blocking due to non-committal of session-one transaction
BEGIN TRAN
Select @@SPID -- Output: 105
UPDATE t
SET v = '888'
WHERE idd = 1
Query Session 1's Waiting Information
Select session_id -- the query session, that is, the blocked session
  Wait_duration_ms, -- wait milliseconds
  Wait_type,--wait type, such as: LCK_M_X indicates that you are waiting to acquire an exclusive lock
  Blocking_session_id -- Blocking session_id session
from sys.dm_os_waiting_tasks
where session_id = 105
Query the resource status of this blocked session request
select resource_type,
  request_status,
  request_mode,
  request_session_id
from sys.dm_tran_locks
where request_session_id = 105
--It shows that Session 2 acquired four locks in update, shared database locks and two intentional exclusive locks (lock tables, data pages).
A key lock locks the record to be updated. Only the request status of the key lock is wait.
The other three lock states, grant, indicate that session 2 has acquired the lock.
Another way to view blocked sessions is to view execution requests for the current session
select session_id,
  status,
  blocking_session_id,
  wait_type,
  wait_time
from sys.dm_exec_requests
where session_id = 105
- The time the configuration statement waits for the lock to be released
Set the lock request timeout period for the statement
- The timeout period is in milliseconds, after which the lock error return error is returned: (1 line is affected) message 1222, level 16, state 51, line 7 has exceeded the lock request timeout period. Statement terminated.

3. Deadlock

Deadlock occurs when two transactions lock resources separately and continue to request resources that have been acquired by the other party.

Reasons for deadlock:

A. Sessions access tables in different order.

B. Sessions run transactions for a long time and update many tables or rows in a transaction, which increases the possibility of conflicts.

C. Session 1 applied for some row locks, Session 2 applied for some row locks, and then decided to upgrade them to table locks.

If these rows are in the same data page and two sessions are upgrading the lock granularity on the same page at the same time, deadlocks will occur.

set lock_timeout 1000
Tracking deadlock -- Session 1
set transaction isolation level serializable
begin tran
update t
set v ='563'
where idd =2
waitfor delay '00:00:10'
update t
set v = '963'
Whereidd = 1 commit -- Session 2
set transaction isolation level serializable
begin tran
update t
set v ='234'
where idd =1
waitfor delay '00:00:10'
update t
set v = '987'
where idd=2
commit

Open another session to open tracing:

/*===================================================================
Open the tracking flag:
    DBCC TRACEON(trace#[,...n],-1) [With No_InfoMsgs]
Check whether some or some marker is open or closed:
    DBCC TRACESTATUS(trace#[,...n],-1) [With No_InfoMsgs]
Trac#: Specifies one or more trace flag digits that need to be turned on or checked for status
2. -1: If -1 is specified, some or some trace flag bits are opened globally
3. With No_InfoMsgs: When this parameter is included in the command, DBCC is prohibited from outputting informative messages
=====================================================================*/
Tracking 1222 returns detailed deadlock information to the log of SQL Server
--Flag BIT-1 indicates that trace flag bit 1222 should be globally enabled for all SQL Server connections
DBCC TraceOn(1222,-1)
go
Verify that the flag bit is started
DBCC TraceStatus
go
Close the flag
DBCC TraceOff(1222,-1)
go
Setting Deadlock Priority -- Setting Deadlock Priority and Adjusting the Possibility of a Query Session Terminating because of Deadlock
SET DeadLock_Priority Low | Normal | High | numeric-priority
-- It is highly likely that the current connection will be terminated.
set deadlock_priority Low
--SQL Server terminates connections with less rollback costs
set deadlock_priority Normal
Reduce the likelihood of connection termination unless another connection is also High or numerical priority is greater than 5
set deadlock_priority High
Value priority: - 10 to 10 values, - 10 is most likely to be terminated, and 10 is least likely to be terminated.
The bigger the two numbers, the less likely they are to be terminated in a deadlock.
set deadlock_priority 10

I hope this article will be helpful to the design of SQL Server database program.