Jump to content


Photo

Database locking problem from PHP scripts


  • Please log in to reply
2 replies to this topic

#1 amal.barman

amal.barman
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 26 October 2006 - 06:59 PM

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.



???





#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 27 October 2006 - 02:02 AM

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?

#3 amal.barman

amal.barman
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 27 October 2006 - 04:42 PM

Thanks btherl for your response.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users