MySQL Bugs: #74033: Docs: UNLOCK TABLES and transactions

Bug #74033	Docs: UNLOCK TABLES and transactions
Submitted:	23 Sep 2014 11:47	Modified:	23 Sep 2014 16:13
Reporter:	Federico Razzoli	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This page:

http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

says:

"UNLOCK TABLES commits a transaction only if any tables currently have been locked with LOCK TABLES to acquire nontransactional table locks. A commit does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table-level locks."

Very detailed information, BUT:

http://dev.mysql.com/doc/refman/5.6/en/commit.html

says:

"Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information."

The opposite is also true: LOCK TABLE implicitly commits the current trx.

This means that UNLOCK TABLES cannot commit any transaction, since no table can be explicitly locked while a transaction is active...

Unless I'm still missing something.

How to repeat:
CREATE TABLE t (c INT) ENGINE = MyISAM;

LOCK TABLE t WRITE;

START TRANSACTION;

Now, the table is not locked.

other information just to help a better documentation

table locks with innodb will only work if you explicity
SET innodb_table_locks=1;
SET autocommit=0;

any other configuration will not allow LOCK/UNLOCK over innodb tables to implicity commit transactions

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_table_locks

Actually, I think that current documentation is quite adequate.

UNLOCK TABLES will commit (implicitly) only when tables are locked with LOCK TABLES command. It will not commit in all other cases in order to enable mysqldump to work nicely with  FLUSH TABLES WITH READ LOCK command, which does not do a classical lock.

You can can first lock tables and then start a transaction. Transactional behavior will not work with MyISAM tables, as MyISAM is not transactional SE.

"You can can first lock tables and then start a transaction"

But then, BEGIN will release the table lock, and UNLOCK TABLES will do nothing.

But again, I may be missing something. If you can create a situation where a table is explicitly locked AND a transaction is active, then I'm probably wrong...

What you write about what happens in the beginning of the transaction is quite true. But, as our documentation clearly shows LOCK / UNLOCK TABLES is mostly designed to work with non-transactional SE, like MyISAM and MEMORY.

As our documentation shows, you can choose whether you want for InnoDB to be affected by LOCK TABLES or not. If yes, and if autocommit is off, then InnoDB will simply wait in LOCK TABLES until other connections have released the lock(s). After that , when transaction starts, locking tables with this command does not have any effect on InnoDB.

As our documentation shows, LOCK / UNLOCK are SQL-level locks which InnoDB can ignore (which is best option in my opinion), while the operation of InnoDB is ruled by InnoDB-level locks, which have nothing in common with LOCK / UNLOCK commands.

The fact is that I am not able to have a table lock (created with LOCK TABLES) and a transaction (started with BEGIN) at the same time. It seems that, when I do one of these things, the other one dies (the transaction commits or the lock is released).

This is probably a pity, because MEMORY is not transactional and it is useful (I don't really care about MyISAM). But well, I suppose I can switch to InnoDB, or simply avoid the LOCK TABLES.

What I'm trying to report is that the statement I quoted above is confusing. I makes me think that:

* UNLOCK TABLES can commit a transaction;

* It is possible for the same connection to 1) have a running transaction 2) hold a table lock - at the same time.

However, I wasn't able to create such situation. If it is impossible, then the documentation is a bit confusing - at least, it confused me :)

Federico,

Yes it is a bit confusing. This is because MySQL server is a very complex product and you usually can not find the answer or one even two pages in our manual. The situation is similar with most other SQL servers.

What makes MySQL a little bit more complicated is a choice of several SE, some of which are transactional and some of which do not have any support for transactions or ACID.

Doing LOCK / UNLOCK tables with transactions is a complex affair. That is why we have a command FLUSH TABLES WITH READ LOCK. You should however read carefully how this command affects both transactional and non-transactional SE. It is mostly used for dumping tables of all SE, for backup purposes.