Tech Off Thread

4 posts

SQL Transaction Blocking On Foreign Key

Back to Forum: Tech Off
  • User profile image
    MetaGunny

    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.

  • User profile image
    MetaGunny

    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
    stevo_

    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
    MetaGunny

    Stevo,

    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.

    Thanks

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.