LOCK TABLES table WRITE blocks my readings

Question

I am at the REPEATABLE-READ level.

Why does it make me wait?

I understand that all reads (SELECTs) at any level are non-blocking.

what am I missing?

Session 1:

mysql> lock tables users write;
Query OK, 0 rows affected (0.00 sec)

Session 2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where id = 1; // wait

Session 1:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Session 2:

mysql> select * from users where id = 1;
+----+-----------------+--------------------+------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+------------+
| id | name            | email              | rol  | email_verified_at   | password                                                     | remember_token | created_at          | updated_at          | deleted_at |
+----+-----------------+--------------------+------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+------------+
|  1 | Bella Lueilwitz | orlo19@example.com | NULL | 2022-08-01 17:22:29 | $2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi | MvMlaX9TQj     | 2022-08-01 17:22:29 | 2022-08-01 17:22:29 | NULL       |
+----+-----------------+--------------------+------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+------------+
1 row in set (10.51 sec)

In this question the opposite is true

Why doesn't LOCK TABLES [table] WRITE prevent table reads?

Answer 1

You reference a question about MySQL 5.0 posted in 2013. The answer from that time suggests that the client was allowed to get a result that had been cached in the query cache. Since then, MySQL 5.6 and 5.7 disabled the query cache by default, and MySQL 8.0 removed the feature altogether. This is a good thing.

The documentation says:

WRITE lock:

• Only the session that holds the lock can access the table. No other session can access it until the lock is released.

This was true in the MySQL 5.0 days too, but the query cache allowed some clients to get around it. But I guess it wasn't reliable even then, because if the client ran a query that happened not to be cached, I suppose it would revert to the documented behavior. Anyway, it's moot, because all currently supported versions of MySQL should have the query cache disabled or removed.