Re: Revisited: Transactions, insert unique.

Lincoln Yeoh wrote:
> 
> Hi David,
> 
> That can work if there's only one session using the database. But it's not
> 100% if there are multiple users. There's a small chance that a row may not
> exist during the select, but exist by the time of the insert. If I'm wrong
> please correct me - then I'll go optimize some code :).
> 
> By having the unorthodox locking mechanism suggested I can ensure at the
> application level that no one else is going to insert stuff before my
> select, update/insert, without having to lock the whole table.
> 
> So it will be
> LOCK arbitrary
> select
> if exist update
> else insert
> UNLOCK arbitrary
> 
> Which would be faster- doing the lock arbitrary method, or doing an insert
> with unique indexes and recovering if necessary (assuming postgresql does
> what other databases do)? I suspect unique indexes could slow inserts and
> updates down significantly.
> 
> If we don't want to do all that, how about we have a select for insert (and
> update), which locks things? But I figured that it would be problematic to
> implement in a number of scenarios tho.

PostgreSQL implements SELECT...FOR UPDATE to allow for the
sequence you'be described:

Session 1:

BEGIN;
SELECT webuser FROM webusers WHERE webuser = 'webuser1';

Session 2:

BEGIN;
UPDATE webusers SET webuser = 'webuser2' WHERE webuser =
'webuser1';*

*At this point Session 2 blocks, waiting for Session 1 to
complete. This prevents the race condition you've described and
only locks those rows which were selected by Session 1's SELECT.
With MVCC, table-level locking is largely a thing of the past.
The MVCC documentation describes this in detail:

http://www.postgresql.org/docs/postgres/mvcc4496.htm

Hope that helps, 

Mike Mascari