Re: php-psql lock problem. Thanks!

I'm not sure what the answer to your problem is, but I'm sure you have the
wrong approach.

For all practical purposes, client/server database programming is a
multiprocessing problem set. What you are trying to implement is a mutex. A
mutex is a mutual exclusion tool. You can't reliably do what you think you are
doing. 

If one process asks if something is locked, and the answer is no, in the
interim time another process can do the same thing. You will still have a
deadlock situation because [n] processes can read something as unlocked, and
then set themselves on a course of action in which all will attempt to lock.
You may reduce the probability, but you can not eliminate it.

You will need to come up with a mutex protocol. i.e. 

<?php$res = pg_exec($conn, "select mylock()");
if(pg_Result($res, "mylock") == "yes")){    (***)    pg_exec($conn, "select myunlock()");}else    // is locked do
somethingelse
 
?>

The "mylock" and the "myunlock" have to work across the multiple PostgreSQL
processes and use SYSV semaphore or something to manage the lock.

So, what end result are you trying to have? Are you saying you want one user to
be able to lock a table for a series of transactions, while another can use it
in a readonly fashion? But it only gets read-only access if something is
already locked? What if it needs to update? Since you mention PHP, I assume
this is a web site or something. Since you mention login, I assume you are
writing some sort of session manager.

AFAIK SQL does not have the concept of a testable Mutex, you will have to write
your own. But if you are doing a session manager in PHP, email me directly, I
have a number of suggestions.




Maurizio Ortolan wrote:
> 
> Hello to everybody!
> 
> I've a little problem with LOCK-ing a
> certain row in a table using PHP and
> PostgreSQL on LINUX.
> 
>  >> In a few words, I'd like to undertand
>  >> how find out if a certain row is locked,
>  >> in order to prevent a kind of deadlock.
> 
> Which is the (system) table where all
> locked row or tables are 'saved' ?
> Is there any flag?
> 
> // ############################
> Example 1:
> 
> User A:
> 
> BEGIN WORK;
> select login from people where userid='1' for update;
> [ ... ]
> COMMIT WORK;
> 
> User B:
> BEGIN WORK;
> (***)
> select login from people where userid='1' for update;
> 
> [ WAIT UNTIL 'COMMIT WORK' of user A  !  :(  ]
> 
> COMMIT WORK;
> 
> Solution:
> I'd like to put in (***) a quick check in order to
> know if the row with userid='1' is already locked or not.
> 
> In this way, if it's already locked, I'll use
>     select login from people where userid='1';
>       [ ONLY READ ]
> instead of
>     select login from people where userid='1' for update;
>       [READ & WRITE]
> 
> // ############################
> Example 2:
> 
> BEGIN WORK;
> LOCK TABLE utenti IN SHARE ROW EXCLUSIVE MODE;
> select login from people where userid='1';
> COMMIT WORK;
> 
> // ############################
> 
> Many thanks to everybody!
> Ciao!
> MaURIZIO
> 
> crix98@____tin.it
> 
> It's sure that
>   a small example in PHP will very very appreciated!! :))
> 
> PS: it's possible to setup a timeout for a locked table,
>      in order to exec an aoutomatic ROLLBACK ??
>      (for examples if the user goes away?
> 
> *******************************************
> **  Happy surfing on THE NET !!      **
> **           Ciao by                           **
> **                       C R I X 98          **
> *******************************************
> AntiSpam: rimuovere il trattino basso
>                  dall'indirizzo  per scrivermi...
> (delete the underscore from the e-mail address to reply)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

-- 
------------------------
http://www.mohawksoft.com