question

BihLuhHew-4648 avatar image
0 Votes"
BihLuhHew-4648 asked BihLuhHew-4648 edited

Locking with ObjectID 0

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 ?

<process status="running" waitresource="OBJECT: 10:0:5 " spid="98" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-06-18T13:48:43.553" lastbatchcompleted="2021-06-18T13:48:43.550" lastattention="1900-01-01T00:00:00.550" clientapp="EntityFrameworkMUE" hostname="XXXXXXX" hostpid="15216" loginname="XXXXXXX" isolationlevel="serializable (4)" xactid="13275674392" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack/>

Regards,
Bih Luh

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @BihLuhHew-4648,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BihLuhHew-4648 avatar image
0 Votes"
BihLuhHew-4648 answered BihLuhHew-4648 edited

@Cathyji-msft ,

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

Regards,
Bih Luh

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BihLuhHew-4648 ,

Did you run DBCC CHECKDB after you got blocking?

0 Votes 0 ·

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.

0 Votes 0 ·

Hi @BihLuhHew-4648,

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
0 Votes 0 ·
Show more comments