WL#14087: Avoid locking reads from ACL tables

The ACL tables (e.g. mysql.user and others) can be made
read-accessible to user by granting them SELECT privilege by admin.
This means that ACL table can be used for reading by commands:
  SELECT
  INSERT
  REPLACE
  UPDATE
  DELETE
  INSERT ... SELECT 
  INSERT  ... SELECT
  UPDATE|DELETE WHERE (SELECT ...)
  multi-UPDATE and multi-DELETE Statements.
  CREATE ... SELECT 
  LOAD DATA ... SET = 
  LOAD XML ... SET = 

Reading of ACL table done by above statements can cause InnoDB
row-level locks being acquired (depending on isolation mode and
binary logging mode). This means that concurrent ACL DDL will be
blocked, which is an issue, since such non-admin users can block
ACL DLL. Revoking SELECT on ACL tables for such users might be
not an option. For example, in MySQL Cloud user's admin account
(which is separate from MySQL Cloud ociadmin@ account) needs this
SELECT privilege to be able to dump/export ACL information from
the the cloud. Still this user's account should not be able to
interfere with ACL DDL done by MySQL Cloud's ociadmin@.

This WL aims to do non-locking reads on ACL table for few commands.  i.e.,
if SERIALIZABLE isolation level is in use, we should silently ignore
it and continue doing non-locking reads on ACL tables for some of
above commands.

Note:  -> Refers to tables listed in WL#14084 HLS
                      Definition 1. ACL Tables.

FR1: SELECT statements including SELECT ... FOR SHARE, but excluding
     SELECT ... FOR UPDATE on ACL tables should not acquire InnoDB
     row-locks in any isolation mode. This means that they should allow
     concurrent updates to ACL tables by DML or by ACL DDL (after
     WL#14084 is implemented).

FR2: DML statements that read data from ACL tables but do not update
     them (e.g. by using subquery on ACL tables in WHERE clause) should
     not acquire InnoDB row-locks in any isolation mode. This means that
     the should allow concurrent updates to ACL tables by DML or by ACL DDL
     (after WL#14084 is implemented).

FR3: Issue warning when DML reads ACL table in serializable isolation
      mode, stating that SE row locks are not acquired.

FR4: DML that reads ACL tables which is written to binary log in statement
     mode would generate a warning, stating that SE row locks are not acquired
     and operation is not safe for statement logging.

     Also we need to ensure that in --binlog-format=mixed mode DML statements
     which read ACL tables are written to binary log using row events, and thus
     their logging is safe in mixed mode.

FR5: Issue a warning when SELECT ... FOR SHARE reads ACL Tables in
     serializable isolation mode, stating that SE row locks are not
     acquired.

A. INTRODUCTION:

  As mentioned in HLD, a user reading  in a concurrent
  connection may cause ACL DDL and DML's to be blocked.

  The high-level idea of this WL is to implement non-locking reads on
   when it is appropriate.

  Simplifying a bit, we can say that reads from ACL tables become
  locking reads/acquire SE level locks in the following cases:

  1) For commands asking for explicit locking like FLUSH TABLES ...
     WITH READ LOCK, LOCK TABLES ... READ/READ LOCAL or
     SELECT ... FOR SHARE/FOR UPDATE.
  2) For any non-SELECT statements, to ensure that they are correctly
     serialized in binary log. (However, this requirement is relaxed
     for INSERT ... SELECT, REPLACE ... SELECT, UPDATE and CREATE TABLE
     SELECT statements in READ UNCOMMITTED/COMMITTED isolation modes).
  3) In cases when binary logging is ON, uses statement or mixed modes,
     and we are executing SELECT statement which calls routine which
     might modify data. Again this is necessary to ensure that such
     statements are correctly serialized in binary log (in fact, when
     binlog is enabled and we use statement/mixed mode, we try to do
     this for all DML that can modify tables, but non-SELECT cases are
     covered by item 2)).
  4) When SELECT is executed in non-autocommit mode in serializable
     isolation mode. This is necessary to implement semantics prescribed
     by SQL standard for the serializable mode. Of course, the same
     semantics requirement apply to other DML in serializable mode,
     however they are already handled by item 2).
  5) When DML statement modifies table participating in a foreign key,
     constraint check acquires row locks on parent or child table.

  Cases described in 1) do not cause security concerns as they require
  extra privileges from users. Thus they should not be affected by
  this WL. However, SELECT ... FOR SHARE still might require special
  handling. See below.

  Requirement for reads to acquire SE locks to ensure correct binlog
  serialization (handled by cases 2) and 3)) can be relaxed for ACL
  tables if we sacrifice possibility for binary logging to work 
  correctly in statement mode for DML that reads ACL tables.
  This looks like a reasonable compromise since:
  a) ACL tables can be considered special tables which are not
     manipulated directly that often
  b) statement/mixed binlog mode are non-default in modern MySQL versions
  c) Common cases when ACL tables might be manipulated directly using
     INSERT/UPDATE and DELETE and not ACL DDL won't be affected.


  Requirement for reads to acquire SE locks to ensure correct semantics
  of serializable isolation mode (handled by cases 2) and 4) above)
  can be relaxed for ACL tables if we take conscious step to break
  SQL-standard semantics of serializable mode for ACL tables.
  Again, this sounds like a reasonable compromise since:
  a) ACL tables can be considered special tables which should not
     be normally queried directly by users. Most important case
     for such access is probably being dump tools. But
     mysqldump/pump tools should not be affected by this change
     as they use REPEATABLE READ isolation mode (or even rely on
     LOCK TABLES ... READ).

  b) Serializable isolation level is not default. So most of users
     SELECTing from ACL tables should not notice anything.

  Case described in 5) does not cause security concern either as
  it requires extra privileges from user creating foreign key.
  Similarly to case 1) this case should not be affected by this WL.

  When DML reads ACL table in serializable isolation mode, we issue a
  warning that SE row locks are not acquired.


B. DESIGN:

  The design of this WL is based on:
  WL#7464 - InnoDB: provide a way to do non-locking reads

  This WL uses the same interface introduced by above work,
  handler::extra(ha_extra_function::HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW)
  to convey InnoDB to skip acquiring row locks for  read
  operation by this WL. This interface is invoked when opening
  the tables for a statement inside open_tables().

  WL#7464 is implemented in InnoDB to enable
  skipping read lock and gap locks when 'handler' object is set with
  HA_EXTRA_SKIP_SERIALIZABLE_DD_VIEW function. These implementations
  did not take  into account. This WL introduces changes
  to InnoDB code to support the same.

  These above changes enables execution of ACL DDL and DML that
  modify the  to not block, when a concurrent
  connection is reading .

  The following section (C), lists specific commands during
  which this WL requests InnoDB to skip read locks for .


C. COMMANDS AFFECTED:

  1. This WL requests InnoDB to skip read row lock on  for
     the following classes of commands:

     i.   All SELECT statements and other read-only statements (e.g. DO/SET)
          that reference  directly (from join list or
          through subquery), excluding SELECT ... FOR UPDATE, but including
          SELECT ... FOR SHARE
          (see below for rationale).
     ii.  All data-modification statements that read  directly
          (using join-list or through subquery).
          Some examples are:
            INSERT/REPLACE ... SELECT,
            UPDATE with subquery in WHERE or SET clauses,
            DELETE with subquery in WHERE clause,
            multi-UPDATE/DELETE,
            LOAD DATA with subquery,
            CREATE TABLE ... SELECT.

     iii. All SELECT-like and data-modification statements which read  through stored function, or through trigger to be invoked
          by them.

     Note: Acquiring row locks when ACL table is read by SELECT ... FOR SHARE
     doesn't introduce security risk, since extra privileges are required in 
     this case. However, this creates risk of deadlock in concurrently
     executed ACL DDL (SELECT ... FOR SHARE only acquires SR metadata locks
     and thus after WL#14084 allows concurrent execution of ACL DDL),
     which we would like to avoid (as ACL DDL is probably not yet ready to
     properly handle deadlocks on row level).
     Thus we ignore locking clause in this case as well. This seems to be
     reasonable compromise as user's should not normally do SELECT ... FOR
     SHARE over ACL tables.

  2. Commands that modify the  continue to acquire
     write row lock. For example:

     - INSERT/REPLACE, UPDATE, DELETE, LOAD DATA commands modifying
       rows in .
     - SELECT/SET/DO commands that modify  through
       stored routines that they call.

  3. The behavior will stay the same for statements that only read
      , Fro example:

    1) Statements requesting explicit locking (except SELECT ...
       FOR SHARE) like LOCK TABLES ... READ [LOCAL] and
       SELECT ... FOR UPDATE.
    2) Statements doing FK checks on ACL tables.
    3) ALTER TABLE ALGORITHM=INPLACE, altering ACL tables.


D. REPLICATION:

  Execution of commands C.1.ii/iii/iv in Row-Based-Replication
  setup would be recommended. In SBR mode the correct binlogging of
  DML relies on row-locking and thus might result in inconsistency
  without locking.

  Because of the above DML that reads ACL tables and is written to binary
  log in statement mode would generate a warning, stating SE locks are not
  acquired and that operation is not safe for binary log.

  Also we need to ensure that in --binlog-format=mixed mode DML statements
  which read ACL tables are written to binary log using row events, and
  thus their logging is safe in mixed mode.


E. TOOLS:

   - MYSQLDUMP/PUMP tool are not affected by this WL. It uses the
     default isolation level REPEATABLE READ mode.

   - MYSQLBACKUP tools doing backup and restore operation is not
     affected by this WL.


F. UPGRADE:

  This WL does not affect upgrade.


G. DOCUMENTATION:

  Documentation should be updated to explicitly state the advantage
  of this WL, which enables  to be not affected by
  concurrent connections reading .

  Also, it would be good to document following restrictions. The aim
  of doing following is to enable a concurrent ACL DDL to be executed
  without blocking.

  1) The DML that reads ACL table will not honor serializable
     isolation mode. I.e., It would not acquire SE row locks.

  2) DML that reads ACL tables which is written to binary log in
     statement mode would not acquire SE row locks. Note that this
     operation is not safe for statement logging.

  3) The SELECT ... FOR SHARE reading ACL Tables would not acquire
     SE row locks, i.e. it would not honor FOR SHARE clause.

Notes:
1. This WL introduces a API interface like,

    'bool is_acl_table(std::string db_name, std::string table_name);'

  which is used in open_tables() to determine if we should skip read locks
  for a .