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]
