Re: Unexpected deadlock
From: Quentin Ran (xyz_at_abc.com)
Date: 09/22/04
- Next message: Tibor Karaszi: "Re: Problems Backing Up"
- Previous message: Tibor Karaszi: "Re: Back up on remote computer."
- In reply to: Karl Gram: "Re: Unexpected deadlock"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 22 Sep 2004 11:22:51 -0500
So I take that your "simple insert" is not a select from the temp table the
other statement is selecting into.
Are you by chance using hyperthreading? Have you run Profiler?
"Karl Gram" <KarlGram@discussions.microsoft.com> wrote in message
news:1F4C2590-B5BA-4755-B588-BF42C9B0E4BE@microsoft.com...
> Me neither, but it is there as you can see in the output given....
>
> "Quentin Ran" wrote:
>
> > I can not see why there would be a deadlock -- blocking possible, but
> > deadlock no, unless your insert statement is something like
> >
> > insert into MYTABLE
> > select * from #PENDING
> >
> >
> > "Karl Gram" <KarlGram@discussions.microsoft.com> wrote in message
> > news:4CB4BF65-0FB2-4D45-884E-741105027E00@microsoft.com...
> > > Hi,
> > >
> > > Recently I encountered a deadlock situation. I have seen many
deadlocks,
> > but
> > > this one looks a bit strange to me. Maybe one of you can explain to me
why
> > > SQL server behaves the way it does?
> > >
> > > The deadlock happens on a dual CPU based machine. Below is the output
of
> > the
> > > deadlock.
> > >
> > > For your understanding SPID 289 (running on Node 1) executes a normal
> > INSERT
> > > statement on table MYTABLE. SPID 281 (on Node 2) does a SELECT
DISTINCT ID
> > > INTO #PENDING FROM MYTABLE
> > >
> > > The statements above are not the exact SQL statements, but are
> > representative.
> > >
> > >
> > > Deadlock output:
> > >
> > > Node:1
> > > RID: 16:1:26719:117 CleanCnt:1 Mode: X Flags: 0x2
> > > Grant List 1::
> > > Owner:0x4b291940 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:289
> > ECID:0
> > > SPID: 289 ECID: 0 Statement Type: INSERT Line #: 1
> > > Input Buf: RPC Event: xxxxx
> > > Requested By:
> > > ResType:LockOwner Stype:'OR' Mode: S SPID:281 ECID:0 Ec:(0x3A72D530)
> > > Value:0x31
> > >
> > > Node:2
> > > TAB: 16:149575571 [] CleanCnt:1 Mode: S Flags: 0x0
> > > Grant List 0::
> > > Owner:0x38df9540 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:281
> > ECID:0
> > > SPID: 281 ECID: 0 Statement Type: SELECT INTO Line #: 20
> > > Input Buf: Language Event: xxxxx
> > > Requested By:
> > > ResType:LockOwner Stype:'OR' Mode: IX SPID:289 ECID:0
Ec:(0x32A27510)
> > > Value:0x2
> > >
> > > Victim Resource Owner:
> > > ResType:LockOwner Stype:'OR' Mode: S SPID:281 ECID:0 Ec:(0x3A72D530)
> > > Value:0x31
> > >
> > >
> > > My interpretation of this output is that the INSERT (SPID 289) causes
an
> > > eXclusive lock on the new row that is inserted. At the same time, the
> > SELECT
> > > INTO (SPID 281) requests a Shared lock on the table. Now SPID 289
wants to
> > > put its Intent eXclusive (IX) lock on the table/page but is blocked by
the
> > > Shared lock request of SPID 281.
> > >
> > > Why doesn't SQL Server put a IX lock on the table and page before
> > inserting
> > > the record and thereby making sure it can lock the table so it won't
get
> > > deadlocked with a shared lock request?
> > >
> > >
> > > ---
> > > Karl Gram
> > > www.gramonline.com
> >
> >
> >
- Next message: Tibor Karaszi: "Re: Problems Backing Up"
- Previous message: Tibor Karaszi: "Re: Back up on remote computer."
- In reply to: Karl Gram: "Re: Unexpected deadlock"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
