Thread: PostgreSQL/PHP: transactions: how-to abstract out?
I posted this on the PHP-general list since it is more of a programming
techniques question that a PostgreSQL question but I thought I would ask
it here also since it has to do with abstracting out the DB layer and I
get the feeling more people on this list have faced this kind of issue
before and have thought about it the issues more in depth.
The problem:
I tough I had abstracted out the SQL querying part of my code out, just
to find out today that it doesn't work when it comes to transactions. I
had come up with this code:
function sql_query($sql) {
$conn = pg_connect("dbname=JC user=postgres");
$res = pg_exec($conn, $sql);
if (!$res) {
echo "CONNECTION: could not execute query ($sql)";
die;
}
else return $res;
}
I had transactions in my code implemented like this:
$sql = "BEGIN";
sql_query($sql);
[some sql that should be in a transaction ...]
$sql = "COMMIT";
sql_query($sql);
This doesn't work. Now that I look at my code I can clearly see why. All
sql queries are executed using a new Postgres connection, hence the use
of BEGIN/COMMIT as I was using them have no effect.
Can someone recommend a way to abstract out my DB layer while still
being able to use transactions? (persistent connections are not an
option since there are too many inherent dangers).
I was thinking of using the same function but if the incoming query
contained the word BEGIN, saving that and all future queries in a
session var and when the COMMIT comes in executing all the saved queries
as one (i.e. "BEGIN;[....];COMMIT"). One drawback is that all queries
will be written out to disk (as session vars) and cause a performance hit.
I was also thinking about maybe the $sql a GLOBAL or first building up
my query as as long string ("BEGIN;[....];COMMIT") and *then* sending it
to my sql_query() function.
The last two seem easier to implement, safer, and more efficient but
they don't seem "elegant" because I haven't really abstracted out the
fact that I want a transaction. Whenever I write an SQL query I have to
think "does this need to be in a transaction" and then use a different
coding technique depending on the answer. And if the future something
that didn't need to be in a transaction now needs to be in a transaction
I have to revisit my code and change the code.
I'm sure someone out there must have thought about this and come up with
an elegant solution and way of abstracting out the DB layer from PHP.
Can anyone share their solution with me or give me some pointers to
reference material?
Thanks,
Jc
On 10 Jan 2003 at 14:05, Jean-Christian Imbeault wrote: > Can someone recommend a way to abstract out my DB layer while still > being able to use transactions? (persistent connections are not an > option since there are too many inherent dangers). Make sql_query() accept a connection as argument. Initiate it someplace else of course before calling sql_query(). Do validate the connection in sql_query(). Simple Bye Shridhar -- Afternoon, n.: That part of the day we spend worrying about how we wasted the morning.
So let me get straight the behavior that you want. You want all of the
queries executed in a single request to automatically be included in one
transaction that is commited at the end of the script? You don't ever want
to have to decide when things should and shouldn't be grouped together in a
transaction?
I don't think using persistent connections has anything to do with this
problem (someone correct me if I'm wrong) but what are the "inherent
dangers" you see in using them (I'm just curious).
thanks,
Rick
----- Original Message -----
From: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, January 09, 2003 10:05 PM
Subject: [GENERAL] PostgreSQL/PHP: transactions: how-to abstract out?
> I posted this on the PHP-general list since it is more of a programming
> techniques question that a PostgreSQL question but I thought I would ask
> it here also since it has to do with abstracting out the DB layer and I
> get the feeling more people on this list have faced this kind of issue
> before and have thought about it the issues more in depth.
>
> The problem:
>
> I tough I had abstracted out the SQL querying part of my code out, just
> to find out today that it doesn't work when it comes to transactions. I
> had come up with this code:
>
> function sql_query($sql) {
> $conn = pg_connect("dbname=JC user=postgres");
> $res = pg_exec($conn, $sql);
> if (!$res) {
> echo "CONNECTION: could not execute query ($sql)";
> die;
> }
> else return $res;
> }
>
> I had transactions in my code implemented like this:
>
> $sql = "BEGIN";
> sql_query($sql);
> [some sql that should be in a transaction ...]
> $sql = "COMMIT";
> sql_query($sql);
>
> This doesn't work. Now that I look at my code I can clearly see why. All
> sql queries are executed using a new Postgres connection, hence the use
> of BEGIN/COMMIT as I was using them have no effect.
>
> Can someone recommend a way to abstract out my DB layer while still
> being able to use transactions? (persistent connections are not an
> option since there are too many inherent dangers).
>
> I was thinking of using the same function but if the incoming query
> contained the word BEGIN, saving that and all future queries in a
> session var and when the COMMIT comes in executing all the saved queries
> as one (i.e. "BEGIN;[....];COMMIT"). One drawback is that all queries
> will be written out to disk (as session vars) and cause a performance hit.
>
> I was also thinking about maybe the $sql a GLOBAL or first building up
> my query as as long string ("BEGIN;[....];COMMIT") and *then* sending it
> to my sql_query() function.
>
> The last two seem easier to implement, safer, and more efficient but
> they don't seem "elegant" because I haven't really abstracted out the
> fact that I want a transaction. Whenever I write an SQL query I have to
> think "does this need to be in a transaction" and then use a different
> coding technique depending on the answer. And if the future something
> that didn't need to be in a transaction now needs to be in a transaction
> I have to revisit my code and change the code.
>
> I'm sure someone out there must have thought about this and come up with
> an elegant solution and way of abstracting out the DB layer from PHP.
>
> Can anyone share their solution with me or give me some pointers to
> reference material?
>
> Thanks,
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
At 02:05 PM 1/10/03 +0900, Jean-Christian Imbeault wrote:
>I posted this on the PHP-general list since it is more of a programming
>techniques question that a PostgreSQL question but I thought I would ask
>it here also since it has to do with abstracting out the DB layer and I
>get the feeling more people on this list have faced this kind of issue
>before and have thought about it the issues more in depth.
>
>The problem:
>
>I tough I had abstracted out the SQL querying part of my code out, just to
>find out today that it doesn't work when it comes to transactions. I had
>come up with this code:
>Can someone recommend a way to abstract out my DB layer while still being
>able to use transactions? (persistent connections are not an option since
>there are too many inherent dangers).
I'm assuming your application is a web app. If it is not, you should
provide more info on why you can't or are not using the same connection for
at least an entire "real world" transaction, and what your app actually
does. Also I recommend you put your abstraction layer on top of PHP's
standard DB interface, if you haven't already. Even if it is a web app, why
can't you use the same DB connection within a page serve??
e.g.
rollback;
begin;
do SQL stuff;
do more SQL stuff;
commit/rollback;
Anyway the issues when doing transactions with web apps are:
1) Web stuff is by nature stateless - connect, get page, disconnect.
2) Users could in theory open multiple windows/browser instances, and often do.
3) Users could visit pages out of sequence, in multiple windows too.
4) Caches could get in the way.
A naive attempt would be to somehow link a DB connection to a user.
Then the maximum simultaneous number of web users you can have would be =
"max concurrent DB transactions" (which usually is the same as "max
concurrent DB connections", but not necessarily so [1]). This may be
acceptable for some apps (limited known users).
But what happens if the user opens more than one window and starts using
them independently? The SQL statements will be interleaved and possibly out
of order, causing a mess. So you probably have to force the user to only
use one window, and not to use the browser back/forward/history stuff.
So it seems to me that unless you enforce strict restrictions on what users
can do when browsing, and how many concurrent users you support, it is
impractical to map "real world" transactions to a single DB transaction
over multiple web pages.
If you don't/can't do that, you probably have to resort to a single page
(e.g. Order Confirmation Form) to display and accumulate items to be
committed followed by a page which commits the transaction.
You could have things stateless, loose and free form before that final
pages, but the final page pair are the ones committing the transaction.
HOWEVER: DB transactions are still useful on a per page basis- e.g. use the
same connection for the page, rollback if anything fails. But given the
issues I mentioned it is no surprise MySQL's lack of transactions in the
old days was not a big deal for webapps.
[1] Example "transactions >> connections" feature:
BEGIN; -- Starts transaction,
select currentTID(); -- Returns 123145
SQL stuff;
KEEP TID=123145; -- releases lock on TID, keeps transaction open
-- even if disconnected, and can be continued from other db connections!
BEGIN TID=123145; -- continues prev TID, blocks other BEGINs of same TID
-- Maybe CONTINUE TID=123145 would be better?
More SQL stuff;
KEEP TID=123145; -- releases lock on TID, keeps transaction open,
BEGIN TID=123145;
SQL stuff;
COMMIT TID=123145; -- or rollback
Probably could be done on Postgresql given MVCC. But not that useful for
most apps.
Would it actually be useful to be able to have more transactions than DB
connections? Maybe for DB clusters?
Just imagine the problems - my vacuum isn't cleaning out any rows and there
are no other DB connections at all. A solution looking for many problems :).
Hmmm, does Oracle MTS do stuff like this?
Hope this helps,
Link.
> Can someone recommend a way to abstract out my DB layer while still > being able to use transactions? (persistent connections are not an > option since there are too many inherent dangers). I use a class. The connection handle is stored as a member variable and the function that executes a query is a method, so that every query is processed via the same connection. To avoid instanciating several objects for different querys and having multiple connection to the DB, my class is able to store result handles in an array using names and can switch back and forth between different result handles as needed. You might want to take a look at PEAR::DB too if only for inspiration. Hope this helps. Cheers. -------- Francois Home page: http://www.monpetitcoin.com/ "We waste our time spending money we don't have to buy things we don't need to impress people we don't like"