Heikki Tuuri wrote:
> 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
Director of Business Intelligence Services