From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Lincoln Yeoh <lylyeoh(at)mecomb(dot)com> |
Cc: | davidb(at)vectormath(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Revisited: Transactions, insert unique. |
Date: | 2000-04-25 02:17:51 |
Message-ID: | 3905004F.CF09708D@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael S. Kelly | 2000-04-25 04:16:00 | Can't find destroydb command in 7.0 |
Previous Message | Stephan Richter | 2000-04-25 02:05:20 | Re: Is 7.0 ready? |