Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to make an 'UNLOCK'?

how to make an 'UNLOCK'?

From
"Grand Titus"
Date:
 
In my DB there are 3 tables A, B and C
I try to make a function "INTEGER Insertion (TEXT,TEXT)" which insert the
parameters into the 3 tables in this way:
 1- Verify if $1 exits in A
   1.1- If yes then read the identificator (id_1) associate to $1
   1.2- If not then insert $1 into A and read its new indentificator (id_1)
 2- Verify if $2 exits in B
   2.1- If yes then read the identificator (id_2) associate to $2
   2.2- If not then insert $2 into B and read its new indentificator (id_2)
3- Verify if (id_1,id_2) exists in C
   3.1 If yes then increase the field 'occurence'
   3.2 If not then insert (id_1,id_2,1) into C (1 is for occurence=1)
My problem is the following:
 Several processus can use this function simultaneously. So for all these
steps (1, 2 and 3) I need to lock respectively the table A, B and C. In
order to improve the efficiency of this DB, I would like to unlock the table
A, for ex, when I go from the step 1 to the step 2 (in order to enable
another processus to work on the table A).
 And I don't know how to unlock a table, I can't find the command 'UNLOCK'.
Do you know another command or solution to do this, for ex by usuing a C
function (I know that those kind of function exit but I nerver programmed
one of them)?

Thanks for your help

Re: how to make an 'UNLOCK'?

From
Tom Lane
Date:
"Grand Titus" <grand.titus@free.fr> writes:
>  And I don't know how to unlock a table, I can't find the command 'UNLOCK'.

There is no UNLOCK short of committing your transaction.  If you had
one, your code would not work anyway (because you'd be unlocking the
table before the next guy could see your uncommitted updates).

I'd suggest using an optimistic-locking approach instead: don't lock
at all, just rely on unique indexes to prevent duplicate insertions.
Once in a while you will get a collision, and then you'll have to
roll back your transaction and try again --- but if that only seldom
happens, it's a lot faster than locking every time.

            regards, tom lane

Re: how to make an 'UNLOCK'?

From
"Grand Titus"
Date:
> I'd suggest using an optimistic-locking approach instead: don't lock
> at all, just rely on unique indexes to prevent duplicate insertions.
> Once in a while you will get a collision, and then you'll have to
> roll back your transaction and try again --- but if that only seldom
> happens, it's a lot faster than locking every time.

My table A (which can be big) is that:
    A(TEXT AbsoluteURL, SERIAL Id) with Id as PRIMARY KEY
AbsoluteURL will contain strings like
"http://archives.postgresql.org/pgsql-general/"
If I declare AbsoluteURL as UNIQUE, do you think it will be good for the
efficient of the DB? Because when I insert a value in the table it could
take a long time to verify if this entry already exists or not. Am I wrong?
If I lock the table A, no other processus will acces to the table but
Postgresql won't have to verify if the entry already exists.
What is the best solution in your opinion?

Thanks for your help

> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

Re: how to make an 'UNLOCK'?

From
Tom Lane
Date:
"Grand Titus" <grand.titus@free.fr> writes:
> If I declare AbsoluteURL as UNIQUE, do you think it will be good for the
> efficient of the DB?

It sure beats doing manual uniqueness checks, which is what it sounds
like you are doing now.  And you need an index on AbsoluteURL anyway
if you are doing searches on it ...

            regards, tom lane

Re: how to make an 'UNLOCK'?

From
Taz Master
Date:
>> I'd suggest using an optimistic-locking approach instead: don't lock
>> at all, just rely on unique indexes to prevent duplicate insertions.
>> Once in a while you will get a collision, and then you'll have to
>> roll back your transaction and try again --- but if that only seldom
>> happens, it's a lot faster than locking every time.
>
>My table A (which can be big) is that:
>    A(TEXT AbsoluteURL, SERIAL Id) with Id as PRIMARY KEY
>AbsoluteURL will contain strings like
>"http://archives.postgresql.org/pgsql-general/";
>If I declare AbsoluteURL as UNIQUE, do you think it will be good for
the
>efficient of the DB? Because when I insert a value in the table it
could
>take a long time to verify if this entry already exists or not. Am I
wrong?
>If I lock the table A, no other processus will acces to the table but
>Postgresql won't have to verify if the entry already exists.
>What is the best solution in your opinion?

Well, it is a key, so it's already indexed, to validate if it exists or
not it would just do a key lookup which should be very fast.  I don't
have the code to postgreSQL to say for sure, but I've never noticed a
speed hit on any databases I've used with unique (M$ SQL, Access,
MySQL) though I've not done large databases on postgres yet.

My opionion is you would not suffer a significant speed loss.

Regards,

Jim Langston

=====
Taz Master
mailto:tazmaster@rocketmail.com


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com