[Zope] MySQL transaction support (was: MySQL Select Statements)

Andy Dustman adustman@comstar.net
Mon, 3 Jul 2000 18:47:07 -0400 (EDT)


On Tue, 4 Jul 2000, Hannu Krosing wrote:

> AFAIK the transaction support is enabled on table-by-table basis, so
> there 
> is no way to know for sure if transactions are 
> supported/partially-supported/unsupported for a particular set of
> queries

True enough. There are several problems here:

1) We must avoid using BEGIN/COMMIT/ROLLBACK on servers < 3.23.15 because
it will result in syntax errors.

2) We must raise exceptions whenever the DA attempts to use transacation
semantics and the database cannot fulfill this. Silently allowing the
transaction manager to believe ROLLBACK has succeeded when it is actually
doing nothing is a bad thing.

3) Even though the server may support transactions, the tables may not.
Calling ROLLBACK hopefully produces an error, which is a good thing.

4) Calling ROLLBACK in AUTOCOMMIT mode probably does not produce an error.
This is bad, but easily avoided by using BEGIN to force a transaction.
Does this cause MySQL to produce an error if the tables don't support it?
I don't know. Probably not.

IMHO, the only good way to avoid these problems is to effectively have two
DAs: A transactional one and a non-transactional one, perhaps determined
by a Zope property. The application designer will have to choose which DA
to use for a given application.

Note also: With non-transactional databases, you would expect to get
better performance by having several open connections which are not
shared. In the case of MySQL, each connection has it's own server thread,
and this can exploit CPUs, if available. However, connections should not
be shared on transactional databases, because transactions are tied to a
connection, and not a cursor. Even if there is a mutex to keep multiple
threads from executing queries simultaneously on the same connection, the
transactions are going to be whacky.

-- 
andy dustman       |     programmer/analyst     |      comstar.net, inc.
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage, 
come no further, for death awaits you all, with nasty, big, pointy teeth!"