Chris, ----- Original Message ----- From: "Chris Nolan" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, September 05, 2003 4:31 AM Subject: Lock escallation etc?
> Hi all! > > Here's a question for all my fellow geeks / coders / software engineers > / curious DB admins. > > I've been doing a lot of reading lately, and have noted a few things: > > 1. Out of the "big three" commercial databases, only Oracle seems to > support nested transactions. Is there any practical purpose for such > things now that InnoDB has partial rollbacks? If there is a purpose for > such a construct, would implementing it hurt InnoDB's performance? I'm > not asking for the feature at all, I'm simply curious. I believe transaction savepoints, which are supported by InnoDB, DB2, SQL Server, and Oracle, can do most of the things which nested transactions can. Simply establish a savepoint at the start of your subtransaction. I was not aware that Oracle has also a nested transaction feature, I thought they only have savepoints. > 2. Reading up on MS SQL Server, the designers at MS seem to attribute > the speed of the product to three major factors: > > * Their pool-of-threads architecture > * The fact that tables are locked as much as they need to be and no > more, with automatic lock escallation as required > (Database->Table->Page->Row) In InnoDB and Oracle, big SELECTs are normally run as non-locking consistent reads. Then, of course, there is no need for lock escalation. DB2 and SQL Server are not multiversioned databases, like Oracle and InnoDB. To get consistent reports from tables you must in DB2 and SQL Server lock the data you read, and they will escalate the locks to page or table level when a threshold in the number of locks is reached. InnoDB's row locks are stored in a bitmap. They fit in very small space. That is why lock escalation in InnoDB is not needed even for locking reads, or UPDATE or DELETE. Lock escalation would also save some CPU time, but I think it would be much less than 10 % in a typical application. > * Optimistic Conflict Control > > Obviously, the first of these is coming to MySQL eventually as listed in > the TODO pages. Would adding the second point to MyISAM be useful at > all, given the fact that we already have INSERT DELAYED? Would adding > the second point to InnoDB speed it up, slow it down or would the > benefits and pitfalls basically cancel each other out (Considering how > fast InnoDB already is, I've a feeling that this is not something that > would help performance)? As for Optimistic Conflict Control, this > wouldn't apply to MyISAM, would it? Seeing InnoDB include it would be > interesting, as Microsoft themselves have been a bit vauge as to the > integrity implications of this feature. Application programmers seem to prefer pessimistic locking methods. In an optimistic method you cannot avoid transaction rollbacks due to serialization conflicts. If you use pessimistic locks and design your transactions carefully, then you can ensure no deadlocks occur, and you will not get any unexpected rollbacks. > Please note: None of the above are feature requests! This is just one > guy's curiosity getting the better of him. > > Regards, > > Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
