From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: How to Force Transactions to Process Serially on A Table
Date: Mon, 19 Dec 2005 09:28:10 -0500
Message-ID: <20051219142810.GB6777@phlogiston.dyndns.org> (raw)
In-Reply-To: <EKEMKEFLOMKDDLIALABIEEGMCGAA.lvaningen@esncc.com>
References: <Pine.LNX.4.44.0512191615510.7853-100000@matrix.gatewaynet.com>
<EKEMKEFLOMKDDLIALABIEEGMCGAA.lvaningen@esncc.com>
On Mon, Dec 19, 2005 at 08:46:39AM -0500, Lane Van Ingen wrote:
> 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?
You'll get an error. You should read this section of the docs:
http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-SERIALIZABLE> (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.
No. The effect is to _emulate_ the case where the set transaction is
processed serially. Importantly, on a high-concurrency database, you
tend to get serialization failures. Moreover, it is not true
mathematical serialization. See section 12.2.2.1 for details in case
you need such a feature, in which case you're back to explicit
locking.
A
>
> -----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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
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: ajs@crankycanuck.ca
Subject: Re: How to Force Transactions to Process Serially on A Table
In-Reply-To: <20051219142810.GB6777@phlogiston.dyndns.org>
* 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