Re: Locking entire database

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