Re: How to Force Transactions to Process Serially on A Table - Mailing list pgsql-sql

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