Обсуждение: 8.1.2 select for update issue

We're seeing some unexpected behavior in one particular 64-bit
Pgsql 8.1.2 running on HP-UX 11.23 and Itanium 2, built
with --enable-thread-safety.  We think we are seeing concurrent
select-for-updates of the same rows by multiple concurrent
backends, contrary to our understanding of select-for-update
semantics.  The rows are selected by each client process as
follows:

SELECT *
from foo
where eventprocessed = 'f'
  and inprogress = 'f'
  and eventstructure is not NULL
order by key asc
for update
limit 25;

Once the rows are selected, they are then updated within the same
transaction, for example, as follows:

update foo set inprogress = 't' where key in (10169339);

We think the row should be locked, unselectable for update, and
that the update above should remove them from selection in any
subsequent select-for-updates like the one above.  However, we
see one backend selecting and locking a set of rows, and while
it presumably has them locked and is chugging through doing
updates like the one above, we see another backend
select-for-update grabbing some of the same rows and performing
updates.

We're unable to reproduce this scenario on demand, but it does
consistently happen about 1/3 of the time on this busy system
whenever we turn on the second process.  Any suggestions on
where to hunt?

TIA,
Ed

"Ed L." <pgsql@bluepolka.net> writes:
> We're seeing some unexpected behavior in one particular 64-bit
> Pgsql 8.1.2 running on HP-UX 11.23 and Itanium 2, built
> with --enable-thread-safety.  We think we are seeing concurrent
> select-for-updates of the same rows by multiple concurrent
> backends, contrary to our understanding of select-for-update
> semantics.

You really ought to be using something newer than 8.1.2.  However
I don't see anything directly related in the release notes.

You do have a transaction block established around this whole process?
Row locks only last as long as the current transaction ...

            regards, tom lane

On Monday 06 August 2007 1:22 pm, you wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > We're seeing some unexpected behavior in one particular
> > 64-bit Pgsql 8.1.2 running on HP-UX 11.23 and Itanium 2,
> > built with --enable-thread-safety.  We think we are seeing
> > concurrent select-for-updates of the same rows by multiple
> > concurrent backends, contrary to our understanding of
> > select-for-update semantics.
>
> You really ought to be using something newer than 8.1.2.

Perhaps.  But we have yet to find a way to make major version
upgrades of 100+ GB, 100+ tps databases sufficiently inexpensive
and painless in terms of SAN space, performance costs, and
customer downtime on heavily loaded systems.  So we put them off
until there is a clear, directly compelling reason to upgrade.

> You do have a transaction block established around this whole
> process? Row locks only last as long as the current
> transaction ...

Of course.  This is grasping at straws, but I was wondering if
perhaps anyone saw anything in this behavior that might suggest
a threadsafe-related anomaly?

TIA.
Ed

"Ed L." <pgsql@bluepolka.net> writes:
> On Monday 06 August 2007 1:22 pm, you wrote:
>> You really ought to be using something newer than 8.1.2.

> Perhaps.  But we have yet to find a way to make major version
> upgrades of 100+ GB,

I did not suggest a major version upgrade.

            regards, tom lane

On Monday 06 August 2007 2:11 pm, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > On Monday 06 August 2007 1:22 pm, you wrote:
> >> You really ought to be using something newer than 8.1.2.
> >
> > Perhaps.  But we have yet to find a way to make major
> > version upgrades of 100+ GB,
>
> I did not suggest a major version upgrade.

My mistaken assumption.  We are considering an upgrade to 8.1.9.
I see the number of bugfixes between 8.1.2 and 8.1.9 is lengthy.

Ed