Re: MySQL/InnoDB-4.1.9 is released
Nick, - Original Message - From: "Nick Arnett" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, January 14, 2005 9:28 PM Subject: Re: MySQL/InnoDB-4.1.9 is released Heikki Tuuri wrote: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html " The correct way to use LOCK TABLES with transactional tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks will very easily happen. Starting from 4.1.9, we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1. That helps old applications to avoid unnecessary deadlocks. " LOCK TABLES when done on an InnoDB table first acquires an InnoDB table lock, and then the MySQL table lock. But when AUTOCOMMIT=1, the InnoDB lock is released immediately. This caused lots of deadlocks with LOCK TABLES. The fix is that in the AUTOCOMMIT=1 mode we do not acquire the InnoDB lock at all. It does not make sense to get a lock and then release it immediately. That's what I was just reading! So... is this the equivalent of using BEGIN and COMMIT, for which I have methods in the Python MySQLdb module? Or is there an advantage to the latter? the BEGIN; .. COMMIT; method does not work here because LOCK TABLES (and UNLOCK TABLES) does an implicit commit of the transaction. Also BEGIN does an implicit commit. As a sidenote, we are working on getting the industry-standard table locking syntax and semantics to MySQL/InnoDB. With the syntaxes LOCK TABLE innodbtable IN SHARE MODE; and LOCK TABLE innodbtable IN EXCLUSIVE MODE; you would get InnoDB table locking similar to DB2 and Oracle. No UNLOCK TABLES would be needed, the next commit would release the table lock. I hope Monty will approve this syntax to 5.0. Thanks again, Nick Arnett Director of Business Intelligence Services Liveworld Inc. Best regards, Heikki http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Heikki Tuuri wrote: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html " The correct way to use LOCK TABLES with transactional tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks will very easily happen. Starting from 4.1.9, we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1. That helps old applications to avoid unnecessary deadlocks. " LOCK TABLES when done on an InnoDB table first acquires an InnoDB table lock, and then the MySQL table lock. But when AUTOCOMMIT=1, the InnoDB lock is released immediately. This caused lots of deadlocks with LOCK TABLES. The fix is that in the AUTOCOMMIT=1 mode we do not acquire the InnoDB lock at all. It does not make sense to get a lock and then release it immediately. That's what I was just reading! So... is this the equivalent of using BEGIN and COMMIT, for which I have methods in the Python MySQLdb module? Or is there an advantage to the latter? Thanks again, Nick Arnett Director of Business Intelligence Services Liveworld Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Nick, - Original Message - From: "Nick Arnett" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, January 14, 2005 7:39 PM Subject: Re: MySQL/InnoDB-4.1.9 is released Heikki Tuuri wrote: * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused very easily deadlocks. Could you explain a bit more about how this relates to MyISAM? Is it just that using LOCK TABLES with InnoDB was causing a lot of deadlocks? http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html " The correct way to use LOCK TABLES with transactional tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks will very easily happen. Starting from 4.1.9, we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1. That helps old applications to avoid unnecessary deadlocks. " LOCK TABLES when done on an InnoDB table first acquires an InnoDB table lock, and then the MySQL table lock. But when AUTOCOMMIT=1, the InnoDB lock is released immediately. This caused lots of deadlocks with LOCK TABLES. The fix is that in the AUTOCOMMIT=1 mode we do not acquire the InnoDB lock at all. It does not make sense to get a lock and then release it immediately. If so, that would explain what I've been seeing in MySQL 4.0.21 (lots of deadlocks on a very small table that I use for managing parallel processes). Until we upgrade (which will be soon, I think), is it best to turn AUTOCOMMIT off and COMMIT when appropriate? The proper way to use LOCK TABLES with InnoDB tables (or a mixture of MyISAM tables and InnoDB tables) is with SET AUTOCOMMIT=0. Then do like this: LOCK TABLES innodbtable WRITE; COMMIT;#releases the InnoDB table lock UNLOCK TABLES;#releases the MySQL table lock Thanks! Nick Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Heikki Tuuri wrote: * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused very easily deadlocks. Could you explain a bit more about how this relates to MyISAM? Is it just that using LOCK TABLES with InnoDB was causing a lot of deadlocks? If so, that would explain what I've been seeing in MySQL 4.0.21 (lots of deadlocks on a very small table that I use for managing parallel processes). Until we upgrade (which will be soon, I think), is it best to turn AUTOCOMMIT off and COMMIT when appropriate? Thanks! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.9 is released
Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 4.1.9 is mainly a bugfix release. This release fixes the CRITICAL BUG #7496 in the innodb_file_per_table option of my.cnf. Secondary indexes of a table could get corrupt at a mysqld shutdown. I recommend that all users of InnoDB with that my.cnf option immediately upgrade to MySQL-4.1.9! OS X 10.3 users should also consider upgrading to 4.1.9. Apple disabled fsync() for internal disk drives, and replaced it with a special fcntl() file flush method. A power outage can easily lead to database corruption in OS X 10.3 with older MySQL versions. Except of critical bug #7496, 4.1.8 has been a very good release, and if we do not discover any serious bugs in 4.1.9 in the next few weeks, I can recommend production use of 4.1.9. Functionality added or changed: * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused very easily deadlocks. * Print a more descriptive error and refuse to start InnoDB if the size of ibdata files is smaller than what is stored in the tablespace header; innodb_force_recovery overrides this. Bugs fixed: * Fixed the critical bug if you enabled innodb_file_per_table in my.cnf. If you shut down mysqld, records could disappear from the secondary indexes of a table. (Bug #7496) * Fixed a bug: 32-bit mysqld binaries built on HP-UX-11 did not work with InnoDB files greater than 2 GB in size. (Bug #6189) * Return a sensible error code from DISCARD TABLESPACE if it fails because the table is referenced by a FOREIGN KEY. * Fixed a bug: InnoDB failed to drop a table in the background drop queue if the table was referenced by a FOREIGN KEY constraint. * Fixed a bug: if we dropped a table where an INSERT was waiting for a lock to check a FOREIGN KEY constraint, then an assertion would fail in lock_reset_all_on_table(). * Fix a little bug: we looked at the physical size of a stored SQL NULL value from a wrong field in the index; this has probably caused no bugs visible to the user, only caused some extra space usage in some rare cases. * Use the fcntl() file flush method on OS X versions >= 10.3. Apple had disabled fsync() in OS X for internal disk drives, which caused corruption at power outages. Upgrading to 4.1.9: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters < ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions <= 4.1.2, then you have to rebuild those tables after you upgrade to >= 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes >= 128) in database names, table names, constraint names, or column names in versions < 4.1, you cannot upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
