On Mon, 17 Feb 2003 09:51:54 +0800, "Christopher Kings-Lynne"
<[EMAIL PROTECTED]> wrote:
>Strategy three:
>
>begin;
>lock table in exclusive mode;
>update row;
>if (no rows affected) insert row;
>commit;
>
>Problem - Works, but this table needs high concurrency.

Chris,

distributing congestion might improve that.  Instead of locking the
whole table just lock one row in a dummy table:

        CREATE TABLE dummylock(id INT PRIMARY KEY);
        INSERT INTO dummylock VALUES (0);
        INSERT INTO dummylock VALUES (1);
        ...
        INSERT INTO dummylock VALUES (999);

Create an immutable function lockhash(<type of PK>) returning a value
between 0 and 999.

        BEGIN;
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
        UPDATE t SET c = 'newval' WHERE pk = 'pk';
        if (no rows affected) THEN
            SELECT * FROM dummylock
             WHERE id = lockhash('pk') FOR UPDATE;
            -- try again
            UPDATE t SET c = 'newval' WHERE pk = 'pk';
            if (no rows affected) THEN
                INSERT INTO t ...;
            END IF;
        END IF;
        COMMIT;

This is just an idea.  Completely untested ...

Servus
 Manfred

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Reply via email to