Counterintuitive locking behavior

Hi everyone;

I recently discovered that subselects in update statements don't assume
that the select is for update of the updating table.

For example, if I do this:

CREATE TABLE foo (
test int primary key,
);

INSERT INTO foo VALUES (1);

then in one session:

BEGIN;
UPDATE foo SET test = 2 WHERE test in (select test from foo where test = 1);

and then in the other session

BEGIN;
UPDATE foo SET test = 3 WHERE test in (select test from foo where test = 1);

When I commit both transactions, the second one chronologically always
takes precedence. In other words, the locks takes effect after the
subselect but before the rows are updated. This strikes me as quite error
prone and quite a bit more error prone than a rule which says that unless
stated otherwise subselects of the updated table are to be selected for
update.

This may strike some as a "do what I mean" kind of feature, but the way I
am looking at it is that a SQL statement is usually written as a
declarative block, and an assumption that the SQL statement is to be
evaluated atomically is a good one for predicability of software (in other
words, locks apply to the whole statement).

Is there a reason why we don't do locking this way? (i.e. where on UPDATE
foo, all rows selected from foo during the update are locked unless the
subselect specifically states otherwise.)

Best Wishes,
Chris Travers