Tech Off Thread

4 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

SQL Transaction Blocking On Foreign Key

Back to Forum: Tech Off
  • 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.

  • User profile image

    Also, I know for a fact it's blocking on the Customers table and the resource type is a KEY.

    I ran one of those sql locking stored procedures.  It shows a WAIT on the customers table, the key resource type.

  • User profile image

    I take it you are understanding why the block is occurring, and you are asking for a technical way to avoid the block?

    Just to be clear before I throw anything in here..

  • User profile image


    Well, I guess I'm looking for the proper solution.

    For a number of reasons, I don't want to fix this by modifiying the relationship.

    I'm looking for another answer or solution or maybe there is something that I'm missing.  It doesn't make sense that two other relationships don't block, but this one does.


Conversation locked

This conversation has been locked by the site admins. No new comments can be made.