pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Cleaning up aborted transactions
8+ messages / 5 participants
[nested] [flat]

* Cleaning up aborted transactions
@ 2003-06-09 08:09 Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 08:05 ` Re: Cleaning up aborted transactions Adrian Tineo <adriantineo@softhome.net>
  0 siblings, 1 reply; 8+ messages in thread

From: Michael Glaesemann @ 2003-06-09 08:09 UTC (permalink / raw)
  To: pgsql-php

I've been working my way through learning PostgreSQL (7.3.2) and PHP 
(4.3.0) and have been really pleased how things have been coming 
together. However, I have a question about handling transactions with 
PostgreSQL and PHP.

Before I figured out how to successfully implement a transaction 
through PHP, I bungled it quite a few times, in that the transaction 
got aborted. Unfortunately, due to my inexperience, the COMMIT 
statement didn't get sent after it was aborted—the PHP script just died.

After I finally figured out how to do transactions without crashing and 
burning (or at least sending a COMMIT if it did abort), I would still 
get SQL errors saying that the current SQL couldn't be processed 
because a transaction had been aborted. Now, I know for a fact that the 
scripts I was running then weren't issuing transactions. And it was 
very sporadic. Sometimes I'd just be running the same script that had 
just run successfully—no changes, nothing occurring in the interim—and 
I'd get the "transaction aborted" SQL error.

I'm wondering if PHP is cycling through a number of different 
connections to use to get to the database. A few of them had been 
aborted and not committed, so when PHP selected that connection, 
Postgres would send the transaction aborted error.

How do I clean up these aborted transactions? Of course, prevention is 
better than cure, but we all get colds sometimes. :)

I've since restarted postmaster and the problem seems like it went 
away. Scripts running fine. But I'd like to know how to fix this in the 
future (without restrarting postmaster) when it happens again.

Thanks for any advice! 



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Cleaning up aborted transactions
  2003-06-09 08:09 Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
@ 2003-06-10 08:05 ` Adrian Tineo <adriantineo@softhome.net>
  2003-06-10 09:21   ` Re: Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Adrian Tineo @ 2003-06-10 08:05 UTC (permalink / raw)
  To: Michael Glaesemann <grzm@myrealbox.com>; pgsql-php

The correct way of working with transactions is to use:

BEGIN
...sql commands
COMMIT or ROLLBACK

So I would use this for example:

pg_connect($connection);
pg_query($connection,"BEGIN;");
$insert="INSERT INTO table VALUES (2,7,5);
$result=pg_query($connection,$insert);
if(!$result){
    pg_query($connection,"ROLLBACK");
    //Something went wrong with the insert so we rollback and nothing
changes in the db
}else{
    pg_query($connection,"COMMIT");
    // If everything went all right, then we commit the changes
}
pg_close($connection);

Of course, the interesting thing comes when you have several operations
(inserts, deletes or updates) between begin and commit, this way either you
make all the changes or make none, that's the cool thing about transactions.
In each operation just check whether the result was valid or not. If ANY of
them was invalid, rollback and none of the changes will take effect.

I don't know if this answer your question...

Adrian Tineo





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Cleaning up aborted transactions
  2003-06-09 08:09 Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 08:05 ` Re: Cleaning up aborted transactions Adrian Tineo <adriantineo@softhome.net>
@ 2003-06-10 09:21   ` Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 11:49     ` Re: Cleaning up aborted transactions Rod Taylor <rbt@rbt.ca>
  0 siblings, 1 reply; 8+ messages in thread

From: Michael Glaesemann @ 2003-06-10 09:21 UTC (permalink / raw)
  To: pgsql-php


On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
> pg_connect($connection);
> pg_query($connection,"BEGIN;");
> $insert="INSERT INTO table VALUES (2,7,5);
> $result=pg_query($connection,$insert);
> if(!$result){
>     pg_query($connection,"ROLLBACK");
>     //Something went wrong with the insert so we rollback and nothing
> changes in the db
> }else{
>     pg_query($connection,"COMMIT");
>     // If everything went all right, then we commit the changes
> }
> pg_close($connection);


Thanks, Adrian, for the illustration, especially the instance with 
ROLLBACK. I've figured out this sequence now.

What I'm wondering about is what happens when this sequence isn't 
properly followed and the script quits before issuing a ROLLBACK or a 
COMMIT. Obviously it's not a good situation and one to be avoided.

But, if it does happen, what are the alternatives? How do I finish off 
that transaction that hasn't been properly finished? I'm assuming 
restarting postmaster will do it, but I'm wondering if there's any 
other way.

I haven't been able to find anything about this after googling. 
Obviously the vast majority of people have their ducks in a row. :)

Thanks again, Adrian. I appreciate the response.

Carefully closing my transactions,

Michael




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Cleaning up aborted transactions
  2003-06-09 08:09 Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 08:05 ` Re: Cleaning up aborted transactions Adrian Tineo <adriantineo@softhome.net>
  2003-06-10 09:21   ` Re: Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
@ 2003-06-10 11:49     ` Rod Taylor <rbt@rbt.ca>
  2003-06-10 11:39       ` Re: Cleaning up aborted transactions Greg Spiegelberg <gspiegelberg@cranel.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Rod Taylor @ 2003-06-10 11:49 UTC (permalink / raw)
  To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-php

On Tue, 2003-06-10 at 05:21, Michael Glaesemann wrote:
> On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
> > pg_connect($connection);
> > pg_query($connection,"BEGIN;");
> > $insert="INSERT INTO table VALUES (2,7,5);
> > $result=pg_query($connection,$insert);
> > if(!$result){
> >     pg_query($connection,"ROLLBACK");
> >     //Something went wrong with the insert so we rollback and nothing
> > changes in the db
> > }else{
> >     pg_query($connection,"COMMIT");
> >     // If everything went all right, then we commit the changes
> > }
> > pg_close($connection);
> 
> 
> Thanks, Adrian, for the illustration, especially the instance with 
> ROLLBACK. I've figured out this sequence now.
> 
> What I'm wondering about is what happens when this sequence isn't 
> properly followed and the script quits before issuing a ROLLBACK or a 
> COMMIT. Obviously it's not a good situation and one to be avoided.

The database, unless explicitly told to commit and the commit is
completely successful will always rollback.

So a disconnect results in a rollback.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Cleaning up aborted transactions
  2003-06-09 08:09 Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 08:05 ` Re: Cleaning up aborted transactions Adrian Tineo <adriantineo@softhome.net>
  2003-06-10 09:21   ` Re: Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 11:49     ` Re: Cleaning up aborted transactions Rod Taylor <rbt@rbt.ca>
@ 2003-06-10 11:39       ` Greg Spiegelberg <gspiegelberg@cranel.com>
  2003-06-10 13:19         ` Re: Cleaning up aborted transactions Rod Taylor <rbt@rbt.ca>
  2003-06-10 13:42         ` Re: Cleaning up aborted transactions Adrian Tineo <adriantineo@softhome.net>
  2003-06-16 05:12         ` Re: Cleaning up aborted transactions Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 3 replies; 8+ messages in thread

From: Greg Spiegelberg @ 2003-06-10 11:39 UTC (permalink / raw)
  To: pgsql-php

Hi all,

Just so I have this straight, because I've been wondering the same,
if someone on my web site does a SELECT and then clicks on the browser
stop or gets click happy hitting one or many other links on the same
web page then the 1st SELECT and all other interrupted SELECT's should
get rolled back if each query starts with a BEGIN; because there is a
disconnect.  Correct?

What about with persistant connections?

Greg


Rod Taylor wrote:
> On Tue, 2003-06-10 at 05:21, Michael Glaesemann wrote:
> 
>>On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
>>
>>>pg_connect($connection);
>>>pg_query($connection,"BEGIN;");
>>>$insert="INSERT INTO table VALUES (2,7,5);
>>>$result=pg_query($connection,$insert);
>>>if(!$result){
>>>    pg_query($connection,"ROLLBACK");
>>>    //Something went wrong with the insert so we rollback and
>>
> nothing
> 
>>>changes in the db
>>>}else{
>>>    pg_query($connection,"COMMIT");
>>>    // If everything went all right, then we commit the changes
>>>}
>>>pg_close($connection);
>>
>>
>>Thanks, Adrian, for the illustration, especially the instance with 
>>ROLLBACK. I've figured out this sequence now.
>>
>>What I'm wondering about is what happens when this sequence isn't 
>>properly followed and the script quits before issuing a ROLLBACK or a 
>>COMMIT. Obviously it's not a good situation and one to be avoided.
> 
> 
> The database, unless explicitly told to commit and the commit is
> completely successful will always rollback.
> 
> So a disconnect results in a rollback.
> 


-- 
Greg Spiegelberg
  Sr. Product Development Engineer
  Cranel, Incorporated.
  Phone: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@Cranel.com
Cranel. Technology. Integrity. Focus.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Cleaning up aborted transactions
  2003-06-09 08:09 Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 08:05 ` Re: Cleaning up aborted transactions Adrian Tineo <adriantineo@softhome.net>
  2003-06-10 09:21   ` Re: Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 11:49     ` Re: Cleaning up aborted transactions Rod Taylor <rbt@rbt.ca>
  2003-06-10 11:39       ` Re: Cleaning up aborted transactions Greg Spiegelberg <gspiegelberg@cranel.com>
@ 2003-06-10 13:19         ` Rod Taylor <rbt@rbt.ca>
  2 siblings, 0 replies; 8+ messages in thread

From: Rod Taylor @ 2003-06-10 13:19 UTC (permalink / raw)
  To: Greg Spiegelberg <gspiegelberg@cranel.com>; +Cc: pgsql-php

On Tue, 2003-06-10 at 07:39, Greg Spiegelberg wrote:
> Hi all,
> 
> Just so I have this straight, because I've been wondering the same,
> if someone on my web site does a SELECT and then clicks on the browser
> stop or gets click happy hitting one or many other links on the same
> web page then the 1st SELECT and all other interrupted SELECT's should
> get rolled back if each query starts with a BEGIN; because there is a
> disconnect.  Correct?

What happens is up to PHP to decide in this case.  If the browser
disconnects from the webserver, thus PHP is interrupted, then yes, that
sounds like what should happen.

> What about with persistant connections?

Persistent & pooled connections are managed by the client.  It is up to
PHP to determine what needs to happen.  PHP should be resetting (rolling
back) the connection when it re-enters into the pool, but the database
has no knowledge that sharing is taking place.

I'm not entirely sure.  Haven't tried persistent connections since the
early 3.x days.

> Rod Taylor wrote:
> > On Tue, 2003-06-10 at 05:21, Michael Glaesemann wrote:
> > 
> >>On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
> >>
> >>>pg_connect($connection);
> >>>pg_query($connection,"BEGIN;");
> >>>$insert="INSERT INTO table VALUES (2,7,5);
> >>>$result=pg_query($connection,$insert);
> >>>if(!$result){
> >>>    pg_query($connection,"ROLLBACK");
> >>>    //Something went wrong with the insert so we rollback and
> >>
> > nothing
> > 
> >>>changes in the db
> >>>}else{
> >>>    pg_query($connection,"COMMIT");
> >>>    // If everything went all right, then we commit the changes
> >>>}
> >>>pg_close($connection);
> >>
> >>
> >>Thanks, Adrian, for the illustration, especially the instance with 
> >>ROLLBACK. I've figured out this sequence now.
> >>
> >>What I'm wondering about is what happens when this sequence isn't 
> >>properly followed and the script quits before issuing a ROLLBACK or a 
> >>COMMIT. Obviously it's not a good situation and one to be avoided.
> > 
> > 
> > The database, unless explicitly told to commit and the commit is
> > completely successful will always rollback.
> > 
> > So a disconnect results in a rollback.
> > 
-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Cleaning up aborted transactions
  2003-06-09 08:09 Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 08:05 ` Re: Cleaning up aborted transactions Adrian Tineo <adriantineo@softhome.net>
  2003-06-10 09:21   ` Re: Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 11:49     ` Re: Cleaning up aborted transactions Rod Taylor <rbt@rbt.ca>
  2003-06-10 11:39       ` Re: Cleaning up aborted transactions Greg Spiegelberg <gspiegelberg@cranel.com>
@ 2003-06-10 13:42         ` Adrian Tineo <adriantineo@softhome.net>
  2 siblings, 0 replies; 8+ messages in thread

From: Adrian Tineo @ 2003-06-10 13:42 UTC (permalink / raw)
  To: Greg Spiegelberg <gspiegelberg@cranel.com>; pgsql-php

Hi Greg

> Just so I have this straight, because I've been wondering the same,
> if someone on my web site does a SELECT and then clicks on the browser
> stop or gets click happy hitting one or many other links on the same
> web page then the 1st SELECT and all other interrupted SELECT's should
> get rolled back if each query starts with a BEGIN; because there is a
> disconnect.  Correct?

I don't let that happen. First, I gather all the required information, then
when the user clicks on submit or whatever he's doing, I do all in one
transaction. If it ends right the user is taken to a screen where he knows
that everything went fine (commit was successfull). If it fails at some
point I send the user to an error page, which can be more or less
descriptive depending on the needs of the interface.

> What about with persistant connections?

I haven't used them as most server out there will have them disabled.
Besides I don't think you need them and is probably not very efficient, also
it could limit the number of users for your app. And it can lead to
unpredictable behaviour on complex websites. If you don't need them, avoid
them.

Adrian Tineo





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Cleaning up aborted transactions
  2003-06-09 08:09 Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 08:05 ` Re: Cleaning up aborted transactions Adrian Tineo <adriantineo@softhome.net>
  2003-06-10 09:21   ` Re: Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
  2003-06-10 11:49     ` Re: Cleaning up aborted transactions Rod Taylor <rbt@rbt.ca>
  2003-06-10 11:39       ` Re: Cleaning up aborted transactions Greg Spiegelberg <gspiegelberg@cranel.com>
@ 2003-06-16 05:12         ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2 siblings, 0 replies; 8+ messages in thread

From: Christopher Kings-Lynne @ 2003-06-16 05:12 UTC (permalink / raw)
  To: Greg Spiegelberg <gspiegelberg@cranel.com>; pgsql-php

PHP issues a ROLLBACK into every persistent connection before passing it to
the next process.

Chris

----- Original Message ----- 
From: "Greg Spiegelberg" <gspiegelberg@cranel.com>
To: <pgsql-php@postgresql.org>
Sent: Tuesday, June 10, 2003 7:39 PM
Subject: Re: [PHP] Cleaning up aborted transactions


> Hi all,
>
> Just so I have this straight, because I've been wondering the same,
> if someone on my web site does a SELECT and then clicks on the browser
> stop or gets click happy hitting one or many other links on the same
> web page then the 1st SELECT and all other interrupted SELECT's should
> get rolled back if each query starts with a BEGIN; because there is a
> disconnect.  Correct?
>
> What about with persistant connections?
>
> Greg
>
>
> Rod Taylor wrote:
> > On Tue, 2003-06-10 at 05:21, Michael Glaesemann wrote:
> >
> >>On Tuesday, Jun 10, 2003, at 17:05 Asia/Tokyo, Adrian Tineo wrote:
> >>
> >>>pg_connect($connection);
> >>>pg_query($connection,"BEGIN;");
> >>>$insert="INSERT INTO table VALUES (2,7,5);
> >>>$result=pg_query($connection,$insert);
> >>>if(!$result){
> >>>    pg_query($connection,"ROLLBACK");
> >>>    //Something went wrong with the insert so we rollback and
> >>
> > nothing
> >
> >>>changes in the db
> >>>}else{
> >>>    pg_query($connection,"COMMIT");
> >>>    // If everything went all right, then we commit the changes
> >>>}
> >>>pg_close($connection);
> >>
> >>
> >>Thanks, Adrian, for the illustration, especially the instance with
> >>ROLLBACK. I've figured out this sequence now.
> >>
> >>What I'm wondering about is what happens when this sequence isn't
> >>properly followed and the script quits before issuing a ROLLBACK or a
> >>COMMIT. Obviously it's not a good situation and one to be avoided.
> >
> >
> > The database, unless explicitly told to commit and the commit is
> > completely successful will always rollback.
> >
> > So a disconnect results in a rollback.
> >
>
>
> -- 
> Greg Spiegelberg
>   Sr. Product Development Engineer
>   Cranel, Incorporated.
>   Phone: 614.318.4314
>   Fax:   614.431.8388
>   Email: gspiegelberg@Cranel.com
> Cranel. Technology. Integrity. Focus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2003-06-16 05:12 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-09 08:09 Cleaning up aborted transactions Michael Glaesemann <grzm@myrealbox.com>
2003-06-10 08:05 ` Adrian Tineo <adriantineo@softhome.net>
2003-06-10 09:21   ` Michael Glaesemann <grzm@myrealbox.com>
2003-06-10 11:49     ` Rod Taylor <rbt@rbt.ca>
2003-06-10 11:39       ` Greg Spiegelberg <gspiegelberg@cranel.com>
2003-06-10 13:19         ` Rod Taylor <rbt@rbt.ca>
2003-06-10 13:42         ` Adrian Tineo <adriantineo@softhome.net>
2003-06-16 05:12         ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox