Jeff Davis wrote:
> On Wed, 2009-02-04 at 11:11 -0500, Bruce Momjian wrote:
> > Well, with no one replying, :-(, I went ahead and added to the Read
> > Committed section of our manual to show a simple case where our read
> > committed mode produces undesirable results. I also did a little
> > clea
On Wed, 2009-02-04 at 11:11 -0500, Bruce Momjian wrote:
> Well, with no one replying, :-(, I went ahead and added to the Read
> Committed section of our manual to show a simple case where our read
> committed mode produces undesirable results. I also did a little
> cleanup at the same time.
We co
Kevin Grittner wrote:
> >>> Bruce Momjian wrote:
> > Well, with no one replying, :-(, I went ahead and added to the Read
> > Committed section of our manual to show a simple case where our read
> > committed mode produces undesirable results. I also did a little
> > cleanup at the same time.
> >
>>> Bruce Momjian wrote:
> Well, with no one replying, :-(, I went ahead and added to the Read
> Committed section of our manual to show a simple case where our read
> committed mode produces undesirable results. I also did a little
> cleanup at the same time.
>
> You can see the resulting text
Bruce Momjian wrote:
> The fundamental behavior above is that the S1 transaction is adding
> _and_ removing rows from the S2 query's result set; S2 is seeing the
> pre-query values that don't match its criteria and ignoring them and
> blocking on a later row that does match its criteria. Once S1
Bruce Momjian wrote:
> The fundamental behavior above is that the S1 transaction is adding
> _and_ removing rows from the S2 query's result set; S2 is seeing the
> pre-query values that don't match its criteria and ignoring them and
> blocking on a later row that does match its criteria. Once S1
Kevin Grittner wrote:
> >>> Tom Lane wrote:
> > Jeff Davis writes:
> >> There you see a snapshot of the table that never existed. Either
> the
> >> snapshot was taken before the UPDATE, in which case i=3 should be
> >> included, or it was taken after the UPDATE, in which case i=4 should
> be
> >
>>> Gregory Stark wrote:
> Hopefully it's not patent encumbered? Might be better not to check
> actually.
I've been in correspondence with the authors. That is the first
question I asked them. The response (from Michael Cahill):
- There are no patent applications covering our work, as far a
"Kevin Grittner" writes:
>> I think Greg has it right: without predicate locking we can't really
>> achieve the behavior you're expecting. So how would we better approach the
>> semantics you want without it?
>
> Well, this thread was talking about dealing with situations where
> queries using
>>> Jeff Davis wrote:
> On Mon, 2009-01-26 at 15:46 -0600, Kevin Grittner wrote:
>> After the COMMIT succeeds, the locks from Session1 are released.
>> Session2 acquires its update lock and reads row 2, finds that it
>> doesn't match its update criteria, downgrades the lock to shared,
>> acquire
On Mon, 2009-01-26 at 15:46 -0600, Kevin Grittner wrote:
> After the COMMIT succeeds, the locks from Session1 are released.
> Session2 acquires its update lock and reads row 2, finds that it
> doesn't match its update criteria, downgrades the lock to shared,
> acquires an update lock on row 3, fin
On Mon, 2009-01-26 at 09:26 -0800, Jeff Davis wrote:
> On Mon, 2009-01-26 at 10:48 -0600, Kevin Grittner wrote:
> > I guess the issue of whether this violation of ACID properties should
> > be considered a bug or a feature is a separate discussion, but calling
> > it a feature seems like a hard sel
>>> Gregory Stark wrote:
> This example is a case of the same issue we were discussing earlier
> involving "predicate locking". To solve it you need a way to lock
> records that your query *isn't* accessing and may not even exist yet
> to prevent them from being turned into (or inserted as) reco
Jeff Davis writes:
> It seems like it would be a challenge to know that the tuple with i=3
> would be updated to a value that matches the search condition j=10. So
> can you tell me a little more about the mechanism by which Sybase solves
> this problem?
This example is a case of the same issue
>>> Jeff Davis wrote:
> On Mon, 2009-01-26 at 14:31 -0600, Kevin Grittner wrote:
>> > Do you re-run the query to find new tuples that might now satisfy
>> > the search condition that didn't before?
>>
>> There can't be any. Blocks taken during the reading of rows so far
>> have not been releas
On Mon, 2009-01-26 at 14:31 -0600, Kevin Grittner wrote:
> > Do you re-run the query to find new tuples that might now satisfy
> > the search condition that didn't before?
>
> There can't be any. Blocks taken during the reading of rows so far
> have not been released, and would preclude the upda
>>> Jeff Davis wrote:
> The tricky part is when an UPDATE with a search condition reads,
> modifies, and writes a value that is used in a search condition for
> another UPDATE.
>
> Every DBMS will block waiting for the first UPDATE to finish. Then
> what?
Either it's totally safe to proceed, o
On Mon, 2009-01-26 at 13:50 -0600, Kevin Grittner wrote:
> A somewhat dated description for Sybase (it predates their support of
> row level locks and the related predicate locks on indexes) is here:
>
> http://manuals.sybase.com/onlinebooks/group-asarc/srv10024/sag/@Generic__BookTextView/41766;p
>>> I wrote:
> Simplified, in a READ COMMITTED transaction a SELECT takes a lock
> which conflicts with update before reading, and holds it until the
> executing statement is done with that table;
That should have said "until after the executing statement completes."
Apologies, but but I just
>>> Jeff Davis wrote:
> I don't think this is PostgreSQL-specific, I think non-MVCC database
> systems face this same choice (although the terminology would be
> different).
A somewhat dated description for Sybase (it predates their support of
row level locks and the related predicate locks on
On Mon, 2009-01-26 at 11:34 -0600, Kevin Grittner wrote:
> READ COMMITTED is not supposed to be able to view the work of a
> concurrent transactions as PARTLY applied and PARTLY committed, which
> is what's happening here. If one statement in a READ COMMITTED
> transaction sees the uncommitted vie
On 2009-01-26, at 17:34, Kevin Grittner wrote:
. It may not
surprise someone who is intimately familiar with PostgreSQL internals
for a single SELECT statement to see PART of a transactions work, but
it would surprise most users, and is certainly not compliant with the
standard.
+1000
--
Sen
>>> Jeff Davis wrote:
> In fact, it's probably most similar to UPDATE ... RETURNING, which
will
> give the same result (that breaks atomicity or isolation, depending
on
> your point of view), which is correct for READ COMMITTED isolation
> level.
READ COMMITTED is not supposed to be able to vie
On Mon, 2009-01-26 at 10:48 -0600, Kevin Grittner wrote:
> I guess the issue of whether this violation of ACID properties should
> be considered a bug or a feature is a separate discussion, but calling
> it a feature seems like a hard sell to me.
>
I think I understand the other perspective on t
>>> Tom Lane wrote:
> Jeff Davis writes:
>> There you see a snapshot of the table that never existed. Either
the
>> snapshot was taken before the UPDATE, in which case i=3 should be
>> included, or it was taken after the UPDATE, in which case i=4 should
be
>> included. So atomicity is broken for
Jeff Davis writes:
> On Sat, 2009-01-24 at 19:45 +, Greg Stark wrote:
>> There already is quite an extensive discussion of how FOR UPDATE
>> behaves including these kinds of violations.
>
> Not in the documentation, that I can see. And I think it's important
> that it be there for the reaso
Jeff Davis writes:
> There you see a snapshot of the table that never existed. Either the
> snapshot was taken before the UPDATE, in which case i=3 should be
> included, or it was taken after the UPDATE, in which case i=4 should be
> included. So atomicity is broken for WHERE.
This assertion is b
On Sat, 2009-01-24 at 19:45 +, Greg Stark wrote:
> There already is quite an extensive discussion of how FOR UPDATE
> behaves including these kinds of violations.
Not in the documentation, that I can see. And I think it's important
that it be there for the reasons I mentioned.
Can you refer
There already is quite an extensive discussion of how FOR UPDATE
behaves including these kinds of violations.
What you propose is interesting though. It would have been impossible
before subtransactions but it's doable now. Still the performance
might be unusable for complex queries. It's b
This post is a follow-up of an off-list discussion with Nathan Boley.
All references to FOR UPDATE apply to FOR SHARE as well.
create table a(i int, j int);
insert into a values(1, 10);
insert into a values(2, 10);
insert into a values(3, 10);
insert into a values(4, 20);
insert into a values(5, 2
30 matches
Mail list logo
