Re: [Zope] MySQL transaction support (was: MySQL Select Statements)
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 7/4/00, 12:47:07 AM, Andy Dustman <[EMAIL PROTECTED]> wrote regarding Re: [Zope] MySQL transaction support (was: MySQL Select Statements): > 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. Oracle is obviously transactional. Does anyone know how it handles connection pooling? I'll look and see it I can figure it out, but I'd love it if someone already knew... Monty ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] MySQL transaction support (was: MySQL Select Statements)
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!" ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] MySQL transaction support (was: MySQL Select Statements)
Andy Dustman wrote: > > There are two factors which determine whether or not transactions can be > supported. Actually, there's only one, but it can't be directly tested > for: > > 1) If the server version (easily obtained upon establishing the > connection) is < 3.23.15, transactions are definitely not supported. > > 2) If the database in use employs BDB tables, transactions are definitely > supported. 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 > The problem is: Support for the transaction manager is done with a MixIN > class, so the decision on whether or not transactions should be supported > in the DA comes before the connection object is created. But after the connection is established you could probably change the mixin class, at least if it is really a proxy wrapper of some other class. Python is _very_ dynamic language. Hannu ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] MySQL transaction support (was: MySQL Select Statements)
On Mon, 3 Jul 2000, Monty Taylor wrote: > Why not have one ZmySQLDA that attempts to determine whether it is > transactional or not when the connect method is executed. That way you > could have a DA connect to your 3.22 database, upgrade your database to > >3.23.15 and not see any problems. A combination of try-except:ing an > attempted "begin rollback" block and a test for database type should do > the trick, no? If it's 3.22 or earlier, the begin-rollback should throw > the synax error Andy mentioned. If that's ok, a test for database type > (not sure about this one) to see if it's BDB? If all of this is wrapped > in the connect method, we should be able to make it 'do the right thing' There are two factors which determine whether or not transactions can be supported. Actually, there's only one, but it can't be directly tested for: 1) If the server version (easily obtained upon establishing the connection) is < 3.23.15, transactions are definitely not supported. 2) If the database in use employs BDB tables, transactions are definitely supported. The problem is: Support for the transaction manager is done with a MixIN class, so the decision on whether or not transactions should be supported in the DA comes before the connection object is created. Which raises an interesting possibility: On the connection object's Properties tab, add a checkbox which indicates that transactional semantics are required. The status of this checkbox determines which class is used for the database connection. This might be workable, not really sure. -- 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!" ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] MySQL transaction support (was: MySQL Select Statements)
Why not have one ZmySQLDA that attempts to determine whether it is transactional or not when the connect method is executed. That way you could have a DA connect to your 3.22 database, upgrade your database to >3.23.15 and not see any problems. A combination of try-except:ing an attempted "begin rollback" block and a test for database type should do the trick, no? If it's 3.22 or earlier, the begin-rollback should throw the synax error Andy mentioned. If that's ok, a test for database type (not sure about this one) to see if it's BDB? If all of this is wrapped in the connect method, we should be able to make it 'do the right thing' Thoughts? Monty > > with BDB tables. But it may be possible to break things down as having a > > DA for 3.22 and one for 3.23. Which is, in practice, may not be any > > different than having one that is not transactional (3.22 never is) and > > one that is transactional (3.23 sometimes is). > What I want to avoid is having to create two different "MySQL database > connections" where I have to choose the "correct" one based on whether or > not my SQL method is using transactions or not. I'll most likely only be > using one version of MySQL at a time, but if I'm using transactions at all, > there's a good chance not all of my tables will support them. > I guess we'll just have to do some testing. > ___ > Ron Bickers > Logic Etc, Inc. > [EMAIL PROTECTED] > ___ > Zope maillist - [EMAIL PROTECTED] > http://lists.zope.org/mailman/listinfo/zope > ** No cross posts or HTML encoding! ** > (Related lists - > http://lists.zope.org/mailman/listinfo/zope-announce > http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] MySQL transaction support (was: MySQL Select Statements)
> -Original Message- > From: Andy Dustman [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 30, 2000 4:08 PM > To: Ron Bickers > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: [Zope] MySQL transaction support (was: MySQL Select > Statements) > with BDB tables. But it may be possible to break things down as having a > DA for 3.22 and one for 3.23. Which is, in practice, may not be any > different than having one that is not transactional (3.22 never is) and > one that is transactional (3.23 sometimes is). What I want to avoid is having to create two different "MySQL database connections" where I have to choose the "correct" one based on whether or not my SQL method is using transactions or not. I'll most likely only be using one version of MySQL at a time, but if I'm using transactions at all, there's a good chance not all of my tables will support them. I guess we'll just have to do some testing. ___ Ron Bickers Logic Etc, Inc. [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] MySQL transaction support (was: MySQL Select Statements)
On Fri, 30 Jun 2000, Ron Bickers wrote: > > That's the problem: They don't get control over when BEGIN/COMMIT/ROLLBACK > > are used. Zope does that if you are using the Transaction Manager. And the > > only way to control use of the Transaction Manager is by > > selection of DA. > > > > That's why I think it may be necessary to have two ZMySQLDAs: One which is > > transactional and one which is not. However, they can both be part of the > > same distribution, and the transactional one can simply subclass the > > non-transactional one (mixing in the TM base and adding the required > > methods), so there is not a lot of code duplication. > > I see. So how would this affect use of transaction and non-transaction > supporting tables? Would two separate database connections be required > where the SQLMethod must use the correct connection depending on transaction > support? > > Making assumptions is bad, but suppose MySQL 3.23 does something elegant > with BEGIN/COMMIT/ROLLBACK attempts on non-transaction supporting tables > (like return a "table does not support transactions" or simply ignores that > statement). Could we then just have one DA for 3.22 and another for 3.23, > where the 3.23 DA handles transactions, but silently ignores them when > dealing with non-transaction tables? These are good questions. But I don't have good answers yet. I tried BEGIN and COMMIT when I didn't even have a database selected, and it took it. It may silently accept ROLLBACK when rollback is impossible, or it may raise an error. I haven't done that much testing with MySQL-3.23 yet, and none with BDB tables. But it may be possible to break things down as having a DA for 3.22 and one for 3.23. Which is, in practice, may not be any different than having one that is not transactional (3.22 never is) and one that is transactional (3.23 sometimes is). -- 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!" ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] MySQL transaction support (was: MySQL Select Statements)
> -Original Message- > From: Andy Dustman [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 30, 2000 1:25 PM > To: Ron Bickers > Cc: Michael Blewett; [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: [Zope] MySQL Select Statements > That's the problem: They don't get control over when BEGIN/COMMIT/ROLLBACK > are used. Zope does that if you are using the Transaction Manager. And the > only way to control use of the Transaction Manager is by > selection of DA. > > That's why I think it may be necessary to have two ZMySQLDAs: One which is > transactional and one which is not. However, they can both be part of the > same distribution, and the transactional one can simply subclass the > non-transactional one (mixing in the TM base and adding the required > methods), so there is not a lot of code duplication. I see. So how would this affect use of transaction and non-transaction supporting tables? Would two separate database connections be required where the SQLMethod must use the correct connection depending on transaction support? Making assumptions is bad, but suppose MySQL 3.23 does something elegant with BEGIN/COMMIT/ROLLBACK attempts on non-transaction supporting tables (like return a "table does not support transactions" or simply ignores that statement). Could we then just have one DA for 3.22 and another for 3.23, where the 3.23 DA handles transactions, but silently ignores them when dealing with non-transaction tables? ___ Ron Bickers Logic Etc, Inc. [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
