Sam, I assume you are having the MySQL binlogging on? The following manual excerpt explains why this has to work as it does:
" INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive (non-next-key) lock on each row inserted into T. Normally does the search on S as a consistent read, but sets shared locks on S if the MySQL binlogging is on. InnoDB has to set locks in the latter case because in roll-forward recovery from a backup every SQL statement has to be executed in exactly the same way as it was done originally. " Also note the following bug fix in 4.0.10: " MySQL/InnoDB-4.0.10, February 4, 2003 a.. In INSERT INTO t1 SELECT ... FROM t2 WHERE ... MySQL previously set a table level read lock on t2. This lock is now removed. " We are discussing moving into row level binlogging in MySQL. Then we could drop also the row locks on S. Regards, Heikki Innobase Oy sql query ............... Subject: Bug? InnoDB transactions and temporary table hanging From: Samuel Liddicott Date: Thu, 6 Feb 2003 10:57:13 -0000 I came accross a problem porting our tv listings system from postgres to mysql with InnoDB. With InnoDB tables, read-only transactions started after a read-write transaction touching the same rows are able to read data as it was before the read-write transaction began; UNLESS the "read-only" transactions selects rows touched by the first transaction INTO a temporary table, in which case these transactions wait until the first (read-write) transaction finishes before proceeding. I see why it does this but I don't think the rule should apply to temporary tables which are private to transactions. If a "read-only" transaction writes to temporary tables, surely it is the business of no-one and can't affect or be affected by other transactions; and so it is not neccessary to wait. This behaviour even occurs with temporary heap tables; and with whatever tansaction isolation option is selected. postgres does not suffer from this problem with temporary tables or regular tables. Is this a bug? Or can/must I work round it? The update process can take upwards of 15 minutes and queries-using-temporary-tables must return within seconds. We use transactions so that the updates might be atomic. Sam --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
