SQL Blocking: Database locks everyday

Question

We have recently migrated to RDS.
Shortly after migration, we have started seeing SQL blocking.
It is an update lock LCK_M_U.

I see a hobt_id in the lock resource type field from activity monitor.

I run below query and it points me to a table and returns a partition id, table name and index id .
This table has one clustered index on it on the primary key column.

SELECT object_name(object_id), partition_id, index_id, partition_number
hobt_id, rows
FROM sys.partitions
WHERE hobt_id=72057598250450944;

Please let me know what does that mean? Does it give me more details about where the lock exists and what can i do to fix it ?

Please let me know if more info is needed.

Thank you,
Mounika

Answer 1

Hi @Mounika Vemula ,

Please let me know what does that mean?

It means a partition below the table is locked.
hobt_id: Indicates the ID of the data heap or B-tree (HoBT) that contains the rows for this partition.

Does it give me more details about where the lock exists and what can i do to fix it ?

You have already get the index_id and table name.
You have to optimize your table design.If this index is not necessary, please delete it.

And you can also reference resolutions from this doc.: sql-server-wait-type-lck-m-u

Please advise. How can we avoid this.

For your case: You have to optimize your table design.If this index is not necessary, please delete it.

tips:
Minimize unnecessary indexes. The greater the number of indexes, the more locks may be required.
But also strictly avoid the occurrence of table scans. If you only modify a small part of the table records, try to use Index Seek to avoid the execution plan of the full table scan.

for select:

(In a non-"uncommitted read" isolation level).
(1) When the query is running, a shared lock will be added to each read record or key value. If the record does not need to be returned, the lock will be released. If the record needs to be returned, it depends on the isolation level. If it is "committed to read", it is also released, otherwise it is not released.

(2) For each index used, SQL Server will also add a shared lock to the above key value.

(3) For each page read, SQL Server will add an intention lock.

(4) The more pages and records that the query needs to scan, the more the number of locks will be. The more indexes used in the query, the greater the number of locks.

So,if you want to reduce the probability of a select query being blocked by others or blocking others, database designers can do:

(1) Try to return as few records as possible. The more results returned, the more locks are needed.

(2) If the returned result set is only a small part of all the records in the table, try to use Index Seek to avoid the execution plan of full table scan.

(3) If possible, design an appropriate index. Avoid SQL Server to find data through multiple indexes.

Of course, these are all for the isolation level above "committed read". If you choose "Uncommitted Read", SQL Server will not apply for these shared locks, and blocking will not occur.

for update:

(1) For each index used, SQL Server will add a U lock to the above key value.

(2) SQL Server only adds X locks to the records or key values ​​to be modified.

(3) The more indexes used to modify the column, the greater the number of locks.

(4) The more pages scanned, the more intention locks will be. During the scanning process, all scanned records will be locked, even if the above is not modified.

So,if you want to reduce the probability of an UPDATE being blocked by others, in addition to paying attention to its query part, the database designer should also do:

(1) Modify as few record sets as possible. The more records are modified, the more locks are required.

(2) Minimize unnecessary indexes. The greater the number of indexes, the more locks may be required.

(3) But also strictly avoid the occurrence of table scans. If you only modify a small part of the table records, try to use Index Seek to avoid the execution plan of the full table scan.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

The fact that you get different queries could indicate that the spid in question is moving on and is running different queries. But if it is running with the any of the isolation levels REPEATABLE READ or SERIALIZABLE inside a transaction, it will stick to the locks, and that can of course cause blocking.

It is difficult to answer the first question with only pieces of information, so I prefer to pass. If possible, I would like to see the output from my beta_lockinfo, http://www.sommarskog.se/sqlutil/beta_lockinfo.html, which collects a lot of information to troubleshoot blocking.

For the second question, I believe that you are on the right track, although, I have not worked a lot with lock resources myself.

Answer 3

A LCK_M_U is an update lock on a table/index (not on a database), that's quite normal when you update data in a database.
What the exact problem here?

Answer 4

So what does this SELECT look like? Is it using indexes, or is scanning the table? In such case, there is no wonder that the UPDATE statement is blocked.

While you can avoid that readers and writers block each other by enabling READ_COMMITTED_SNAPSHOT on the database, I think the answer in your case that you need to analyse the two statements in question more closely to see why they are clashing.

Answer 5

@Olaf Helper @m @Erland Sommarskog

Thanks a lot for your detailed explanation. We are actually considering to disable this clustered index. I will explain in detail about the problem.

1. An UPDATE query is being blocked by a SELECT query. When i pick up the SPID of this SELECT query i get different tables in these three cases.
   For example,

a)dbcc inputbuffer(SELECT SPID) --> (@P0 nvarchar(4000))SELECT TableA.C1 FROM TableA WHERE (TableA.C1 = @P0)

b) SELECT * FROM sys.sysprocesses s CROSS APPLY sys.dm_exec_sql_text (sql_handle) WHERE s.spid = SELECT SPID --> (@P0 nvarchar(4000))SELECT TableB.C1 FROM Table WHERE (TableB.C1 = @P0)

c) Activity Monitor --> Blocking SPID --> Right click and select 'details' --> (@P0 nvarchar(4000))SELECT TableC.C1 FROM TableC WHERE (TableC.C1 = @P0)

In these three cases, its the same SPID but three different SELECTS are returned. Any idea what does that mean? Which of these three should i consider is causing lock? I think its tableB because

2. Activity monitor displays the hotbid. When i check more details about this hotbid , it points me to tableB and a clustered index on the same tableB. This index is created on this C1 coulumn that is appearing in the a,b,c SELECT queries. Please let me know if it means that lock is being caused by this index on this column?

3.. I am using below command to grab the wait resource and when i pick up this wait resource and run the second query below, it points me to a row.
Does that mean this row is where lock is placed ? Please advise.
USE [master]
GO
SELECT
session_id,blocking_session_id,wait_time,wait_type,last_wait_type,wait_resource,transaction_isolation_level,lock_timeout
FROM sys.dm_exec_requests WHERE blocking_session_id <> 0
GO

---

SELECT
* FROM dbo.T29 (NOLOCK)
WHERE %%lockres%% = '(<wait_resource_id>)';
GO

It would be great if you could clarify me on these. I am very confused because a,b and c returns three different select queries (pointing to different tables) for the same SPID.