Thread: Opinions on how to Integrate Transactions

Hey,

I'm new to using transactions. I'd like to integrate them into my PHP
application to prevent a few issues we've been having. Such as, some
queries in the beginning of the php script running, then an error
occurring, preventing queries toward the bottom of the script from
running. It's leaving me with things such as a forum topic without any
associated forum posts. From what I've read on transactions, they
would be the solution to my problems.

So I have a couple questions about them.

1. Is there a way to list active transactions on all databases? I'd
like a way to see if there are any "idle" transactions that are just
hanging there. Is there a way to "kill" them from the console?

2. I have a PHP class which communicates with the DB, which is
included into every other page on the site. Should I just add BEGIN
and COMMIT at the beginning and end of the class? This way, I wont
need to edit every single page on my site to include BEGIN and COMMIT?
Or will that type of "blind" transactions cause problems?

3. What happens when there IS an error? Do I need to run ROLLBACK
every time? Or will it automatically reset when the connection is
closed when the PHP script dies?

Thanks for the advice.

> Hey,
>
> I'm new to using transactions. I'd like to integrate them into my PHP
> application to prevent a few issues we've been having. Such as, some
> queries in the beginning of the php script running, then an error
> occurring, preventing queries toward the bottom of the script from
> running. It's leaving me with things such as a forum topic without any
> associated forum posts. From what I've read on transactions, they
> would be the solution to my problems.

As far as I understand it, the transactions may solve your problem. Or
more precisely, symptomps of your problem. I don't say you should not
use transactions, but a proper solution would be to fix the original
problem causing the error.

Why do the statements fail? I usually see this when the user's input is
not verified properly (not all required information entered, invalid
inputs, invalid characters, etc.). If this is the case, add the
verification of inputs (and then maybe the transaction).

> So I have a couple questions about them.
>
> 1. Is there a way to list active transactions on all databases? I'd
> like a way to see if there are any "idle" transactions that are just
> hanging there. Is there a way to "kill" them from the console?

No, AFAIK it's not possible to list currently running transactions. You
can list currently running statements (see the pg_stat_activity), and
various information about the database (number of commited / rolled back
transactions etc. - see the pg_stat_database).

But I don't see why you'd be interested in a currently running
transactions, as:

(a) If the client disconnects without explicitly commiting or rolling
back a transaction, the transaction is rolled back automatically

(b) If the client is connected, and the transaction is still open, it
means a SQL statement is running (and you can see it in the
pg_stat_activity), or the client (PHP script) is performing something
else. So you can't kill it anyway as you don't know if the script will
continue or what.

> 2. I have a PHP class which communicates with the DB, which is
> included into every other page on the site. Should I just add BEGIN
> and COMMIT at the beginning and end of the class? This way, I wont
> need to edit every single page on my site to include BEGIN and COMMIT?
> Or will that type of "blind" transactions cause problems?

What do you mean by 'beginning and end of a class'? A transaction should
demarcate an atomic piece of functionality, so we it's impossible to say
where to put 'begin / commit' without a proper analysis of your code.

The simplest solution is probably to put 'begin' right after opening the
connection to the database, and 'commit' right before the disconnect.
This way you'll enclose the whole page into a single transaction, so you
won't get the 'partially created forums' and so on.

> 3. What happens when there IS an error? Do I need to run ROLLBACK
> every time? Or will it automatically reset when the connection is
> closed when the PHP script dies?

If there is an error, it's impossible to run a commit (unless you use
'rollback to' statement). More precisely - it's possible to execute
'COMMIT' but the transaction will replace it with 'ROLLBACK'. So for
example this:

CREATE TABLE Test (id integer);

BEGIN;
INSERT INTO Test VALUES (1);
INSERT INTO Test VALUES ('aaa');
COMMIT;

won't insert anything into the 'Test' table (it'll remain empty).

> Thanks for the advice.

regards
Tomas

On Sun, 2009-06-14 at 11:14 -0400, APseudoUtopia wrote:

> associated forum posts. From what I've read on transactions, they
> would be the solution to my problems.

Yes, but proper use of transactions - and a proper understanding of
transactions - will help you a lot more than that.

I very, very strongly recommend that you read the PostgreSQL manual. It
will not only teach you about PostgreSQL, but should teach you a lot
about how to use a relational database effectively.

It's really important to understand how transactions operate in a
concurrent environment, how locking works (and where it doesn't apply),
etc. Understanding MVCC is also rather helpful. The PostgreSQL manual
will teach you all these things and a LOT more.

> 1. Is there a way to list active transactions on all databases?

SELECT * FROM pg_stat_activity;

Filter the results as desired.

>  I'd
> like a way to see if there are any "idle" transactions that are just
> hanging there. Is there a way to "kill" them from the console?

pg_cancel_backend(...)

However, you should not have idle transactions in the first place. An
idle transaction indicates an application bug unless your application is
designed to hold transactions open while waiting for user input. If it
is meant to, then killing those idle transactions would be wrong, since
the app expects them to be there later.

Holding transactions open for long periods, like waiting for user input,
causes all sorts of issues, though, and is best avoided.

> 2. I have a PHP class which communicates with the DB, which is
> included into every other page on the site. Should I just add BEGIN
> and COMMIT at the beginning and end of the class? This way, I wont
> need to edit every single page on my site to include BEGIN and COMMIT?
> Or will that type of "blind" transactions cause problems?

It's a much better idea to look at how you're actually communicating
with the database, examine each query or set of queries, and see what
needs to be wrapped in a transaction.

In lots of cases it will be fine to just wrap the lot in begin/commit.
In some cases, though, you might have one operation that you want to
succeed even if later ones fail. Additionally, if you have single
independent statements, there's no need to wrap them in BEGIN/COMMIT,
since Pg creates an implicit transaction for each statement if one isn't
already running.

> 3. What happens when there IS an error? Do I need to run ROLLBACK
> every time? Or will it automatically reset when the connection is
> closed when the PHP script dies?

If the connection is closed while a transaction is open, an implicit
ROLLBACK is issued by the backend.

However, you should always close your transactions properly, both to
ensure that data that should get committed does get committed, and so
that if you start using connection pooling (which you probably will)
you're not driven insane by issues with transactions left open.

--
Craig Ringer