Database locking problem from PHP scripts

[amal.barman]

Hello,
I am unable to solve a locking problem. I am using
postgresql database for this project. Here script one
(booking.php) tries to setup a lock for update and ask
user to fill-up the form. Now when the user fill-up
the form and submit it for update, second script
(update.php) update the database and releasing the
locks. This is working for client (say) A and B from
psql prompts but failed from PHP scripts. I have
written following scripts as follows.

booking.php
-----------

...
$db=pg_pconnect($host,$database);
// PostgreSQL database
$sql_str="BEGIN; SELECT * FROM seat WHERE seat_no=1
FOR UPDATE NOWAIT";
...
<FORM method="get" action="update.php">
// collecting user's input
...


update.php
----------
...
$db=pg_pconnect($host,$database);
$sql_str="UPDATE seat SET status='booked' WHERE
seat_no=1; END";
...

// end

My update script failed to do lock the table and
records. How can I fix? One WARNING is showing by
PostgreSQL, "there is no tranaction in progress".

Thanks for your help in advance.



???

[btherl]

I don't think pconnect allows you to keep transactions between HTTP requests.

Instead, you can do application level locking.  Create an entry in a table, and have other competing processes also create the same entry.  If there's a conflict, one process will fail.  Once the entry is successfully created, that represents an exclusive lock for the process which created the table entry.  That'll work as long as all processes which may alter that table respect the application level locking mechanism.

How were you planning to end the transaction if the client never fills in the form?

[amal.barman]

Thanks btherl for your response.