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

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

> 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?

Look at
http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html

> (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.
>

Then transaction isolation SERIALIZABLE is not for this task.

What you would do is use the
SELECT ... FOR UPDATE
construct.

Normally you would not care about locking the whole table
but only row of interest.

> -----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
>

--
-Achilleus