Re: Counterintuitive locking behavior - Mailing list pgsql-general
From | Boszormenyi Zoltan |
---|---|
Subject | Re: Counterintuitive locking behavior |
Date | |
Msg-id | 50E923A3.1080807@cybertec.at Whole thread Raw |
In response to | Counterintuitive locking behavior (Chris Travers <chris.travers@gmail.com>) |
List | pgsql-general |
2013-01-06 03:18 keltezéssel, Chris Travers írta: > 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.) What you are seeing is the MVCC behaviour of PostgreSQL in READ COMMITTED mode. You can use REPEATABLE READ mode in 9.2.x or SERIALIZABLE mode in earlier generations. Please read the "Concurrency control" section of the documentation. http://www.postgresql.org/docs/9.2/interactive/mvcc.html With this mode: session 1: zozo=> create table foo (test int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE zozo=> insert into foo values (1); INSERT 0 1 zozo=> begin isolation level repeatable read; BEGIN zozo=> update foo set test = 2 where test = (select test from foo where test = 1); UPDATE 1 session 2: zozo=> begin isolation level repeatable read; BEGIN zozo=> update foo set test = 2 where test = (select test from foo where test = 1); (session 2 is waiting for the lock on the row at this point) session 1: zozo=> commit; COMMIT session 2 threw an error after session 1 committed: ERROR: could not serialize access due to concurrent update But updating a row blindly is quite silly even in this small example, you can use SELECT ... FOR UPDATE for explicit locking. session 1: zozo=> begin; BEGIN zozo=> select * from foo where test = 1 for update; test ------ 1 (1 row) session 2: zozo=> begin; BEGIN zozo=> select * from foo where test = 1 for update; session 2 waits... session 1: zozo=> update foo set test = 2 where test = 1; UPDATE 1 zozo=> commit; COMMIT session 2 now gives the result: zozo=> select * from foo where test = 1 for update; test ------ (0 rows) and your application can warn the user that the row is not there anymore, so no point in the subsequent UPDATE. You should do a new query to find the row you wanted. Best regards, Zoltán Böszörményi -- ---------------------------------- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/
pgsql-general by date: