why there are so many kinds of "table lock" related syntax?

[tiancaiamao]

General Question

I want to lock the table to try the @@tidb_enable_point_get_cache, but misuse the syntax.
I used alter table t read only ... it doesn't work as expected.

Then I take a glance at the parser.y file, and found we have soooooooooooooo many related syntax:

alter table t lock none
alter table t lock = none
alter table t lock = default
alter table t lock = shared
alter table t lock = exclusive

alter table t read only
alter table t read write

lock table t read
lock table t read local
lock table t write
lock table t write local

unlock table
admin cleanup table lock XX

alter table t cache
alter table t nocache

Each of the above statement is legal, no grammar check error.
But what does they mean? why sooooooooooooooooo many different syntax?
As a developer, I'm quite confused (not to say the real user).

[tiancaiamao]

@crazycs520 any idea?

[tiancaiamao]

alter table t cache
alter table t nocache

Is what I added for #25293, it's non-standard MySQL syntax and the feature is not a part of MySQL.

[tiancaiamao]

lock table t read
lock table t read local
lock table t write
lock table t write local

lock table seems to be also TiDB extension, it's used to make a table read only in one server (local keyword),
or in the whole cluster scope.
It seems this is not GA feature? I can't find the official document for it https://docs.pingcap.com/tidb/stable

[zimulala]

I think some of the following are MySQL syntax:

alter table t lock none
alter table t lock = none
alter table t lock = default
alter table t lock = shared
alter table t lock = exclusive

Related link: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#:~:text=LOCK%20%5B%3D%5D%20%7BDEFAULT%20%7C%20NONE%20%7C%20SHARED%20%7C%20EXCLUSIVE%7D

[CbcWestwolf]

It seems this is not GA feature? I can't find the official document for it https://docs.pingcap.com/tidb/stable

It is an experimental feature in TiDB but a formal feature in MySQL.

tidb> lock table t read;
Query OK, 0 rows affected, 1 warning (0.00 sec)

tidb> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                               |
+---------+------+-----------------------------------------------------------------------------------------------------------------------+
| Warning | 1235 | LOCK TABLES is not supported. To enable this experimental feature, set 'enable-table-lock' in the configuration file. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[crazycs520]

the following syntax are also MySQL syntax:

lock table t read
lock table t read local
lock table t write
lock table t write local

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

But TiDB add 2 special lock type: read local, write local, which use for tispark.

[tiancaiamao]

OK... ALTER TABLE t LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE} is standard MySQL syntax.
It's a dummy operation in TiDB.

and UNLOCK TABLE UNLOCK TABLES are also MySQL syntax.
UNLOCK TABLES clean the table locks of the current session...
UNLOCK TABLE ... I don't know what's it for.

alter table t read only
alter table t read write

They are seems to be bug, MySQL don't have such syntax and it's introduced in pingcap/parser#1095,
it should be replaced by the lock table t read only syntax.

[mjonss]

LOCK TABLE t {read|write}/UNLOCK TABLE[S] is really old MySQL syntax developed for MyISAM tables, which lacked transactions. It locked the full table for that session/connection until a new transaction/ddl or UNLOCK TABLES. It is not supported by TiDB. The local keyword is probably TiDB enhancements as mentioned above.
UNLOCK TABLE has the same meaning as UNLOCK TABLES in MySQL.

ALTER TABLE t ... LOCK = ... was added in MySQL while starting to support ONLINE SCHEMA CHANGES. Later ALGORITHM was also added, so one could limit DDL to INSTANT changes, aka Meta data only changes, just like TiDBs ddl without reorg.
The ALTER TALBE t ... LOCK = ... where SHARED means block changes but allow SELECT and EXCLUSIVE means block all other sessions access to the table during the DDL. This could be used as a way to avoid duplicate key or NULL value issues from other sessions during DDL.

[jebter]

The issue has not been updated for too long, so I will close it，if there are any updates, you can reopen it