Re: serialization errors

On Tue, 28 Jan 2003, Ryan VanderBijl wrote:

> Let us suppose I have a table like this:
>     create sequence seq_foo;
>     create table foo (
>         bar  int primary key default nextval('seq_foo'),
>         name varchar(32),
>         unique(name)
>     );
>
> I need to have multiple clients update this table concurrently.
>
> Below are two "scripts", one for client A, one for client B:
>
> Time:  Client A
> 1.     BEGIN;
> 2.     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 3.     SELECT * FROM foo;  -- each get their own db 'copy'/version
> 4.     INSERT INTO foo(name) VALUES('test');
> 5.     [no-op]
> 6.     COMMIT;
> 7.     [no-op]
>
> Time:  Client B
> 1.     BEGIN;
> 2.     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> 3.     SELECT * FROM foo;  -- each get their own db 'copy'/version
> 4.     [no-op]
> 5.     INSERT INTO foo(name) VALUES('test');
> 6.     [paused waitting for insert to complete]
> 7.     ERROR:  Cannot insert a duplicate key into unique index foo_name_key
>
> The documentation about concurrency control / serializable isolation level
> indicates that if there is a concurrent conflicting update, then I would
> receive the following error:
>     ERROR:  Can't serialize access due to concurrent update
> ( The documentation is found here:
>     http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/mvcc.html )
>
> However, as the scripts above demonstrate, one can receive other errors.
>
> So, assuming I mis-understand the documentation, and that mutliple errors can
> legally occur, how can I detect if an error received is because of a concurrent
> update?

Note that the above isn't an update.  It's not a case of finding a target
row that's been concurrently modified and committed (as described by the
paragraph in that document starting with "UPDATE, DELETE, and SELECT FOR
UPDATE"), it's a case of violating a unique constraint.  If the constraint
wasn't there, there wouldn't be an error in doing the above.