Re: Locking entire database - Mailing list pgsql-general

On Sat, 15 Sep 2007, Ron Johnson wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 09/15/07 19:59, Panagiwths Pediadiths wrote:
> >
> > On Sat, 15 Sep 2007, Ron Johnson wrote:
> >
> > On 09/15/07 03:28, Panagiwths Pediadiths wrote:
> >>>> Thats the fun part, I actually need to allow duplicates in specific cases
> >>>> but not in this one :)
> > Same table?
> >> Yup
> >
> >>>> Shouldn't the serializable level prevent these duplicates? As I understand
> >>>> it serializable
> >>>> should give the same result as if the transactions were performed the one
> >>>> after the other.
> > (Please don't top-post.)
> >
> > Seems to me that you are confused as to the "essence" of relational
> > databases.  In other words, the best (heck, even the acceptable) way
> > to design schemas, and how to control the flow of data in order to
> > achieve your ultimate "data" goal.
> >
> >
> >> I dont see why the case i suggest is so obscene
>
> Then you have not explained it to us clearly.
>
> (English language difficulties *are* an acceptable excuse...)
>
> >> More specifically consider a table with to columns where the unique index
> >> is the two columns together
> >
> >> However at some stage of the application I want to insert into the
> >> database only if there is no element
> >> with a value at column 1 equal to that that i intend to insert.
> >
> >> Oddly, in serializable isolation mode, two transactions performing such an
> >> insertion in parallel one of the
> >> two transaction hits the phantom read case, whereas it should be protected
> >> by the isolation level.
>
> It should, *if* you do it properly.
>
> IOW, is your program structured like:
>     BEGIN
>         SELECT COUNT(*) INTO :cnt
>         FROM rdf WHERE segment_1 = :some_val;
>         IF :cnt == 1 THEN
>            do one thing
>         ELSE
>            do another
>         END IF;
>     COMMIT;
>
> or is it structured:
>     BEGIN
>         SELECT COUNT(*) INTO :cnt
>         FROM rdf WHERE segment_1 = :some_val;
>     COMMIT;
>     BEGIN
>         IF :cnt == 1 THEN
>            do one thing
>         ELSE
>            do another
>         END IF;
>     COMMIT;

Everything is done in the context of one transaction e.g.

BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    INSERT INTO table SELECT somwhere.value1, somewhere.value2 FROM
somewhere WHERE somewhere.value1 NOT IN ( SELECT table.segment1 FROM
table)

END

Many transactions doing this in parallel end up inserting the value many
times. Could i be missing something regarding how to set up the isolation
level?
Thanks!

>
> >>>> On Fri, 14 Sep 2007, Scott Marlowe wrote:
> >>>>
> >>>>> On 9/14/07, Panagiotis Pediaditis <pped@ics.forth.gr> wrote:
> >>>>>> A simpler example,
> >>>>>>     In the context of one transaction i do many queries of the form
> >>>>>>           INSERT INTO table value WHERE value NOT IN TABLE;
> >>>>>>
> >>>>>> If i have 2 processes running the same 100s of these at the same time i
> >>>>>> end up with duplicates.
> >>>>>> Even with isolation set to serializable
> >>>>>> any ideas?
> >>>>> Unique index?
>
> - --
> Ron Johnson, Jr.
> Jefferson LA  USA
>
> Give a man a fish, and he eats for a day.
> Hit him with a fish, and he goes away for good!
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFG7IaUS9HxQb37XmcRAq/bAJwNlJG2BNqfTbXPxd2sa6GsQn3nwQCfXaDo
> BMR4Lple09XnPB5w11geonY=
> =g8lJ
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>