February 26, 2009 at 7:37 pm
From the activity monitor sometimes i see processid BlkBy another Processid when they try to access same database. is there a way we can prioritize the process. For Example
I have a query that inserts 50 million records into a database A in multiple steps, afte reach step it has to record(mark) the inserts into another database B, by the time it records if another user try to access database A then does my insert query has to wait for the user to complete the process so that i can continue my further step to insert. If so is there a way i can manually keep the other process waiting and finish the inserts.
February 27, 2009 at 2:34 am
I'm not sure if I'm following you to the atomic details of the issue but I would say you may want to work in two fronts.
1- Setting a explicit transaction
2- Code offending user select statements with NOLOCK hint after setting transaction isolation level to serializable.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 27, 2009 at 3:43 am
There is no way to set priority for a process. But you can prevent other processes to take control on the table using trnasaction and lock hints. In your case, begin a transaction at the start of insert process, lock the table using TABLOCK hint and commit the transaction at the end of insert proccess. Even long transactions are not recommended as they reduces concurrency.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply