Locking with ObjectID 0

Bih Luh Hew 1

Hi,

Recently I keep getting blocking from an application code that uses MSDTC with Serializable isolation level. The head blocked was waiting for a an Object where ID is 0, when too many of them, it caused deadlock.

How can we know what was the object since the objectID is 0 ?

Regards,
Bih Luh

Erland Sommarskog 112.7K Reputation points MVP

2021-06-18T21:54:39.063+00:00

A better question may be if the serializable isolation level is really needed. That isolation level is quite prone to cause deadlocks. So unless the application developers have a good reason to use that isolation level, changing it to read committed might reduce the number of deadlocks.

2 answers

CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2021-06-18T07:23:35.767+00:00
Hi @Bih Luh Hew ,

> How can we know what was the object since the objectID is 0 ?

If you see the ObjectId is 0, that means there was no metadata found. This could be because:

•The table that the page was part of has been deleted since the page corruption was logged
•The system catalogs are corrupt in some way
•The page is corrupt and so incorrect values were used to look up the metadata

Please refer to the blog Finding a table name from a page ID.

We can using below T-SQL to check object name.

USE DBname; GO SELECT OBJECT_NAME (objectID); GO

Please refer to MS document OBJECT_NAME (Transact-SQL).

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Bih Luh Hew 1 Reputation point

2021-06-18T07:51:35.16+00:00

@CathyJi-MSFT ,

We have DBCC every week but there wasn't any corruption reported.

Regards,
Bih Luh
Please sign in to rate this answer.
CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2021-06-18T08:08:04.513+00:00

Hi @Bih Luh Hew ,

Did you run DBCC CHECKDB after you got blocking?

Bih Luh Hew 1 Reputation point

2021-06-18T08:30:08.847+00:00

Hi @CathyJi-MSFT ,

We have weekly DBCC CheckDB on Primary table.
We have daily DBCC check on Secondary replica too, but did not find any corruption.

I just run DBCC CheckTable on the table did not find any corruption too.

CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2021-06-18T09:33:50.813+00:00

Hi @Bih Luh Hew ,

There isn’t an object ID of 0 in the system. Did the value is Null when you run below T-SQL? Please share us the result of below query. Please change the DBname to the database that the database ID is 10.

USE DBname; GO SELECT OBJECT_NAME (0); GO

Bih Luh Hew 1 Reputation point

2021-06-18T13:25:06.857+00:00

Hi @CathyJi-MSFT ,

It is NULL

and from the blocked process report, it shows objectID 1713, but this object does not exists too.

CathyJi-MSFT 22,306 Reputation points Microsoft Vendor

2021-06-21T01:40:16.003+00:00

Hi @Bih Luh Hew ,

Did you check the object ID 85575343. By the way, what is version of your SQL server? Using select @@version to check this.

Did you check the state of Server Process ID 8? Did it could be killed?

Bih Luh Hew 1 Reputation point

2021-06-21T02:00:54+00:00

Hi @CathyJi-MSFT ,

Object ID 85575343 is a user table. This table has 8 indexes, the transaction will run 2 update statement to update 2 different row in the same table using primary Key. Since affected columns are also part of the indexes , it uses a cluster index seek first then perform cluster update.
I cannot delete any of the index because all of them all widely used in others queries

This is a SQL Server 2014 EE.

Developer need time and downtime to make the code change, so not likely we can change the code logic soon.

I was wondering if the objectID 0 related to MSDTC ?

I believe SPID 8 was the system process who capture blocking, we can't kill a system background process.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Locking with ObjectID 0

2 answers

Your answer