Re: Do I need serializable for this query?

On Tue, Apr 10, 2007 at 10:52:11PM +0200, Peter Eisentraut wrote:
> William Garrison wrote:
> > I have a table that keeps running totals. It is possible that this
> > would get called twice simultaneously for the same UserID. Do I need
> > to put this in a serializable transaction?
>
> Transaction isolation is only a question of interest if you have more
> than one statement in a transaction.

Not true; the isolation level is also relevant if you're doing
concurrent updates or deletes (although, as the documentation
mentions, PostgreSQL's serializable isolation level doesn't guarantee
true serializability).

create table test (id integer primary key, t text);
insert into test (id, t) values (1, 'a');

T1: begin isolation level serializable;
T2: begin isolation level serializable;
T1: update test set t = 'b' where id = 1;
T2: update test set t = 'c' where id = 1; -- blocks
T1: commit;
T2: ERROR: could not serialize access due to concurrent update

--
Michael Fuhr