Peer review on a deadlock?

Question

Peer review on a deadlock?

Lowell

SSC Guru

Points: 323518
More actions
February 29, 2016 at 9:24 am

#308947

I've got a deadlock that is featuring a PAGE lock on another table, on insert, and i cannot seem to make out what is going on;
it seems to be the parallelism a single row insert? or another process with parallelism that doesn't have a statement, so i can't tell what it's doing to block?
if someone could help me decrypt this a bit more, i'd appreciate it;
as far as environment, it's SQL2008R2 Enterprise,
Maxdop = 4 and cost threshhold for parallelism is 65
I've got an email and xml shredder i slapped together that is trying to tell me some details:
Deadlocks
information
Deadlock Graph Report
The following Input buffers could be read directly from the XML, and were the available commands that were involved in the deadlock.
AllInputBuffersDataBaseNamewaitresourceWaitResourceDetailsstatustransactionnamehostnameloginname
(@p0 int,@p1 varchar(8000),@p2 nvarchar(4000),@p3 nvarchar(4000))INSERT INTO [dbo].[AuditValue]([AuditID], [MemberName], [OldValue], [NewValue]) VALUES (@p0, @p1, @p2, @p3) SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value] ProductionDB PAGE: 18:8:233791 dbo.TheLogTable suspended user_transaction GDC-SQL-P03
Proc [Database Id = 18 Object Id = 1301631730] ProductionDB PAGE: 18:8:233791 dbo.TheLogTable suspended user_transaction GDC-SQL-P03
{no input buffer} ProductionDB PAGE: 18:8:233791 dbo.TheLogTable suspended user_transaction GDC-SQL-P03
{no input buffer} ProductionDB PAGE: 18:8:233791 dbo.TheLogTable suspended user_transaction GDC-SQL-P03
{no input buffer} ProductionDB PAGE: 18:8:233791 dbo.TheLogTable suspended user_transaction GDC-SQL-P03
{no input buffer} ProductionDB PAGE: 18:8:233791 dbo.TheLogTable suspended user_transaction GDC-SQL-P03
{no input buffer} ProductionDB PAGE: 18:8:233791 dbo.TheLogTable suspended user_transaction GDC-SQL-P03
{no input buffer} ProductionDB PAGE: 18:8:233791 dbo.TheLogTable suspended user_transaction GDC-SQL-P03
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Have you run sp_WhoIsActive? Not sure if that will help, but the only other thing that I can think of would be running SQL Profiler.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 2

I would start by looking at the following things:

> <frame procname="" line="89" stmtstart="9704" stmtend="18556" sqlhandle="0x03001200f252954d9248480120a400000100000000000000" />

This 9,000-character-long call looks like it's part of a much-larger batch. Examine that for locks that are taken through the call, and what locks may be held through the life of the batch, and may have been taken before the current call began. If it's still in cache, you can view it by passing the sql_handle value into sys.dm_exec_sql_text:

SELECT * FROM sys.dm_exec_sql_text(0x03001200f252954d9248480120a400000100000000000000)

><inputbuf> Proc [Database Id = 18 Object Id = 1301631730] </inputbuf>

Look at the code of that procedure (SELECT OBJECT_NAME(1301631730, 18) to determine which procedure)

> waitresource="PAGE: 18:8:233791"

What's on that page? What table, what index, and where in the index?

Execute the following to view details about the page:

-- output results to text ([ctrl]+T before you execute)

DBCC TRACEON(3604);

DBCC PAGE(18, 8, 233791,3);

The top of the output is the page header; the following items in it may have value here:

- m_type: 1 = data row page, 2 = index page, 3 = BLOB page. Values above that are metadata pages.

- Metadata: ObjectId: This is the owning object (table) if m_type < 4 Pass this value to the OBJECT_NAME function, or look in sys.tables with this value as object_id

- Metadata: IndexID: if m_type < 4, then this value plus the Metadata: ObjectId value are the keys on the sys.indexes view.

The data contents of the page will be dumped beneath the header. Poke through that to see what rows are on the page. That may help your efforts.

Seeing what was actually happening at the time of the deadlock often points you toward a solution.

-Eddie

Eddie Wuerch
MCM: SQL

Lowell SSC Guru Points: 323518 More actions · Answer 3

Eddie that got me over the logjam, thanks;

grabbing the object_name you mentioned, and adding some new smart parsing to my email that shreds xml to get that object name in the future is going to help;

Now i know the offending procedure, which does select but then an update, and that has a catch all query in it featuring lots of OR statements, so that's my problem child, i just couldn't see where to look.

i was thinking background processes, and it was right there; thank you for the help!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!