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> (raw)
In-Reply-To: <Pine.LNX.4.44.0512191615510.7853-100000@matrix.gatewaynet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lvaningen@esncc.com, achill@matrix.gatewaynet.com
Subject: Re: How to Force Transactions to Process Serially on A Table
In-Reply-To: <EKEMKEFLOMKDDLIALABIEEGMCGAA.lvaningen@esncc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox