Support SELECT FOR UPDATE OF TABLES

[jackysp]

Feature Request

Is your feature request related to a problem? Please describe:

For a multi-table join, a for update stmt locks all tables, some tables may not need to be locked.

Describe the feature you'd like:

Support the of tables syntax, which specifies that only some of the tables are locked. See https://dev.mysql.com/doc/refman/8.0/en/select.html for more details.

Describe alternatives you've considered:

No.

Teachability, Documentation, Adoption, Migration Strategy:

https://dev.mysql.com/doc/refman/8.0/en/select.html

[jackysp]

PTAL @sunxiaoguang

[jackysp]

PTAL @coocood @cfzjywxk

[tisonkun]

@jackysp thanks for creating this issue.

@jackysp @cfzjywxk do you think we should prepare a design doc for this feature? It seems not a "huge" change but may be a significant change since it changes how locks work for TiDB.

As the pull request doesn't update parser dependency, I assume that this feature will change how SQL currently with "OF tables" works. Also we may want a clear plan for testing and get this feature general access.

What do you think?

[jackysp]

I've updated parser, which didn't support the syntax of tables in the past. Our syntax documentation doesn't provide it either. So, I think this just extends the syntax and functionality. @tisonkun

[jackysp]

I can write a design document for the pr, which is fine. @tisonkun