On Sun, 2004-02-15 at 20:36, Thomas Mueller wrote: > Hi Chris, > > > > > 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 then we have THREE different configurations to consider, and need to be > > > sure to design the mid-level interface appropriately. > > > > My statement was meant to say that the MyISAM table handler will just > > disregard BEGIN and COMMIT statements, just as it parses but ignores > > CHECK and FOREIGN KEY constraints in table creation, thus illustrating > > that you wouldn't need to add a mysql-with-transactions directory to the > > source tree. > > But don't we have to differentiate between a database with and a > database without transactions? > If we have transactions we can simply do all the database stuff, if > anything fails we do the rollback and bail out. We still have a > perfectly consistent database. > Without transactions we have to remember every successful database > modification we made and have to revert that.
That would be very difficult in the case of UPDATEs though. Additionally, with MyISAM tables there is a problem with race hazards. For example: SELECT * FROM table WHERE id = 3; UPDATE table SET attr = 'thing' WHERE id = 3; // Error occurs UPDATE table SET attr = $oldvalue WHERE id = 3; In the above statement, you have no way of ensuring that no one else has buggered around with the row we're interested in. As DBMail's queries seem to be primarily INSERTs and SELECTs though, this isn't as big a problem as using LOCK TABLE statements would not result in a noticable performance hit. > > That would complicate everything a lot. Is it possible to drop > MySQL-without-transactions support? > > > I was referring to someone who said in the body of their message posted > > to another branch of this thread "the supposed database mysql". > > Considering MySQL has proven itself time and time again in terms of > > reliability, licencing flexibility and performance, such statements are > > baseless regardless of what arguments you make for which features. > > That was me, I'm sorry! I didn't want to start a MySQL bashing thread. I > had lot of problems with MySQL in the past, that's why I said that. In my > eyes a database should support some basic things to have the right to > carry the name SQL database. Among these are transactions, foreign key > constraints, stored procedures, trigger, subselects. MySQL had not even > one of these features (today they probably have most of these features) > leading to poorly designed software, where the application had to do lot > of work the database should do (a good example in dbmail are the quotas > - the database could and should calculate that on its own). > The case insensitiveness of MySQL is another point where I had lot of > problems in the past. I only know two case insensitive databases: MySQL > and MS Access. Every other database is case sensitive (Postgres, Oracle, > MS SQL, what-ever). > And one should never forget the dual license of MySQL. > On the other hand people tell me that MySQL is faster than Postgres - > that's simply wrong. It's only faster if you use the database type with > less features. There are tons of threads on the postgresql list where > these two databases (and others) are compared. > That's why my heart beats faster when I hear the name MySQL - I'm sorry, > I'll try to suppress these sidekicks. > The reason I react to statements against my favourite database, MySQL, is simply due to the things I have done with it and things I have seen others do with it. Today, MySQL does have transactions. foreign key constraints and subselect and stored procedure functionality are in beta. MySQL's case insensitivity is a result of having to compensate for Windows' terrible deficencies in some areas and a design choice in others (which is actually a pretty stupid design choice if you ask me - case-sensitive string comparisons are always faster). Regarding the comparisons you speak of, could you point me to some of them? I'm yet to see a recent, decent comparison between the two. I'd love to see PostgreSQL 7.4.1 vs MySQL 4.0.17 / 4.1.1 on Linux 2.6.x or FreeBSD 5.2.x . I find your comment regarding the dual licence very interesting. If you'd like to continue this (hopefully friendly) discussion, please write to me off the list. :-) > > > Is the entire point of your comment that we can safely use > > > > > > BEGIN; > > > > > > .. whatever dbmail does... > > > > > > COMMIT; > > > > > > regardless of whether or not we know if the host database actually > > > supports > > > these keywords? You could have just said that. > [..] > > But yes, you have perfectly summarised the point of my comment. > > As a quick speed up that's a good solution yes. But if it's the right > permanent solution? Personally, I think it is. MyISAM in MySQL is a great table type for lots and lots of INSERT statements OR lots and lots of SELECT statements but not both (quoting Jeremy Zawdony from Yahoo!). Anyone serious about performance in a case like DBMail would probably use InnoDB anyway. The fact that it is now part of the standard MySQL distro gives little reason to not use MyISAM tables except for their ability to perform FULLTEXT searches. Regards, Chris
