deadlock on parent-child relationship

I have a program that inserts a row to a parent table and before it
commits then calls another program to insert rows to the child table.
This is causing a deadlock. When I looked at it, the
first program has an X lock on the primary key of the parent table and
the second program is trying to get a share lock on the index of the
parent table ?
Why is this happening ? How can I avoid it ?
Thanks
RogerMake sure the order of the tables in the from clause is the same in both
queries and consider using the UPDLOCK table hint.
Read more here:
http://msdn.microsoft.com/library/d... />
a_8i93.asp
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
ML
http://milambda.blogspot.com/|||You can't avoid it unless both updates occur on the same connection, or
unless you bind the second connection to the first. Look up sp_bindsession
in BOL. Exclusive locks are held on an inserted row until it is committed,
so no other transaction can see the row until it's committed (unless you use
WITH(NOLOCK), which should be avoided whenever possible).
I prefer to dump an update that contains related information into temp
tables so that they can be committed using set-based operations within a
stored procedure, but that can have performance and scalability implications
depending on whether tempdb is on it's own disk subsystem and on whether
there's enough memory so that the contents of the temp tables aren't
migrated out to disk. Set-based operations minimize lock duration, index
maintenance and transaction logging, so it's a trade-off. Without testing,
it cannot be determined which method provides the best performance and
scalability for a particular update scenario. However, I prefer to keep
transaction processing within stored procedures because I've found that
troubleshooting and repairing blocking and deadlock problems is less
expensive if all transactions are contained in procedures. It's a lot
easier to add a SELECT WITH(UPDLOCK) to a stored procedure than to alter,
recompile, and redeploy a client program.
"Roger" <wonderinguys@.gmail.com> wrote in message
news:1138809010.484456.142920@.z14g2000cwz.googlegroups.com...
>I have a program that inserts a row to a parent table and before it
> commits then calls another program to insert rows to the child table.
> This is causing a deadlock. When I looked at it, the
> first program has an X lock on the primary key of the parent table and
> the second program is trying to get a share lock on the index of the
> parent table ?
> Why is this happening ? How can I avoid it ?
> Thanks
> Roger
>|||the program that inserts the child table is in a new spid...a different
one from the parent program. Why is that ? i am from DB2 running on
mainframe where this never happens. So need some help with this.|||On 2 Feb 2006 12:21:33 -0800, Roger wrote:

>the program that inserts the child table is in a new spid...a different
>one from the parent program. Why is that ? i am from DB2 running on
>mainframe where this never happens. So need some help with this.
Hi Roger,
That's the cause of your deadlock, then.
This surely doesn't happen automatically. In fact, you have to work
pretty hard to get a subprocedure to run in a different spid in SQL
Server. (Doing it from the client is easier, but still takes some
effort).
Can you post (snippets of) your code?
Hugo Kornelis, SQL Server MVP