Mysql transaction with table locking

Question

I need to use table locking (write) and along with that update a few tables, so I need transactions at the same time, as the locking is not transaction-safe.

From mysql documentation I read the following https://dev.mysql.com/doc/refman/5.6/en/lock-tables-and-transactions.html

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal 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 internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if autocommit = 1, to help old applications avoid unnecessary deadlocks.

On the other hand from this page we have https://dev.mysql.com/doc/refman/5.6/en/commit.html

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

So, if with START TRANSACTION disables the autocommit, then why in table locking section it says that the correct way is to begin a transaction with SET autocommit = 0 (not START TRANSACTION). Am I missing something or there is a contradiction between these two ? Can I use START TRANSACTION with table locking? I am using InnoDB.

Thanks

Answer 1

https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html

• LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.
• Beginning a transaction (for example, with START TRANSACTION) implicitly commits any current transaction and releases existing table locks.