SQL Transaction Blocking On Foreign Key

Joined
Jan 10, 2007
Messages
43,898
Location
In The Machine
I'll try to keep this short.* I have about 4 inserts I'm doing.

The first insert is into a users table in a users database.

The second insert is into a customers table in a billing database, that returns a customer id.

The next 3 inserts are into other tables that have customerid as a foreign key.

The odd thing is the first two are fine.* Which are an addresses table insert and a credit card table insert.* I pass in the customerid and it inserts fine.

However, the one I'm having trouble with is a table called product subscriptions.

These are all simple inserts.* The product subscriptions doesn't have anymore relationships than the others, and the relationships it does have doesn't have any relationships to the customers or other tables.* It only has a relationship to products, etc.

Now, to backup, all of these are controlled by a sqltransaction ado.net object.

When first debugging, I commented out hte product subscriptoins insert, and it worked fine and didn't hang.

I also tried moving it up before the other two inserts, but then it hung.

Also, what is odd is I did a select on all tables with no locking and the product subscriptions insert is happening, the data is there.

To get it to work, I modified the relationship to NOT ENFORCE RI on inserts or updates.

So, it's now working.* However, it doesn't make any sense.* The ONLY difference I can see between the product subscriptions insert and others, is that I pass in a CODE, that I then do a select on the product tables for the product id,*the other two I just pass in directly the ID.

I know it's hard to debug this without anyone seeing the actual code.* I thought I'd post this to see if anyone ran across a similar problem.

More...

View All Our Microsoft Related Feeds
 
Back
Top