Re: PostgreSQL/PHP: transactions: how-to abstract
От | Lincoln Yeoh |
---|---|
Тема | Re: PostgreSQL/PHP: transactions: how-to abstract |
Дата | |
Msg-id | 5.1.0.14.1.20030110150647.027d6db0@mbox.jaring.my обсуждение исходный текст |
Ответ на | PostgreSQL/PHP: transactions: how-to abstract out? (Jean-Christian Imbeault <jc@mega-bucks.co.jp>) |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления:
Следующее
От: Francois SuterДата:
Сообщение: Re: PostgreSQL/PHP: transactions: how-to abstract out?