Re: Understanding how my code is causing deadlocks!
From: Kevin Kraus (KevinKraus_at_discussions.microsoft.com)
Date: 11/04/04
- Next message: Joseph Geretz: "Help! Are my DTS Packages gone for good?"
- Previous message: Sasan Saidi: "RE: Copy DB structure into a new DB"
- In reply to: Wayne Snyder: "Re: Understanding how my code is causing deadlocks!"
- Next in thread: AnthonyThomas: "Re: Understanding how my code is causing deadlocks!"
- Reply: AnthonyThomas: "Re: Understanding how my code is causing deadlocks!"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 4 Nov 2004 10:09:02 -0800
Can you help me elaborate the scenario for me? Here is what I am getting
from your post of conversion deadlocks and locking mode compatibility.
If I get an Updlock (U) on the resource (I assume it defaults to page
level?) the only other compatible locks are shared (S) and intent shared
(IS). So my assumption would be that another process has an S or IS lock and
is trying to convert to a U or higher lock and they are waiting for me to
remove my U, thus the deadlock? is this a possible scenario?
Is it possible to acquire an S or IS lock and convert it to a U or higher
without releasing the S or IS first?
Thanks for the post and help
Kevin
"Wayne Snyder" wrote:
> You could be getting a conversion deadlock. AN update statement begins by
> getting updlock locks, and then updating them to exclusinve locks. You can
> still be deadlocking against other processes... As the other poster
> suggested , enable the trace flags and worry through the output you get..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
>
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Kevin Kraus" <KevinKraus@discussions.microsoft.com> wrote in message
> news:C5230DDC-D73A-4AA7-838F-CA9536A6E56D@microsoft.com...
> > Hello everyone,
> >
> > I have a windows service that has a job of periodically pulling data from
> > SQL Server and sending it to a web service. The way the service pulls the
> > data is as follows:
> >
> > - Update statement to update batch of records (1 table) to be "pulled"
> > - Select statement to pull records marked in batch (1 join to other table
> > for extra data)
> > - Send to Web Service
> > - Update statement to update batch of records (1 table) with status of
> > "pulled" (Periodic deadlocks produced here)
> >
> > Background on the service code and process:
> > - Component instanciated by windows service to do the sql calls is a .NET
> > ServicedComponent with TransactionOption.NotSupported.
> > - All SQL is embedded in code.
> > - First update statement and select statement are sent to SQL server in
> one
> > call.
> > - Second Update statement is made in separate sql server call
> > - The host application that owns this database is out of my control and I
> > don't know how it makes it's SQL calls to the resources I am trying to
> update.
> >
> > My understanding of deadlocks is that my process thread needs to have a
> lock
> > on a resource (r1) and try to acquire another lock on a different resource
> > (r2) while a different process thread has r2 locked and is trying to
> access
> > r1 that I have locked. I don't see how I have any locks based off of my
> > simple update, select, update statements.
> >
> > I need enlightenment on what possible locking scenarios might be causing
> > these deadlocks.
> >
> > Thanks for your help
> >
> > Kevin
>
>
>
- Next message: Joseph Geretz: "Help! Are my DTS Packages gone for good?"
- Previous message: Sasan Saidi: "RE: Copy DB structure into a new DB"
- In reply to: Wayne Snyder: "Re: Understanding how my code is causing deadlocks!"
- Next in thread: AnthonyThomas: "Re: Understanding how my code is causing deadlocks!"
- Reply: AnthonyThomas: "Re: Understanding how my code is causing deadlocks!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
