Re: How to Force Transactions to Process Serially on A Table

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(at)matrix(dot)gatewaynet(dot)com]
> Sent: Monday, December 19, 2005 9:25 AM
> To: Lane Van Ingen
> Cc: pgsql-sql(at)postgresql(dot)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(at)crankycanuck(dot)ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes