Re: determine snapshot after obtaining locks for first statement

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: determine snapshot after obtaining locks for first statement
Date: 2009-12-16 23:26:40
Message-ID: 4B291850020000250002D650@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> With the code you give, the snapshot is acquired at the beginning
> of processing the UPDATE command, before it finds out that the
> target is t2a and acquires a lock on it.

Right. The optimization would be to wait to determine the snapshot
for the UPDATE statement until we acquire the lock. (This trick
only worked for the first statement in a database transaction.)
Perhaps that's not feasible in PostgreSQL. That's what I'm asking.

> (Besides which the lock acquired by UPDATE isn't exclusive and
> wouldn't block anyway...)

It blocks other UPDATEs.

>> The optimization Cahill describes is that for the first statement
>> in a transaction, the lock for the UPDATE is acquired before
>> obtaining the snapshot, so T2 succeeds after T1 commits.
>
> If he's talking about automatically taking an exclusive lock, I
> doubt very many of our users would find that an improvement.

I don't believe he's talking about a lock which excludes SELECTs on
the data. He's talking about reducing transaction aborts based on
the "First Committer Wins" rule. Apparently it helped his
high-contention benchmarks considerably in all three isolation
levels he was testing -- snapshot, serializable snapshot, and
serializable S2PL.

If there's something fundamentally different about how PostgreSQL
does things, such that we *can't* get the lock before the snapshot,
that's fine -- I just thought maybe this was a trick we could use.

-Kevin

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2009-12-16 23:29:48 Re: PATCH: Add hstore_to_json()
Previous Message KaiGai Kohei 2009-12-16 23:18:17 Re: Largeobject Access Controls (r2460)