Unable to apply read or write lock manually into Innodb table via mysql command line

Question

I am trying to test a scenario in table locked cases.

I tried to apply read and write locks like these -

mysql> lock tables table_name write;
Query OK, 0 rows affected (0.01 sec)

But was able to write after this -

mysql> insert into table_name (id) values (1000000023);
Query OK, 1 row affected (0.00 sec)

Read lock is also not working. I was able to do a select query successfully after applying lock.

I need to test my application behaviour (restful APIs) when we do a table migration of large amount of data, when there may be write locks applied on the table. So, I guess, if I am able to set the lock via command line, it will apply to my APIs as well. I am using the same user to login to command line as I am using in my application to login to mysql.

Please let me know if I am missing something.

The table is InnoDb type.

Answer 1

I think you are misunderstanding table locking. According to the documentation:

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

You are basically locking other sessions out of writing to the table, but YOU can still write to it while you hold the lock.

For what you are doing, there is an example in that documentation page:

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.