Question: update and transaction isolation 
Author Message
 Question: update and transaction isolation

Quote:
Tom Lane writes:
> The reason it works in read-committed mode is that the second guy to
> arrive at the row will observe that the row has an update in progress;
> will block waiting for the previous updater to commit or abort; and if
> commit, will use the updated version of the row as the starting point
> for his update.  (This is what the EvalPlanQual ugliness in the executor
> is all about.)

Isn't that a violation of the principle that transactions in read
committed mode will look at the data that was committed *before* the
statement had begun?

--

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Mon, 20 Sep 2004 06:07:50 GMT
 Question: update and transaction isolation

Quote:

> Tom Lane writes:
>> The reason it works in read-committed mode is that the second guy to
>> arrive at the row will observe that the row has an update in progress;
>> will block waiting for the previous updater to commit or abort; and if
>> commit, will use the updated version of the row as the starting point
>> for his update.  (This is what the EvalPlanQual ugliness in the executor
>> is all about.)
> Isn't that a violation of the principle that transactions in read
> committed mode will look at the data that was committed *before* the
> statement had begun?

Hey, I didn't design it.  Complain to Vadim ...

But actually, SELECT FOR UPDATE also violates the principle you allege,
and must do so if it's to be useful at all.  The results you get are
whatever's in the row after it's been locked, not what was in the row
at the instant of statement start.  UPDATE is essentially behaving in
the same way.

To my mind, full SERIALIZABLE mode is the only approach that can be
explained in terms of simple notions like "you see only the data that
existed at time T".  Read-committed mode is conceptually much dirtier,
even though it's often simpler to use in practice.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Mon, 20 Sep 2004 06:16:24 GMT
 Question: update and transaction isolation

Quote:


> > Tom Lane writes:
> To my mind, full SERIALIZABLE mode is the only approach that can be
> explained in terms of simple notions like "you see only the data that
> existed at time T".

There's another way. If the current value is different from
that at time T, we may be able to reset the time when the
statement begun, which is equivalent to replaceing the snapshot
(this isn't allowed in serializable mode). Of cource it would
be very difficult to implement(at least effectively).

As I've already mentioned many times SELECT and SELECT ..
FOR UPDATE are alike in appearance but quite different in
nature. For example, the meaning of the snapshot isn't the
same as you've pointed out already in this thread.
It's meaingless for SELECT and UPDATE(SELECT .. FOR UPDATE)
to have a common snapshot.

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------



Mon, 20 Sep 2004 09:09:07 GMT
 Question: update and transaction isolation

Quote:
> > For each page hit, I do this:

> > update pagecounts set counter = counter + 1 where pagename =
> 'testpag.php'

> > Do I have to set a particular isolation level? Or does this not work in
> > general?

> In read committed level, if the second update launches before the first
> update is finished (commits), then both of these updates will operate on
> the old counter value.  That is, you miss one page hit.

can you break it into this:

begin;
select counter from pagecounts where pagename='testpag.php' for update;
update pagecounts set counter=counter+1 where pagename='testpag.php';
commit;

Chris

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 20 Sep 2004 09:40:09 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Question about SELECT FOR UPDATE in transaction, isolation level

2. Question about SELECT FOR UPDATE in transaction, isolation level

3. Which transaction isolation level can prevent lost updates?

4. Connection transaction isolation question

5. Question about default transaction isolation level

6. Question on Transaction Isolation levels.

7. HELP: Transaction Isolation Levels

8. SQL Server Transaction Isolation Level

9. HELP: Transaction Isolation Levels

10. setting transaction isolation level on connection

11. setting transaction isolation level programatically

12. transaction isolation?


 
Powered by phpBB® Forum Software