Re: Locking entire database

From: Panagiwths Pediadiths <pped(at)ics(dot)forth(dot)gr>
To: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Locking entire database
Date: 2007-09-16 00:59:31
Message-ID: Pine.GSO.4.58.0709160357450.4109@calliope
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sat, 15 Sep 2007, Ron Johnson wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> 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
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.

> >
> > On Fri, 14 Sep 2007, Scott Marlowe wrote:
> >
> >> On 9/14/07, Panagiotis Pediaditis <pped(at)ics(dot)forth(dot)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)
>
> iD8DBQFG653ZS9HxQb37XmcRAjV+AJsFoJKc79XiGLFWSOT8Kfs0kxQItQCfWcJp
> syO91mlpB6+P+n5tWh0fGSc=
> =t8pG
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Johnson 2007-09-16 01:27:48 Re: Locking entire database
Previous Message Robert Treat 2007-09-16 00:25:25 Re: PostgreSQL Glossary?