On 2008-01-18 12:29, James Henstridge wrote: > On 18/01/2008, M.-A. Lemburg <[EMAIL PROTECTED]> wrote: >> On 2008-01-18 09:37, James Henstridge wrote: >>> Hello, >>> >>> I've been looking at implementing two phase commit for the psycopg2 >>> driver for PostgreSQL. It was suggested that I bring up the issue on >>> this list to see if there were any suggestions about what form the API >>> should take. >>> >>> The API from the initial patch I produced stuck pretty close to the >>> PostgreSQL API, adding three methods to the connection object: >>> >>> prepare_transaction(xid) - prepare the transaction, using the given >>> ID. This closes off the transaction, allowing a new one to be started >>> (if needed). >>> >>> commit_prepared(xid) - commit a previously prepared transaction . Must >>> be called outside of a transaction (i.e. no execute() calls since the >>> last commit/rollback). >>> >>> rollback_prepared(xid) - rollback a previously prepared transaction. >>> >>> The idea being that this should be enough to plug psycopg2 into a >>> transaction manager such as Zope's transaction module or similar. >> Zope doesn't require any specific additional APIs to hook >> the database module into its transaction mechanism. While >> you do need a wrapper (the Zope DA), the three methods used >> by the Zope TM easily map onto the standard .commit() and >> rollback() methods of the database interface. > > I already have an understanding of how the Zope transaction manager > works. The point is: > > 1. The database adapter needs to provide some API for use by a Zope > DataManager. This API needs to co-exist with the standard DB-API > transaction handling. > > 2. If the database adapter is going to provide an API used to > implement two-phase commit, does it make sense to standardise such an > API across different database adaptrers? This leads on to the > question I asked in my previous email: > >>> I understand that this API might not be implementable by other >>> database adapters, which brings up the question: what would be a good >>> API? > > >>> >From a quick search, I found two other adapters implementing 2pc both >>> with incompatible APIs: >>> >>> kinterbasdb implements a Connection.prepare() method, which performs >>> the first phase and causes a subsequent commit() or rollback() to >>> complete that transaction. Transaction identifiers are not exposed by >>> the API. >>> >>> pymqi provides a patch to the DCOracle2 adapter. It doesn't seem to >>> add any explicit API to the connection object, but DCOracle2 does have >>> an incompatible prepare() method used for prepared statements. >> pymqi is a wrapper for IBM MQSeries which can act as XA-compliant >> two-phase commit transaction manager (TM). For this to work, the underlying >> database interface has to be compatible to the XA specification, >> which is essentially a C interface used directly by the TM. >> >> Note that XA implements transactions completely outside the >> normal scope of the Python database module, ie. you may not >> call .commit() or .rollback() on the connection objects, but >> instead have to register with the XA TM any action that >> you plan to have as part of a two-phase commit transaction. > > Yep. I am not sure how easy it would be to expose a Python level > two-phase commit API for DCOracle2 -- I just brought it up as an > example of a database adapter that people are using with a transaction > manager (albeit at the C level). > >> BTW, I'm not sure whether you are interpreting the .prepare() correctly: >> this only prepares a statement for later execution, it doesn't >> do the first part of a two-phase commit which would be to save >> the current transaction log and check whether it could potentially >> be committed without problems. > > I guess I was a bit unclear. When I said that DCOracle had an > incompatible Connection.prepare() method, I meant that it is > incompatible with respsect to kinterbasdb's Connection.prepare(). > > Therefore, standardising a prepare() method for use in two-phase > commit would be problematic.
Thanks for the clarification. I was thinking of the cursor.prepare() method. >>> So is there any recommendations for what a two-phase commit API should >>> look like? >> It depends a lot on what you're trying to solve. >> >> In general, you usually have to adjust to an existing >> transaction manager and that then defines the interface >> to use. The are two industry standards for this: XA (X/Open) >> and DTC (MS). > > I realise that tying a database adapter into a transaction manager > will often involve some level of database-specific code. > > I just wonder if there is enough commonality to justify some level of > standardisation. It seems silly for everyone to do things differently > for no good reason. Agreed, but the need for such interfaces only comes up if you plan to implement the transaction manager (TM) itself in Python and need to use the database module as resource manager (RM). I don't know how this would work with DTC (have never used it, but it appears to be similar to XA). With XA, the RM has to provide a C struct defining a set of C function hooks (the XA switch). This is then used by the TM to implement two-phase commits. Now, if a database provides such an XA interface, this could also be made available to a Python TM. You'd then have to open the connection via this XA interface rather than the standard connection constructor (or pass in a parameter to this constructor to make it use the XA open instead of the RM open). Perhaps we could piggy-back the XA-style interface onto the connection interface and its constructor and turn it into an XA DB-API extension ?! XA Spec: http://www.opengroup.org/products/publications/catalog/c193.htm -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Jan 18 2008) >>> Python/Zope Consulting and Support ... http://www.egenix.com/ >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/ ________________________________________________________________________ :::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! :::: eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48 D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg Registered at Amtsgericht Duesseldorf: HRB 46611 _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
