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.