> On Thu, Jul 16, 2009 at 12:34 PM, Steve Caligo<steve.cal...@ctie.etat.lu> > wrote:
>> 2) FIRST TRY, USING "UPDATE WHERE ID = ( SELECT )" >> The goal is to have two clients set their unique ID to a >> single/different >> row from the table. First, using "limit" in a slightly different way: > This is pretty clearly NOT the situation described in the > documentation. There is no FOR UPDATE or FOR SHARE anywhere in this > query. You could argue that we should treat an UPDATE statement as > applying an implicit FOR UPDATE to any subqueries found therein, but > that has nothing to do with whether the current behavior matches the > documentation; it's a discussion of whether the current behavior is > good or bad. Should there really be a difference between doing an UPDATE or just requesting rows to be locked for a later modification (update or deletion, whatever) through SELECT ... FOR UPDATE? I'm aware that there's no FOR UPDATE in that query, as its direct use is discouraged in the documentation (SELECT ... LIMIT ... FOR UPDATE) and reported as "odd behavior" previously as well: http://archives.postgresql.org/pgsql-bugs/2004-10/msg00138.php Yes, I'm considering this behavior bad, as I'd expect the UPDATE statement to lock either the whole table (if there's no other way) or only the rows returned by the sub-SELECT, but as far as I can see, such row-level locking can't be achieved in PostgreSQL but through the use of serialization. Allowing FOR UPDATE in sub-queries has been rejected in this same thread, you've also mentioned it your previous reply. http://archives.postgresql.org/pgsql-bugs/2004-10/msg00150.php > This is pretty weird behavior, and I am among those who think it > sucks. But it is documented. > > http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY This behavior may be consistent with the transaction isolation levels PostgreSQL provides (read committed/serializable, while one would need true repeatable read in this case), it is a huge drawback when working with the database, as one has to think about potential collateral damage with every single SELECT...FOR UPDATE / UPDATE query one writes, especially the complex ones. > An interesting fact is that if you stick another "for update" into the > subquery here, the command will fail utterly, with the following error > message: > > ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate functions See my second link above, it works as designed. The document states that the FOR UPDATE is applied to the view or sub-query, which I assume means: "applied to a SELECT containing a sub-query or view". As such, I'm expecting a single statement to be an atomic operation, i.e. always works on the same data snapshot, independent of the transaction isolation level. This atomicity has to be enforced through a full table lock (which is often the easiest to implement, but also the most expensive efficiency-wise) or serialization. Side note: a cross-database test showed that Oracle, DB2 nor MySQL (with InnoDB storage engine) run the query as true repeatable read, whether one makes use of the "skip locked rows" (when available) or not. One always ends up with two different rows being updated. Best regards, Steve Caligo -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
