|
|
Posted by dhek on 11/04/07 09:17
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns99DDED79C3A76Yazorman@127.0.0.1...
> dhek (dhek@dhek.dk) writes:
>> Well, my issue is that I'm reading data from tables A, B, C and D and
>> updates table E.
>>
>> What I need to prevent is 2 things:
>>
>> 1) New rows must not be added to either table A and B while my
>> transaction
>> is executing
>> 2) Existing rows must not be modified
>
> I'm not sure that I get this. Assuming you assemble data into a temp
> table or a table variable, and you batch goes:
>
> INSERT #tmp(...)
> SELECT ....
> FROM A, B
> ...
>
> INSERT #tmp(...)
> SELECT ...
> FROM C, D
>
> UPDATE E
> SET ...
> FROM E
> JOIN #tmp...
As mentioned I perform a SELECT statement followed by an UPDATE-statement in
my transaction.
When I get new Orders and related information from various tables I need to
update a synchronization-table indicating the time of last sync.
My problem is that with this setup I need to prevent others from adding new
orders to the table while my transaction is executing to prevent "lost
records".
> Why would it be an issue if some adds or modifies rows into A or B
> once you have run that SELECT statement? I can possibly understand
> that you don't want permit rows to be added or modified in C or D while
> you are reading A and B. But once you have read A or B, it cannot matter
> if modifications happens while your transaction is running, or if they
> are held up until your transaction completes.
With the setup u proposed in your example I agree - it would be foolish not
to allow others to read/write data to table A,B when done with those
table:-)
If my sync-indicator had only been located in the records that are selected
I would not have a problem at all - only need to set the transaction level
to READ COMMITED or SYNCHRONIZED. I do not have the option of setting it to
snapshot isolation (not supported by my application.)
>> My query is executed on a SQL server 2005. I do not have the option to
>> change the configuration of it.
>
> Well, if you want to read that is consistent at a certain moment in
> time, snapshot isolation is your only foolproof option. It also has the
> advantage of not blocking updates.
Do u mean is allows for records to be modified or is it table u refer to?
Navigation:
[Reply to this message]
|