| From: | "Ed L(dot)" <pgsql(at)bluepolka(dot)net> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | 8.1.2 select for update issue |
| Date: | 2007-08-06 18:23:07 |
| Message-ID: | 200708061223.07391.pgsql@bluepolka.net |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jeff Davis | 2007-08-06 18:32:55 | Re: PGSQL internals |
| Previous Message | Keaton Adams | 2007-08-06 18:20:45 | Re: Template zero xid issue |