Thread: Cleaning up aborted transactions

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!

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

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

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

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.

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

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

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
>