Post reply

Sch-M Lock and Serializable transacation !

  • January 6, 2005 at 7:41 am

    #87261

    Hi !

    I've have an application which update a database with lots of Insert, Update and Delete ... In order to protect data, I'm using a serializable transaction.

    At the beginning of the update, I remove all foreign keys constraints in order to not to manage order in my querys ...

    But it seems that some Sch-m locks are set when the application remove constraints ... And thoses locks block all other applications that would also query the database ...

    As I'm in a serializable transaction, I'm wondering if there is a way to avoid Sch-M locks and remove chekck constraints just for the transaction ...

    Thanks for any help !

  • January 7, 2005 at 9:08 am

    #536468

    Schema locks make sense in that you're dropping them within the transaction. I, personally, would rather focus on fixing the logic and keep the constraints inplace.

    If you really need to ignore the constraints, why not just disable them instead of dropping them (alter table nocheck constraintname)?

  • January 7, 2005 at 9:17 am

    #536470

    I've already tried but it seems that it also lock with a Sch-M ...

    And yes ... I really need to ignore ...

    But could you tell me what the main difference between a nocheck and check for a foreign keys constraints ?

     

  • January 7, 2005 at 9:28 am

    #536474

    nocheck disables the FK constraint...

    check enables the FK constraint

  • January 7, 2005 at 9:31 am

    #536477

    Ok ...

    I will try again to see if this is better than drop ...

     

    But what is the interest of a FK without check ?

     

    For info : the Alter Table NoCheck lock with a Sch-M ... so I come back to my problem ...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply