Sch-M Lock and Serializable transacation !
-
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 !
-
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)?
-
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 ?
-
nocheck disables the FK constraint...
check enables the FK constraint
-
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