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]
