SQL Transaction Blocking On Foreign Key

[NewsBot]

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