Chris, ----- Original Message ----- From: "Chris Nolan" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, December 13, 2003 7:54 AM Subject: "Planned" transactions?
> Hi again all, > > Given that a transaction looks like this: > > BEGIN; > SELECT useless_field FROM useless_table WHERE useless_identifier = > 'useless'; > SELECT useless_field FROM useless_table WHERE useless_identifier = > 'something else'; > INSERT INTO useless_table (useless_field, useless_identifier) VALUES > ('what?','huh?'); > COMMIT; > > Assuming the isolation level is either READ_REPEATABLE or SERIALIZABLE, > would there be any possible benefit to gain from taking the statements > that make up the entire transaction, working out what tables and columns > will be touched and then coming up with some execution policy? I take it > that at the moment, InnoDB's rollback segments grow in a fashion that is > basically a "backward looking" approach of what I've described - am I > correct? I do not fully understand what you mean by 'planned' and by 'backward looking'. If your isolation level is SERIALIZABLE, then those SELECTs are actually executed with LOCK IN SHARE MODE, and they do not conceptually look at the undo logs in the 'rollback segment' at all, only at the row locks set by other transactions. If the SELECTs are 'consistent non-locking reads' (the default in InnoDB), then the first SELECT sets the snapshot timepoint of the transaction. Purge cannot remove history which is after that timepoint, until the transaction commits. It does not matter what tables and what rows the SELECTs look at, the only important thing is the timepoint of the first consistent read SELECT in the transaction. > Regards, > > Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]