Tech Off Post

Single Post Permalink

View Thread: SQL Transaction Blocking On Foreign Key
  • User profile image

    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 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.