Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.
Overview
This topic describes how to view the lock information when you use apsaradb for RDS SQL Server.
Detail
Alibaba Cloud reminds you that:
- Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
- You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
- If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.
You can view the lock information in the
sys.dm_tran_locks system view.- The lock information of the database.
You can execute the following SQL statement to query which databases have locks:
select str(request_session_id, 4, 0) as spid, convert(varchar(20), db_name(resource_database_id)) as DB_Name, case when resource_database_id = db_id() and resource_type = 'OBJECT' then convert(char(20), object_name(resource_Associated_Entity_id)) else convert(char(20), resource_Associated_Entity_id) end as object, convert(varchar(12), resource_type) as resrc_type, convert(varchar(12), request_type) as req_type, convert(char(3), request_mode) as mode, convert(varchar(8), request_status) as status from sys.dm_tran_locks order by request_session_id desc;
The following command output is returned.
Note: the MODE column is a lock MODE, as described below:
- Share (S) is used for operations that do not change or update data (read-only operations), such as SELECT statements.
- Update (U) is used in resources that can be updated. Prevent common forms of deadlock that occur when multiple sessions read, lock, and possibly subsequent resource updates.
- It is used for data modification operations, such as INSERT, UPDATE, and DELETE, to ensure that multiple updates are not performed on the same resource at the same time.
- Intent locks are used to establish a hierarchy of locks. The type of intent lock IS: sharing (IS), intention to sort (IX), and sharing with intention to sort (SIX).
- Schema locks are used when performing operations that depend on the table schema. The types of schema lock are modify (Sch-M) and stable (Sch-S).
- Bulk update (BU) is used when bulk copying data into a table and the TABLOCK hint is specified.
- Data table lock information
You can execute the following SQL statement to query which data tables have locks:
select request_session_id sessionid, resource_type type, convert(varchar(20), db_name(resource_database_id)) as db_name, OBJECT_NAME(resource_associated_entity_id, resource_database_id) objectname, request_mode rmode, request_status rstatus from sys.dm_tran_locks where resource_type in ('OBJECT')The following command output is returned.
Description
- sessionid is the process that locked the table.
- objectname is the name of the locked table.
Application scope
- ApsaraDB RDS for SQL Server