Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
diogopc
Starting Member
2 Posts |
 Posted - 2011-08-01 : 07:42:20
|
| Hello,I'm new here so please be gentle :)I have a job running that updates a couple of tables (17 steps) which supply a view. The problem is it takes about 3 or 4 minutes to complete the job and meanwhile the view can still be accessed, ocasionally generating dirty reads. I googled for a while for ways to set an isolation level on a job without any luck.Any ideas on how to change the isolation level on a job?Thanks in advance. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-01 : 10:13:07
|
| You can't set the isolation level at the job. Technically speaking it doesn't sound you are getting "dirty reads" (uncommitted transactions). It sounds more like people are accessing the table while the job is in various steps between step 1 and step 17. So they are reading committed transactions (from any individual statement) but the "logical transaction" of the all 17 steps has not completed. Would you say that is accurate? If so read on...One possibility would be to have 1 step which calls a master stored procedure which in turn calls the 17 sub-procedures all within the scope of a transaction. That would of course make the table(s) unaccessible for the 3 minutes that the transaction is open.Another possibility is could be to control row access manually with some sort of status column. For instance if the job only updates certain rows you could set them as "Locked" for the duration of the process then "unlock" them when the final step completes. Ignore those rows in your WHERE clause of the view.Another possibility could be to have two sets of tables - one set is "live" the other is not. The view always points to the live set. YOur job only updates the non-live set.Be One with the OptimizerTG |
 |
|
|
diogopc
Starting Member
2 Posts |
Posted - 2011-08-01 : 10:50:25
|
Thanks TG, you figured out pretty much what I have going on. With that beeing said I think the only valid solution is this:quote:
Originally posted by TGAnother possibility could be to have two sets of tables - one set is "live" the other is not. The view always points to the live set. YOur job only updates the non-live set.
Thank you. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-08-01 : 11:34:59
|
| You're welcome.If you go that route then here are some further suggestions:- name your tables with "_0" and "_1" suffix (or put them in seperate 0/1 databases). - create a one column one row meta table that tracks which set is live (0 or 1)- add a final job step which validates the non-live tables and then, within a transaction, updates the meta table and alters the view.- failed validations will send out a notification rather than making the live/non-live switch.Be One with the OptimizerTG |
|
|
|
|
|
|
Sql Server Agent Jobs - Isolation Level