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
