Thread: PostgreSQL locking from PHP scripts
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 I can fix it? One WARNING is showing by PostgreSQL, "there is no tranaction in progress". Thanks for your help in advance. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thursday 26 October 2006 11:11, Amal burman <amalburman@yahoo.com> wrote: > 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. You can't do this kind of thing in PHP. PHP resets the database connection between scripts. The script is finished as soon as the page is delivered to the client. Also, web connections are stateless and there is no way to even know what web server process or database connection you'll be getting on subsequent page views. -- "It is a besetting vice of democracies to substitute public opinion for law." - James Fenimore Cooper
Attachment
On Monday 06 November 2006 11:16, "Gavin M. Roy" <gmr@ehpg.net> wrote: > Well you can use persistent connections. Instead of pg_connect use > pg_pconnect. I don't know if it will keep the lock or not, I've not > tested, but it seems like you'd need a pretty elaborate connection > management setup to make sure you're locking and unlocking the > records for the right client. No, it won't. pconnect and connect are guaranteed to work the same by PHP, so pconnect connections are reset at script end. The only difference is connection overhead. I've been trying to find a way around this to allow cursor usage, but there doesn't seem to be one. -- "Thank God we don't get all the government we pay for." -- Will Rogers
Attachment
Well you can use persistent connections. Instead of pg_connect use pg_pconnect. I don't know if it will keep the lock or not, I've not tested, but it seems like you'd need a pretty elaborate connection management setup to make sure you're locking and unlocking the records for the right client. On Nov 6, 2006, at 11:05 AM, Alan Hodgson wrote: > On Thursday 26 October 2006 11:11, Amal burman > <amalburman@yahoo.com> wrote: >> 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. > > You can't do this kind of thing in PHP. PHP resets the database > connection > between scripts. The script is finished as soon as the page is > delivered > to the client. > > Also, web connections are stateless and there is no way to even > know what > web server process or database connection you'll be getting on > subsequent > page views. > > -- > "It is a besetting vice of democracies to substitute public opinion > for > law." - James Fenimore Cooper >
Alan Hodgson wrote: > On Thursday 26 October 2006 11:11, Amal burman <amalburman@yahoo.com> wrote: > >> 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. >> > > You can't do this kind of thing in PHP. PHP resets the database connection > between scripts. The script is finished as soon as the page is delivered > to the client. > > Also, web connections are stateless and there is no way to even know what > web server process or database connection you'll be getting on subsequent > page views. > > So is PHP and 'ANY' database a useless adventure when it goes to developing a robust business and accounting package? --Hal.
Herbie McDuck wrote: > Alan Hodgson wrote: >> On Thursday 26 October 2006 11:11, Amal burman <amalburman@yahoo.com> >> wrote: >> >>> 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. >> >> You can't do this kind of thing in PHP. PHP resets the database >> connection between scripts. The script is finished as soon as the >> page is delivered to the client. >> >> Also, web connections are stateless and there is no way to even know >> what web server process or database connection you'll be getting on >> subsequent page views. >> > So is PHP and 'ANY' database a useless adventure when it goes to > developing a robust business and accounting package? No, it applies to anything you do in a web browser. The HTTP protocol is "stateless" which means all resources, connections etc are killed at the end of the script. Resources of any type are not kept alive at the end of a script. It's not a php thing or <insert random language here>, it's a HTTP protocol thing. -- Postgresql & php tutorials http://www.designmagick.com/
Herbie McDuck wrote: >> Also, web connections are stateless and there is no way to even know >> what web server process or database connection you'll be getting on >> subsequent page views. >> > So is PHP and 'ANY' database a useless adventure when it goes to > developing a robust business and accounting package? Not at all. It just means that you'll have to create your own "state" logic using sessions. For instance, create a column within the necessary table that you can check to see whether or not some other process/user is using the record in question and then build the necessary logic around that to enable/disable access to the records. This is something that isn't specific to PHP. You'll encounter the same issues with any other middleware language that uses a web browser as it's primary means of interaction. HTTP just doesn't keep track of the state of your application. It's up to you as the developer to handle that. Charley
On Monday 06 November 2006 20:24, Chris wrote:
> Herbie McDuck wrote:
> > Alan Hodgson wrote:
> >> On Thursday 26 October 2006 11:11, Amal burman <amalburman@yahoo.com>
> >>
> >> wrote:
> >>> 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.
> >>
> >> You can't do this kind of thing in PHP. PHP resets the database
> >> connection between scripts. The script is finished as soon as the
> >> page is delivered to the client.
> >>
> >> Also, web connections are stateless and there is no way to even know
> >> what web server process or database connection you'll be getting on
> >> subsequent page views.
> >
> > So is PHP and 'ANY' database a useless adventure when it goes to
> > developing a robust business and accounting package?
>
> No, it applies to anything you do in a web browser.
>
> The HTTP protocol is "stateless" which means all resources, connections
> etc are killed at the end of the script. Resources of any type are not
> kept alive at the end of a script.
>
> It's not a php thing or <insert random language here>, it's a HTTP
> protocol thing.
In theory you can use prepared transactions to get around this, but I've never
seen anyone fully implement it in PHP.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Charley Tiggs wrote: > Herbie McDuck wrote: >>> Also, web connections are stateless and there is no way to even know >>> what web server process or database connection you'll be getting on >>> subsequent page views. >> So is PHP and 'ANY' database a useless adventure when it goes to >> developing a robust business and accounting package? > > Not at all. It just means that you'll have to create your own "state" > logic using sessions. For instance, create a column within the > necessary table that you can check to see whether or not some other > process/user is using the record in question and then build the > necessary logic around that to enable/disable access to the records. > > This is something that isn't specific to PHP. You'll encounter the > same issues with any other middleware language that uses a web browser > as it's primary means of interaction. HTTP just doesn't keep track of > the state of your application. It's up to you as the developer to > handle that. > > Charley > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > > Hmmm..Yes I can see two columns in a record. The 'WHO' and some pending transaction ID that the calling session that locked the record knows about so that the update session can find the relevant and specific record to transact with. As mentioned, I've done something similar in another language. When you have ten people trying to sell low sulfur diesel fuel at the same moment in time, record contention can become dicey at best. --Hal.
Chris wrote: > Herbie McDuck wrote: >> Alan Hodgson wrote: >>> On Thursday 26 October 2006 11:11, Amal burman >>> <amalburman@yahoo.com> wrote: >>> >>>> 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. >>> >>> You can't do this kind of thing in PHP. PHP resets the database >>> connection between scripts. The script is finished as soon as the >>> page is delivered to the client. >>> >>> Also, web connections are stateless and there is no way to even know >>> what web server process or database connection you'll be getting on >>> subsequent page views. >> So is PHP and 'ANY' database a useless adventure when it goes to >> developing a robust business and accounting package? > > No, it applies to anything you do in a web browser. > > The HTTP protocol is "stateless" which means all resources, > connections etc are killed at the end of the script. Resources of any > type are not kept alive at the end of a script. > > It's not a php thing or <insert random language here>, it's a HTTP > protocol thing. > There is another way to approach this with PHP and other random languages, as long as scripts can be executed outside of the context of an HTTP server module (such as PHP in CLI mode): have a the web script interact with a standalone commandline script or daemon that lives across requests. Thus your HTTP-requested PHP script could communicate via some IPC method to a PHP-CLI script which holds the database connections, binds them to a session ID and actually handles the mechanics of transactions and queries (a simple connection pooling method, if you will) and thus transactions can be maintained across multiple requests. Now, saying it and doing it are two different things, I understand; not exactly trivial to implement. But still, if it is important enough to your application, it can be done. And, I have found PHP to be quite stable running in daemon mode. At least, amply stable enough to handle transactions that span minutes or hours. (I have had PHP daemon scripts run for weeks without problem)
Robert Treat wrote: > On Monday 06 November 2006 20:24, Chris wrote: > >> Herbie McDuck wrote: >> >>> Alan Hodgson wrote: >>> >>>> On Thursday 26 October 2006 11:11, Amal burman <amalburman@yahoo.com> >>>> >>>> wrote: >>>> >>>>> 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. >>>>> >>>> You can't do this kind of thing in PHP. PHP resets the database >>>> connection between scripts. The script is finished as soon as the >>>> page is delivered to the client. >>>> >>>> Also, web connections are stateless and there is no way to even know >>>> what web server process or database connection you'll be getting on >>>> subsequent page views. >>>> >>> So is PHP and 'ANY' database a useless adventure when it goes to >>> developing a robust business and accounting package? >>> >> No, it applies to anything you do in a web browser. >> >> The HTTP protocol is "stateless" which means all resources, connections >> etc are killed at the end of the script. Resources of any type are not >> kept alive at the end of a script. >> >> It's not a php thing or <insert random language here>, it's a HTTP >> protocol thing. >> > > In theory you can use prepared transactions to get around this, but I've never > seen anyone fully implement it in PHP. > > How exactly would that work? With PDO Prepare/Execute? Would you prepare a transaction, and then store the PDOStatement object as a session var? Or do you mean this is something that would have to be implemented in PHP internals?
On Tuesday 07 November 2006 02:54, Rick Morris wrote: > Robert Treat wrote: > > On Monday 06 November 2006 20:24, Chris wrote: > >> Herbie McDuck wrote: > >>> Alan Hodgson wrote: > >>>> On Thursday 26 October 2006 11:11, Amal burman <amalburman@yahoo.com> > >>>> > >>>> wrote: > >>>>> 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. > >>>> > >>>> You can't do this kind of thing in PHP. PHP resets the database > >>>> connection between scripts. The script is finished as soon as the > >>>> page is delivered to the client. > >>>> > >>>> Also, web connections are stateless and there is no way to even know > >>>> what web server process or database connection you'll be getting on > >>>> subsequent page views. > >>> > >>> So is PHP and 'ANY' database a useless adventure when it goes to > >>> developing a robust business and accounting package? > >> > >> No, it applies to anything you do in a web browser. > >> > >> The HTTP protocol is "stateless" which means all resources, connections > >> etc are killed at the end of the script. Resources of any type are not > >> kept alive at the end of a script. > >> > >> It's not a php thing or <insert random language here>, it's a HTTP > >> protocol thing. > > > > In theory you can use prepared transactions to get around this, but I've > > never seen anyone fully implement it in PHP. > > How exactly would that work? With PDO Prepare/Execute? Would you prepare > a transaction, and then store the PDOStatement object as a session var? > Your thinking of prepared statements, which are something different entirely. I'm talking about prepared transactions, which are typically used for 2 phase commit implementations. http://www.postgresql.org/docs/8.1/interactive/sql-prepare-transaction.html > Or do you mean this is something that would have to be implemented in > PHP internals? Actually it canall be done at the SQL level, although if you were going to do it seriously I'd suspect that you'd want to add some php internal functions to do it, though aiui there aren't any knobs you'd get from libpq to do this, so not sure what that would like like on the php end. In any case upon a bit more reflection prepared transactions really wouldn't allow you to do what the OP wanted, so it's all kind of moot. -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL