From: "Lane Van Ingen" <lvaningen@esncc.com>
To: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: How to Force Transactions to Process Serially on A Table
Date: Mon, 19 Dec 2005 08:46:39 -0500
Message-ID: <EKEMKEFLOMKDDLIALABIEEGMCGAA.lvaningen@esncc.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-7"
Content-Transfer-Encoding: 8bit
In-Reply-To: <Pine.LNX.4.44.0512191615510.7853-100000@matrix.gatewaynet.com>
Importance: Normal

Thanks, that helped.

Please answer 2 other related questions, if you would:
(1) What must I do to 'Be prepared for serialization failures'  (how to
detect, how to handle)?
     Do you have a sample?
(2) Also, I am assuming that the effect of all of this is to just force
transactions to wait in line
to be processed serially, and that it only lasts as long as the pl/pgsql
transaction block or
the next COMMIT.

-----Original Message-----
From: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com]
Sent: Monday, December 19, 2005 9:25 AM
To: Lane Van Ingen
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] How to Force Transactions to Process Serially on A Table

O Lane Van Ingen έγραψε στις Dec 19, 2005 :

> I am using PL/SQL functions on Windows 2003, version 8.0.1.
>
> I have not used explicit PostgreSQL LOCKing before, but I need some advice
> on how properly to use some explicit locking. I think that duplicate key
> violations I am now getting are the result.
>
> I want to force transactions being used to update a table to be processed
on
> a first-come, first-served basis. I want my Pl/sql function to execute to
> completion on each transaction before another starts.
>
> Need some advice on how to do this. From what I can read in the docs, it
> looks like I need to solve the problem by using the following, but doing
so
> gives me an SPI_execution error:
>   BEGIN;
>   LOCK <table> IN SHARE ROW  EXCLUSIVE MODE;
>     lock adns_report_hour_history in share row exclusive mode;
>   INSERT INTO <table> VALUES ...  - or - UPDATE <table> SET ....
>   COMMIT;
> Will this make the next transaction wait until the previous transaction
has
> completed? Do I need to set any config parameters?
>
> If you can include an actual code snippet in the response, it would help
...

what you want is to set the xaction isolation level.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
......
COMMIT;

Be prepared for serialization failures though.

>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
-Achilleus