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

DBDesign Q

Can two entities have more than one relationship between them?

A(A_ID PK,.., bID FK)

B(B_ID PK,.., aID FK)

A.bID refers to B.B_ID
B.aID refers to A_ID

2. Does it make sense?

Product (ProductID PK, Name,, SpecialAttrID FK)

Attribute (AttrID PK, Name,.,ProductID FK)

Product can have many attributes. One attribute belongs to only one product.
There is only one main("special") attribute for each product

Thank you in advance,
Andynet__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311232036.e0eeae5@.posting.google.com>...
> Can two entities have more than one relationship between them?
> A(A_ID PK,?.., bID FK)
> B(B_ID PK,?.., aID FK)
> A.bID refers to B.B_ID
> B.aID refers to A_ID
Yes, for example:

Employee E1 works in Department D1
Department D1 is managed by Employee D2

> 2. Does it make sense?
> Product (ProductID PK, Name,?, SpecialAttrID FK)
> Attribute (AttrID PK, Name,?.,ProductID FK)
> Product can have many attributes. One attribute belongs to only one product.
> There is only one main("special") attribute for each product
> Thank you in advance,
> Andy

A lot of people do this, seduced by how generic and flexible it is.
It also saves the bother of actually working out what those pesky
atrributes actually are in advance (aka "analysis"). The big downside
comes when you want to query this data, and find out how complex the
queries become. For example:

select p.name
from product p, attribute a1, attribute a2, attribute a3
where p.productid = a1.productid
and a1.name = 'Color'
and a1.value = 'Red'
and p.productid = a2.productid
and a2.name = '100'
and a2.value = 'Medium'
and p.productid = a3.productid
and a3.name = 'Width'
and a3.value = '7.5'

instead of:

select p.name
from product p
where p.color = 'Red'
and p.length = 100
and p.width = 7.5;|||Hi,

I think i does'nt make sence.

The reason is for the creation of each table , another table should
exists already vice-versa.

So i think u can not create even a single table as that regard.

With thanks|||It is correct on an ERD, but incorrect in a relational schema.

"Andy" <net__space@.hotmail.com> wrote in message
news:edb90340.0311232036.e0eeae5@.posting.google.co m...
> Can two entities have more than one relationship between them?
> A(A_ID PK,..., bID FK)
> B(B_ID PK,..., aID FK)
> A.bID refers to B.B_ID
> B.aID refers to A_ID
> 2. Does it make sense?
> Product (ProductID PK, Name,., SpecialAttrID FK)
> Attribute (AttrID PK, Name,..,ProductID FK)
> Product can have many attributes. One attribute belongs to only one
product.
> There is only one main("special") attribute for each product
> Thank you in advance,
> Andy|||"Alan" <alan@.erols.com> wrote in message
news:bpthmj$1t348d$1@.ID-114862.news.uni-berlin.de...
> It is correct on an ERD, but incorrect in a relational schema.

That's a rather astounding statement. How do you propose to support your
assertion?

> "Andy" <net__space@.hotmail.com> wrote in message
> news:edb90340.0311232036.e0eeae5@.posting.google.co m...
> > Can two entities have more than one relationship between them?
> > A(A_ID PK,..., bID FK)
> > B(B_ID PK,..., aID FK)
> > A.bID refers to B.B_ID
> > B.aID refers to A_ID
> > 2. Does it make sense?
> > Product (ProductID PK, Name,., SpecialAttrID FK)
> > Attribute (AttrID PK, Name,..,ProductID FK)
> > Product can have many attributes. One attribute belongs to only one
> product.
> > There is only one main("special") attribute for each product
> > Thank you in advance,
> > Andy|||"Andy" <net__space@.hotmail.com> wrote in message <news:edb90340.0311232036.e0eeae5@.posting.google.co m>...

> Can two entities have more than one relationship between them?
> A(A_ID PK,..., bID FK)
> B(B_ID PK,..., aID FK)
> A.bID refers to B.B_ID
> B.aID refers to A_ID
> 2. Does it make sense?
> Product (ProductID PK, Name,., SpecialAttrID FK)
> Attribute (AttrID PK, Name,..,ProductID FK)
> Product can have many attributes. One attribute belongs to only one product.
> There is only one main("special") attribute for each product

Maybe you really want an "extender table" approach, in which
the Product table holds attributes which all products have,
and additional tables to hold attributes which some, but not
all, products have, perhaps like so:

Product (ProductID PK, Name, Manufacturer, Price, Mass, Volume, ...)

Book (ProductID PK/FK, Author, Pages, Binding, ...)

Album (ProductID PK/FK, Artist, Tracks, Media, PlayingTime, ...)

Software (ProductID PK/FK, Platform, Media, ...)

--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||raghuraman_ace@.rediffmail.com (Raghuraman) wrote in message news:<66c7bef8.0311240532.731c4819@.posting.google.com>...
> Hi,
> I think i does'nt make sence.
> The reason is for the creation of each table , another table should
> exists already vice-versa.
> So i think u can not create even a single table as that regard.
>
>
> With thanks

It is still not unusual for two tables to cross with a foreign key in
each pointing to the other. Circular relationships are not rare. As
another example,
a table of a publishers books can be linked with a table of the
publishers authors (books have authors and authors write books).

They can be murder to load (chicken or egg type question) and maintain
(deletes have the same problem), but they are not unusual.|||Whilst you can techincally create references this way (circular), I
would recommend not doing so. With a small and simple system, this
approach appears to be a good idea, however, I would suggest this is
bad practice as when you work on larger systems the complexity
increases dramatically. Don't get into the habit of doing this if you
can.

Our old back office system at work (pre me) was built with the
majority of references being circular. There were even tables which
held information on which circular link was to be used (depending on
the data). At the size of db we work with, this was a complete
nightmare. It was one of the worst designed systems I have ever seen.
Everyone on this forum would laugh if they saw the design. We have
since changed this as you would expect.

Yes, you can work with them, just explain to your colleagues why you
will be found mumbling to yourself later when it's become too complex.
If you absolutely have to do this, FFS make sure it's documented for
future developers.

Keep it simple. It will make a huge difference in the long run ! I'd
recommend spending a little longer working out an easier none circular
way of referencing the data.

That's my tuppence. You may well disagree.

"Joe \"Nuke Me Xemu\" Foster" <joe@.bftsi0.UUCP> wrote in message news:<1069701707.142710@.news-1.nethere.net>...
> "Andy" <net__space@.hotmail.com> wrote in message <news:edb90340.0311232036.e0eeae5@.posting.google.co m>...
> > Can two entities have more than one relationship between them?
> > A(A_ID PK,..., bID FK)
> > B(B_ID PK,..., aID FK)
> > A.bID refers to B.B_ID
> > B.aID refers to A_ID
> > 2. Does it make sense?
> > Product (ProductID PK, Name,., SpecialAttrID FK)
> > Attribute (AttrID PK, Name,..,ProductID FK)
> > Product can have many attributes. One attribute belongs to only one product.
> > There is only one main("special") attribute for each product
> Maybe you really want an "extender table" approach, in which
> the Product table holds attributes which all products have,
> and additional tables to hold attributes which some, but not
> all, products have, perhaps like so:
> Product (ProductID PK, Name, Manufacturer, Price, Mass, Volume, ...)
> Book (ProductID PK/FK, Author, Pages, Binding, ...)
> Album (ProductID PK/FK, Artist, Tracks, Media, PlayingTime, ...)
> Software (ProductID PK/FK, Platform, Media, ...)|||In article <edb90340.0311232036.e0eeae5@.posting.google.com>,
net__space@.hotmail.com says...
> Can two entities have more than one relationship between them?

There can be many individual relationships between tables. However, the
question is, does your design require it?

> 2. Does it make sense?
> Product (ProductID PK, Name,?, SpecialAttrID FK)
> Attribute (AttrID PK, Name,?.,ProductID FK)
> Product can have many attributes. One attribute belongs to only one product.
> There is only one main("special") attribute for each product

I would think that what you might want could be satisfied with a simple
one-to-many:

CREATE TABLE Product (
ProductID int NOT NULL,
ProductName varchar(18) NULL,
CONSTRAINT PK1 PRIMARY KEY CLUSTERED (ProductID)
)

CREATE TABLE Attribute (
AtrributeID int NOT NULL,
ProductID int NOT NULL,
AttributeName varchar(18) NULL,
MainAttribute bit NULL,
CONSTRAINT PK2 PRIMARY KEY CLUSTERED (AtrributeID),
FOREIGN KEY (ProductID)
REFERENCES Product(ProductID)
)

Other appropriate constraints (depending on your target platform) would
limit one MainAttribute being "true" within a set of attributes for one
given product.

Alternatively, does one attribute (guessing something like "height")
really only occur for one product or many? You might have a many-to-many
relationship here. It might be that you need three tables, with one
being an associative table between Product and Attribute:

CREATE TABLE Attribute(
AtrributeID int NOT NULL,
AttributeName varchar(18) NULL,
CONSTRAINT PK2 PRIMARY KEY CLUSTERED (AtrributeID)
)

CREATE TABLE Product(
ProductID int NOT NULL,
ProductName varchar(18) NULL,
CONSTRAINT PK1 PRIMARY KEY CLUSTERED (ProductID)
)

CREATE TABLE ProductAttribute(
ProductID int NOT NULL,
AtrributeID int NOT NULL,
MainAttribute bit NULL,
CONSTRAINT PKProductAttribute
PRIMARY KEY NONCLUSTERED (ProductID, AtrributeID),
FOREIGN KEY (ProductID)
REFERENCES Product(ProductID),
FOREIGN KEY (AtrributeID)
REFERENCES Attribute(AtrributeID)
)

These are some alternatives for you to consider.

------------
Paul Tiseo, Systems Programmer
Research Computing Facility, Mayo Clinic
tiseo128.paul23@.mayo.edu
(please remove numbers to email me)