Re: [PDO] transactions

Lukas Kahwe Smith wrote:
> Hi,
> 
> Just wanted to start a discussion on differences in transaction handling 
> and if there is anything we can (optionally) do inside PDO to make this 
> more portable. For example MySQL allows COMMIT even there were errors 
> during a transaction.

I have been trying to have a look at this because it was one of the areas that 
somewhat lacking in PDO when applied to Firebird. Firebird has a somewhat 
complex transaction capability as a result of it's multi-generational design - 
but simply switching it 'IBASE_COMMITTED' it would look like any other database. 
If PDO is to actually replace all the existing drivers then it needs to be able 
to handle all of the 'special cases' even if these are only available to a 
single database? Transaction management is one area where I have to use 
php_interbase as it can handle the special stuff, and currently I don't see how 
these can be incorporated into PDO specifically with cross connection transactions!

For information ... ( I could not find a simple page to link to ;) )
--------------------------------
ACID - Firebird is fully ACID compliant. All ANSI isolation levels except Dirty 
Read are supported. Dirty Read isolation violates ACID principles and is not 
supported by Firebird.

Locking - In 1984 Firebird ancestor was the world's first database system to use 
multi-version concurrency control, a technology that avoids the use of user 
locks to achieve multi-user concurrency. Locking policy is optimistic and at 
row-level. Except in extreme transaction configurations, readers and writers do 
not block each other. This technology has since been imitated by Oracle in the 
late 80's and by SQLServer, Postgres and InnoDB (MySQL) in their most recent 
releases. Firebird offers an optional row level lock in the SELECT statement, 
for applications that need row locks, using a syntax similar to Oracle's. It is 
also possible to impose a table lock for applications that need such a feature.

Transaction Control. Firebird offers full support for commit, rollback and 
savepoint, including nested savepoints. Additionally, both COMMIT [WITH] RETAIN 
and ROLLBACK [WITH] RETAIN are supported, enabling the resources allocated for a 
transaction to be re-used for repetitive tasks.

Distributed Transactions. Firebird has offered two-phase commit and XA since its 
earliest releases.

Transaction programmability. Developers can select the isolation level on a 
transaction-by-transaction basis. Deadlock detection and resolution is automatic 
(and infrequent, see 'Locking' above). It is possible to specify whether 
deadlock notification is immediate or deferred for a configurable timeout 
period. Most locking conflicts are not deadlocks. Each Firebird transaction can 
be configured according to several parameters to determine when and how 
conflicts are resolved.
--------------------------------

With the current PDO API there is no way of actually passing settings in the 
call to beginTransaction, while the equivalent Firebird/Interbase call allows a 
transaction accross multiple connections 
http://uk3.php.net/manual/en/function.ibase-trans.php

Additionally I think that you have to create another PDO object when you want 
another transaction open in parallel? Rather than just creating an additional 
transaction on the open connection?

-- 
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php