SQL Server learning notes: transactions, locks, blocking, and deadlocks

SQL Server learning notes: transactions, locks, blocking, and deadlocks

This article describes the usage of transactions, locks, blocking, and deadlocks in SQL Server learning notes. We will share this with you for your reference. The details are as follows:

1. Transactions

Implicit transactions

/* ===================================================== ======================================, When the drop, fetch, open, revoke, grand, alter table, select, insert, delete, update, and truncate table statements are executed first, the SQL Server session automatically opens a new transaction, if the implicit transaction mode is activated in the session, the transaction remains open until the transaction rollback or commit statement ends. If you forget to commit the transaction, at the corresponding isolation level, the locks occupied by transactions may not be released, so do not use implicit transactions as much as possible. ========================================================== =========================*/-- Session 1 set implicit_transactions onupdate tset v =' ext12 'set implicit_transactions offselect @ TRANCOUNT -- output: 1. The transaction is not released. The occupied X exclusive lock will not be released and other sessions will be blocked.

-- Session 2, blocked by session 1, does not return any record select * from t

If you execute commit in session 1 to commit the transaction, Session 2 will return a record immediately.

Change the execution sequence of the two sessions:

-- Session 1 set implicit_transactions on -- the implicit transaction select * from tset implicit_transactions offselect @ TRANCOUNT -- input: 1, indicating that the transactions in this session have not been committed.

-- Session 2 and Session 2 are not blocked by Session 1. -- this is because the default isolation level of the session is read committed. -- although transactions in session 1 are not committed, however, at this isolation level, the select statement releases the occupied S shared lock after running, so it does not block the write operation update tset v = 'ext'

Displays the earliest active transactions in the database.

/* ===================================================== ====================================== If the transaction is always open in the database, the operation of other processes may be blocked. Why is it possible, not necessarily? The reason is that the shared lock will be immediately released after the select statement at the default isolation level queries data. In addition, log backup only truncuts off the logs of non-active transactions, so active transactions will cause more and more log data. To locate uncommitted transactions, you can use the following command to display the earliest active transactions of a database. however, the following command does not return an exception: uncommitted transactions that do not occupy lock resources ================================ ===================================== */begin tran -- start to display transactions select * from t -- release the shared lock immediately after running select @ TRANCOUNT -- input: 1. The transaction dbcc opentran ('wc ') is not committed. The earliest active transaction of the database is displayed, but "no active transaction in the open state" is displayed"

Query transaction information through sessions

-- Since the select statement in the preceding uncommitted transaction is automatically released after execution at the default isolation level, the dbcc opentran command does not return this active transaction, -- however, the following view solves this problem and finds all active transactions. -- Find the select session_id of the active transaction, -- the ing relationship between session_id and transaction_id: transaction_id, is_user_transaction, is_localfrom sys. dm_tran_session_transactions -- the transaction in the session to identify all opened transactions where is_user_transaction = 1 -- find the execution statement select c. session_id, -- Relationship between session_id and connection_id c. connection_id, c. most_recent_ SQL _handle, s. textfrom sys. dm_exec_connections c -- execute the connection. The latest query information is cross apply sys. dm_exec_ SQL _text (c. most_recent_s Ql_handle) swhere c. session_id = 361 -- select t. transaction_id, t. name, -- user_transaction t is displayed here. transaction_begin_time, case t. transaction_type -- transaction type when 1 then' read/write transactions 'when 2 then' read-only transactions 'when 3 then' system transactions 'when 4 then' distributed transactions 'end' transaction type ', case t. transaction_state when 0 then' the transaction has not fully initialized the 'when 1 then' transaction has been initialized, but the 'when 2 then' transaction has not been started. The transaction is active and the 'when 3 then' transaction has ended. This status is used by the read-only transaction 'when 4 then'. The commit process 'when 5 then' has been started for the Distributed Transaction. The transaction is in the ready state and waiting for parsing 'when 6 then'. The transaction has been committed. 7 then' the transaction is rolling back 'when 8 then' the transaction has rolled back 'end' transaction state' from sys. dm_tran_active_transactions t -- the active transaction where transaction_id = 150764485

2. Lock

When a user wants to read the data being modified by another user, or the user is modifying the data being read by another user, or the user wants to modify the data being modified by another user, concurrency issues may occur. Locking prevents concurrency issues.

The lock mode of resources is called the lock mode. The lock mode in SQL Server is: Shared lock, intention lock, update lock, exclusive lock, architecture stability lock, architecture modification lock, and bulk update lock, key range lock. Not all lock modes are compatible. For example, a resource with exclusive locks cannot be added with other locks, and other transactions must wait until the exclusive locks are released.

Various Objects in SQL Server can be locked. The granularity of resources that can be locked varies greatly, from fine-grained (row, key) to coarse-grained (database ). Fine-grained locks allow users to query unlocked rows with higher concurrency, but require more lock resources (each locked row requires a lock Resource ); coarse-grained locks reduce concurrency, but require few lock resources.

Resources that can be locked in SQL Server:

DB (database) Metadata (system Metadata) Object (database Object: View, function, stored procedure, trigger) Table (Table) Hobt (heap or B tree) allocation Unit (Related Pages grouped by data type (data, row overflow, and large objects) Extent (8 8 KB pages) Page (8 KB data Page) rid (the row identifier corresponds to the row of a heap table) Key (Key range lock, Key in B tree) FileApplication

View lock Activity

Select resource_type, -- Resource Type resource_database_id, -- id of the database where the resource is located resource_associated_entity_id, -- ID of the object associated with the resource in the database. -- The value can be the Object ID, Hobt ID, or allocation unit ID. -- The resource type determines object_name (resource_associated_entity_id, resource_database_id), resource_lock_partition, -- the ID of the locked shard of the partitioned lock resource. The unpartitioned lock resource value is 0 resource_description, -- Description of the resource, which only contains information that cannot be obtained from other resource columns: request_session_id, -- Request resource session request_type, -- Request type, the value is LOCK request_mode, which is the request mode. For granted requests, the value is the granted mode. For waiting requests, the value is the requested mode (locked mode) request_status -- the current status of the request. The value may be GRANTED, CONVERT, or WAITfrom sys. dm_tran_locksWHERE request_session_id = 361

Lock upgrade of control table

Each lock consumes memory resources. When the number of locks increases, the memory required will increase, and the available memory in the system will decrease. If the memory usage of the lock exceeds the threshold value, SQL Server upgrades the fine-grained lock (row lock) to the coarse-grained lock (Table lock). This process is the lock upgrade.

The advantage of lock upgrade is that the number of locks can be reduced and the memory usage can be reduced accordingly. The disadvantage is that a larger resource is locked, which can cause blocking and reduce concurrency.

-- The default value is alter table tSET (lock_escalation = TABLE), whether it is a partitioned TABLE or not. -- when a TABLE is upgraded, if the TABLE is already partitioned, enable lock upgrade at the partition level alter table tSET (lock_escalation = auto) -- disable lock upgrade at the TABLE level. If you use the TabLock prompt or query at the Serializable isolation level, there will still be a TABLE lock alter table tSET (lock_escalation = disable)

In addition to the locking mode and lock granularity mentioned above, the locking level also affects the transaction isolation level.

The so-called isolation level is actually the degree of mutual influence between transactions. For example, if a transaction modifies data, can other transactions see the modified data, regardless of whether the transaction is committed. For the highest isolation level, the modifications made by this firm are invisible to any other transaction. For the lowest isolation level, the modifications 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 may cause dirty reads.

2. read committed reads committed data, so it solves the dirty read problem, but there may be the problem of non-repeated reading, that is, there are two reads in a transaction, the value of the first read and the second read data may be different, because the select statement in the transaction immediately releases the shared lock after reading the data, at this time, another transaction modified the data read by the first transaction, so that the value of the first read and the second read will be different.

3. repeatable read solves the problem of non-repeated reads, that is, two reads before and after a transaction. The read data values are the same, but there may be Phantom reads, that is, the data read for the first time is indeed the same as the data read for the second time, but the number of records read for the second time may be more than the number of records read for the first time, this is because the read records are indeed locked, but new records may be added to the table.

4. serializable locks the range between keys and keys in the query range to solve the phantom read problem, such as where id> = 5 and id <= 10, only two records with the id of 7, 9 are added to the table. The ranges 5-6, 7-8, and 9-10 are locked.

5. snapshot in ALLOW_SNAPSHOT_ISOLATION allows you to read data of consistent transaction versions, but it may not be the latest version. That is to say, you can only read a certain version in a transaction. For example, there are two reads in a transaction. After reading the data for the first time, the data is modified by another transaction and the transaction is committed. At this time, the read data is the same as the first read data, this means that if the data in a transaction is modified by another transaction, the read data is the same. 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, an update conflict error occurs.

6. the read committed isolation level under READ_COMMITTED_SNAPSHOT allows the same transaction to always be able to read the committed data that has been run, and the Data Reading will not block writing, and the write will not block reading, does not cause update conflicts.

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

-- Session 1: modify data, but no commit transaction BEGIN TRANselect @ SPID -- output: 287 UPDATE tSET v = '20160901' WHERE idd = 1 -- Session 2, because the session-1 transaction is not committed, the BEGIN TRANselect @ SPID -- output: 105 UPDATE tSET v = '000000' WHERE idd = 1 -- query the waiting information of Session 1 select session_id, -- query the session, that is, the blocked session wait_duration_ms, -- wait for the number of milliseconds wait_type, -- Wait type, such: LCK_M_X indicates that it is waiting for obtaining the exclusive lock blocking_session_id -- blocking the session of the session_id Session from sys. dm_ OS _waiting_taskswhere session_id = 105 -- query the resource status of the blocked Session Request selec T resource_type, request_status, request_mode, request_session_idfrom sys. dm_tran_lockswhere request_session_id = 105 -- indicates that Session 2 has obtained a total of four locks during update, shared database locks, and two intention exclusive locks (locking tables and data pages ), -- a key lock locks the record to be updated. Only the Request status of the key lock is wait. -- the other three locks are in grant state, indicating that Session 2 has obtained the lock. -- Another method for viewing blocked sessions: -- view the execution request of the current Session select session_id, status, blocking_session_id, wait_type, wait_timefrom sys. dm_exec_requestswhere session_id = 105 -- configure the time for the statement to wait for the lock release -- set the lock request timeout period of the statement -- the timeout period is in milliseconds, after timeout, the system will return a locking error and an error will be returned: (1 line is affected) Message 1222, level 16, status 51, and 7th have exceeded the lock request timeout period. The statement has been terminated.

3. deadlock

When the two transactions lock the resources respectively and continue to request the resources that the other party has obtained, a deadlock will occur.

Cause of deadlock:

A. The session accesses the table in different sequence.

B. The session runs the transaction for a long time and updates many tables or rows in a transaction, which increases the possibility of conflict.

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

If these rows are on the same data page and two sessions upgrade the lock Granularity on the same page at the same time, a deadlock will occur.

Set lock_timeout 1000 -- Trace deadlock -- Session 1 set transaction isolation level serializablebegin tranupdate tset v = '20160901' where idd = 2 waitfor delay' 00: 00: 10 'Update tset v = '000000' where idd = 1commit -- Session 2 set transaction isolation level serializablebegin tranupdate tset v = '000000' where idd = 1 waitfor delay '00: 00: 10 'Update tset v = '000000' where idd = 2 commit

Enable another session to enable tracking:

/* ===================================================== ================================ Enable the tracking flag: dbcc traceon (trace #[,... n],-1) [With No_InfoMsgs] Check whether some or some flag bits are enabled or disabled: dbcc tracestatus (trace #[,... n],-1) [With No_InfoMsgs] 1. trace #: Specifies one or more trace flag numbers that need to be enabled or need to be checked. 2. -1: If-1 is specified, some trace flag 3 is opened globally. with No_InfoMsgs: When the command contains this parameter, disable DBCC to output informative messages ============================================== =========================================================== */-- Trace 1222 the detailed deadlock information can be returned to the SQL Server log-flag-1 indicates that the trace flag 1222 should be enabled globally for all SQL Server connections DBCC TraceOn (1222, -1) go -- verify whether the flag bit starts DBCC TraceStatusgo -- disable the flag bit DBCC TraceOff (1222,-1) go to set the deadlock priority -- set the priority of the deadlock, SET DeadLock_Priority Low | Normal | High | numeric-priority -- set deadlock_priority Low -- sets DeadLock_Priority Low -- SQL Server termination set deadlock_priority Normal -- reduces the possibility of connection termination, unless the other connection is also High or the value priority is greater than 5 set deadlock_priority High -- value priority:-10 to 10 values,-10 is the most likely to be terminated, and 10 is the least likely to be terminated, -- the larger the two numbers, the more difficult it is to terminate set deadlock_priority 10 in a deadlock.

I hope this article will help you design SQL Server database programs.