Mysql - Table lock error

Question

I am experiencing some problems with table locking. I have locked certain number of table in transaction. LOCK TABLES t1 READ, t2 READ, t3 READ, t4 READ,t5 READ,t6 READ,t7 READ;

While reading its ok, but when I am trying to write/update to this tables it's showing the error Table 't1' was locked with a READ lock and can't be updated

But I never locked the table for write, I have to write to these tables with updated values.

My aim is I need to select and I need to update rows, while others should not interact with above tables until the process finished.

Much appreciated your solutions!

Answer 1

From mysql doc:

Rules for Lock Acquisition To acquire table locks within the current session, use the LOCK TABLES statement. The following lock types are available:

READ [LOCAL] lock:

The session that holds the lock can read the table (but not write it).

Answer 2

MariaDB [test]> lock table super1 read;
Query OK, 0 rows affected (0.00 sec)


MariaDB [test]> select * from super1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
|  6 |
|  7 |
+----+
6 rows in set (0.00 sec)

MariaDB [test]> insert into super1 VALUE(10);
ERROR 1099 (HY000): Table 'super1' was locked with a READ lock and can't be updted

other session can SELECT too

MariaDB [test]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> lock table super1 write;
Query OK, 0 rows affected (0.00 sec)

other session can't SELECT

MariaDB [test]> insert into super1 VALUE(10);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into super1 VALUE(11);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> unlock tables;
Query OK, 0 rows affected (0.00 sec)