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; <do what you like with the table> 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]
