Search Postgresql Archives

Re: Locking entire database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

>>>> On Fri, 14 Sep 2007, Scott Marlowe wrote:
>>>>
>>>>> On 9/14/07, Panagiotis Pediaditis <pped@xxxxxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux