The only question is if the keywords BEGIN and COMMIT are ignored or if they generate an error. In the former case, no problems. In the latter case, we have to either drop support for certain configurations or code around them.
Aaron "John Hansen" <[EMAIL PROTECTED]> said: > I assume here that you talk about MySQL 4. > MySQL 3 does not have transactions. > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > On Behalf Of Chris Nolan > Sent: Sunday, February 15, 2004 11:57 AM > To: dbmail-dev@dbmail.org > Subject: Re: [Dbmail-dev] some speed tests > > > Forgive the bluntness of the statement, but why is anyone even worrying > about transactions as they relate to MySQL??? > > COMMIT in MySQL is passed to the table handler. In the case of MyISAM > tables, the handler disregards the statement. For InnoDB and BDB tables, > > COMMIT acts as it does in PostgreSQL. > > So various people in this thread implying that MySQL isn't really a > database need to do some more reading. > > In summary, just encapsulate everything in transaction blocks and the > underlaying database will act appropriately. > > Regards, > > Chris > > Aaron Stone wrote: > > >I don't even know where to begin in terms of designing the delivery > >chain around transactions. Could we do it as simply as adding > >functions... > > > > void db_begin_transaction(void); > > void db_flush_transaction(void); (or db_commit_...?) > > > >and then calling these functions before and after each major section of > > >database code? For the delivery chain, we could do it inside of > >insert_message(). For dbmail-smtp, this basically means that the > >execution of the whole program is within one transaction. For > >dbmail-lmtpd, it means that each message is delivered within a > >transaction but the miscellaneous queries before the main message > >delivery chain are not transacted. For MySQL, these functions would be > >noops. > > > >Thing that might work? > > > >Aaron > > > > > >Thomas Mueller <[EMAIL PROTECTED]> said: > > > > > > > >>Hi Aaron, > >> > >> > >> > >>>Do you have any way of narrowing this down to specific queries that > >>>are taking the longest and/or are being executed the most? That would > > >>>identify which low-level database functions are being called, then we > > >>>can just trace our way up the call chain to see who's misbehaving or > >>>acting on a flawed design. Also, if you could run similar tests > >>>against the latest 1.2, it would help to give a frame of reference, > >>>particularly for my delivery chain design. > >>> > >>> > >>That's simple: the main design flaw (actually that's no design flaw I > >>think, that's because the so called database MySQL couldn't do > >>transactions in the past) is that dbmail doesn't use transaction. > >> > >>Because of that AutoCommit is used and whenever dbmail does > >>anySqlQuery Postgres does 'BEGIN; anySqlQuery; COMMIT;' - and that is > >>terribly slow. To ensure the Durability in ACID the database has to > >>fflush() every transaction to stable storage! That's why there is only > > >>one solution: we have to use transaction. > >> > >>With transactions we could remove the integrity checks of > >>dbmail-maintenance too, because the database guarantees integrity. > >> > >>Anyway, I did a trace of all SQL queries when a mail is copied using > >>IMAP. I got 35 SELECT, 4 INSERT, 5 UPDATE (44 db operations to insert > >>one mail?). > >> > >>When searching for the sequential scan I found something quite > >>interesting in the docs: the planer decides for every scan if a > >>seqScan is cheaper that an index scan, and does a seqScan even if an > >>index > >>exists: > >> > >>dbmail=> explain SELECT mailbox_idnr FROM mailboxes WHERE > owner_idnr=2; > >> QUERY PLAN > >>--------------------------------------------------------- > >> Seq Scan on mailboxes (cost=0.00..1.06 rows=3 width=8) > >> Filter: (owner_idnr = 2) > >>(2 rows) > >> > >>The table has an index on owner_idnr. > >> > >>So I should repeat this test with a database with several hundred to > >>thousand user, several dozen mailboxes for each user and several dozen > > >>mails in each mailbox to find out if all required indizes are there. > >>Did anyone write a script to create such a database? > >> > >>But I found a strange query: > >>SELECT mailbox_idnr FROM mailboxes WHERE mailbox_idnr = '4' AND > >>owner_idnr = '2' mailbox_idnr is the primary key so that could be > >>optimized to: SELECT 4 > >>;-) > >> > >> > >>I don't have a 1.2 installation, I'm sorry. > >> > >> > >>-- > >>MfG Thomas Mueller - http://www.tmueller.com for pgp key (95702B3B) > >>_______________________________________________ > >>Dbmail-dev mailing list > >>Dbmail-dev@dbmail.org > >>http://twister.fastxs.net/mailman/listinfo/dbmail-dev > >> > >> > >> > > > > > > > > > > > > > _______________________________________________ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > _______________________________________________ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > --
