Thread: 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