My apologies for what may be an obvious answer to others but the documentation (for v8.1) leaves me concerned (enough not to trust "expected behaviour" of some commands). Essentially I was thinking of using PERFORM (SELECT) ... FOR UPDATE within PL/pgSQL - all indications are that this should be sufficient to prevent other concurrent transactions from modifying selected rows until the current transaction completes: "This prevents them from being modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE of these rows will be blocked until the current transaction ends." See: http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE Okay so far - until you read this: "ROW SHARE Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes. The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a lock of this mode on the target table(s) [...] "ROW EXCLUSIVE Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table [...]" See: http://www.postgresql.org/docs/8.1/interactive/explicit-locking.html#LOCKING-TABLES The thing that worries me is that according to the documentation, "ROW SHARE" and "ROW EXCLUSIVE" lock modes don't conflict which means (logical progression, not considering actual behaviour) an UPDATE, DELETE or INSERT could change data in an underlying table, despite another transaction already having executed "SELECT ... FOR UPDATE". Is the explicit locking documentation page just missing something or have I completely misunderstood either the behaviour of "SELECT ... FOR UPDATE" or the interaction of the various locking modes? The workaround for the moment is just to explicitly LOCK the table but I'd rather remove explicit locking statements if possible. Thanks..
