Thread: LOCK TABLE HELP

----
Email.it, the professional e-mail, gratis per te: clicca qui

Sponsor:
Una BMW Z4 Roadster a 10€? Prova gratis su Bidplaza.it!
Clicca qui

luca.ciciriello@email.it wrote:
> Hi All.
>
> I'm using for the first time the postgres lock utilities, but brobably
> I'm doing something of not legal.My action are:void *Execute(void
> *pParam){    ........................................    ........................................  
>      string tableLock = "BEGIN
> WORK;";             tableLock.append("
> LOCK TABLE ");           

Could you repost your message without all the   stuff? There's
probably a "plain text" option in your email package you need to turn on.


--
   Richard Huxton
   Archonet Ltd

luca.ciciriello@email.it wrote:
>
> Well, all this doesn't work (the connection is the always the same in
> all methods and functions).
I do not understand this statement.


There are some other things you could mention that might help:

Why do you need these table level locks - what are you trying to achieve
that requires them instead of row level locking?

Have you considered using SELECT ... FOR UPDATE / SELECT ... FOR SHARE
(if appropriate) ?

Can you avoid the explicit locking by using a SERIALIZABLE transaction
instead?

What about the locking is not working correctly?

What is currently happening? What do you expect to happen instead?

What happens if you start up two psql sessions and write the same code
in them by hand? Does the locking interaction work correctly then?

Have you read this documentation:
http://www.postgresql.org/docs/8.3/static/explicit-locking.html
?

Are you using only one connection to the server or more than one? If
more than one, how are you ensuring the right thread uses the right
connection?
> Have I to L ock the table, perform some operation on this table, and
> unlock the table all in the same function scope?
Locks are released at transaction COMMIT / ROLLBACK, and that's (as far
as I know) the only way to release a lock. You should acquire the lock,
do your work, and commit the transaction, eg:

BEGIN;
LOCK TABLE sometable IN ACCESS EXCLUSIVE MODE;
-- Do your work
COMMIT;


I don't know what function scope could possibly have to do with this or
with your threading. What matters is the SQL you're sending to the
server. Have you tried logging the SQL statements you're sending to the
server and making sure they work as expected in psql?

--
Craig Ringer

----
Email.it, the professional e-mail, gratis per te: clicca qui

Sponsor:
Scopri le tue passioni con Leonardo.it!
Clicca qui

luca.ciciriello@email.it wrote:
> Sorry for the bad text format. Below the right (I hope...)  text:
> Hi All.I'm using for the first time the postgres lock utilities, but

Nope, sorry. Still full of HTML stuff. Hang on, I'll see if I can fix it.

Luca's message below:
=====================


I'm using for the first time the postgres lock utilities, but brobably
I'm doing something of not legal.

My action are:

void *Execute(void *pParam)
{
         ........................................
         ........................................

  & nbsp;      string tableLock = "BEGIN WORK;";
         tableLock.append(" LOCK TABLE ");
         tableLock.append(actorTable);
         tableLock.append(" IN ACCESS EXCLUSIVE MODE;");
         res = PQexec(connection, tableLock.c_str());

        ........................................
        ........................................


pObj->ReturnNotification(static_cast<string>(notify->relname),connection);

          .........................................
          .........................................

           CleanUpBeforeStop(void *pParam);
}

void CleanUpBeforeStop(void *pParam)
{
           .........................................
           .........................................
           res = PQexec(pPti->conn, "COMMIT WORK;");
}

Well, the function Execute is the detached procedure of a thread. In
here I execute the LOCK command on the table "actorTable". Then, in the
method ReturnNotification I read the locked table. At last, calling the
function CleanUpBeforeStop, I execute the COMMIT command.

Well, all this doesn't work (the connection is th e always the same in
all methods and functions). Have I to Lock the table, perform some
operation on this table, and unlock the table all in the same function
scope?

Any Idea?

Thanks in advance.

Luca

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:
> luca.ciciriello@email.it wrote:
>> Sorry for the bad text format. Below the right (I hope...)  text:
>> Hi All.I'm using for the first time the postgres lock utilities, but
>
> Nope, sorry. Still full of HTML stuff. Hang on, I'll see if I can fix it.
>
> Luca's message below:
> =====================
>
>
> I'm using for the first time the postgres lock utilities, but brobably
> I'm doing something of not legal.

Like Craig asked - what doesn't work?

--
   Richard Huxton
   Archonet Ltd

> luca.ciciriello@email.it wrote:

> My action are:
>
> void *Execute(void *pParam)
> {
>         ........................................
>         ........................................
>
>  & nbsp;      string tableLock = "BEGIN WORK;";
>         tableLock.append(" LOCK TABLE ");
>         tableLock.append(actorTable);
>         tableLock.append(" IN ACCESS EXCLUSIVE MODE;");
>         res = PQexec(connection, tableLock.c_str());


> Well, all this doesn't work (the connection is th e always the same in
> all methods and functions). Have I to Lock the table, perform some
> operation on this table, and unlock the table all in the same function
> scope?

What do you mean it doesn't work?  How exactly it fails?

If anything, I'd suggest to send the LOCK TABLE in a separate PQexec()
call from BEGIN WORK.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

----
Email.it, the professional e-mail, gratis per te: clicca qui

Sponsor:
Scopri le tue passioni con Leonardo.it!
Clicca qui

luca.ciciriello@email.it wrote:
> Sorry for delay in my answer. The problem is that with the lock
> instructions my app remain in a freeze state. It resembling a MUTEX
> deadlock.Anyway, as soon as possible I'll test yuor idea to use a
> separate PQexec.

Freeze state?  Oh, you mean like somebody already holds the lock, so
your LOCK TABLE is just waiting for the holder to release it ...

Have a look around the pg_locks view.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support