MYSQL METADATA LOCK(MDL LOCK) 理論及加鎖類型測試

#0 open_table_get_mdl_lock (thd=0x7fffd0000df0, ot_ctx=0x7fffec06fb00,
    table_list=0x7fffd00067d8, flags=0, mdl_ticket=0x7fffec06f950)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:2789
#1 0x0000000001516e17 in open_table (thd=0x7fffd0000df0,
    table_list=0x7fffd00067d8, ot_ctx=0x7fffec06fb00)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:3237

MDL_INTENTION_EXCLUSIVE(IX)
MDL_SHARED(S)
MDL_SHARED_HIGH_PRIO(SH)
MDL_SHARED_READ(SR)
MDL_SHARED_WRITE(SW)
MDL_SHARED_WRITE_LOW_PRIO(SWL)
MDL_SHARED_UPGRADABLE(SU)
MDL_SHARED_READ_ONLY(SRO)
MDL_SHARED_NO_WRITE(SNW)
MDL_SHARED_NO_READ_WRITE(SNRW)
MDL_EXCLUSIVE(X)

- GLOBAL is used for the global read lock.
     - TABLESPACE is for tablespaces.
     - SCHEMA is for schemas (aka databases).
     - TABLE is for tables and views.
     - FUNCTION is for stored functions.
     - PROCEDURE is for stored procedures.
     - TRIGGER is for triggers.
     - EVENT is for event scheduler events.
     - COMMIT is for enabling the global read lock to block commits.
     - USER_LEVEL_LOCK is for user-level locks.
     - LOCKING_SERVICE is for the name plugin RW-lock service

 /**
    Helper struct which defines how different types of locks are handled
    for a specific MDL_lock. In practice we use only two strategies: "scoped"
    lock strategy for locks in GLOBAL, COMMIT, TABLESPACE and SCHEMA namespaces
    and "object" lock strategy for all other namespaces.
  */

| Type of active   |
  Request |   scoped lock    |
   type   | IS(*)  IX   S  X |
 ---------+------------------+
 IS       |  +      +   +  + |
 IX       |  +      +   -  - |
 S        |  +      -   +  - |
 X        |  +      -   -  - |

Request  |  Granted requests for lock            |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      S         | +   +   +   +    +    +   +    +    +    -  |
      SH        | +   +   +   +    +    +   +    +    +    -  |
      SR        | +   +   +   +    +    +   +    +    -    -  |
      SW        | +   +   +   +    +    +   -    -    -    -  |
      SWLP      | +   +   +   +    +    +   -    -    -    -  |
      SU        | +   +   +   +    +    -   +    -    -    -  |
      SRO       | +   +   +   -    -    +   +    +    -    -  |
      SNW       | +   +   +   -    -    -   +    -    -    -  |
      SNRW      | +   +   -   -    -    -   -    -    -    -  |
      X         | -   -   -   -    -    -   -    -    -    -  |

MDL_STATEMENT:Locks with statement duration are automatically released at the end
              of statement or transaction.
MDL_TRANSACTION: Locks with transaction duration are automatically released at the end
                of transaction
MDL_EXPLICIT:Locks with explicit duration survive the end of statement and transaction.
             They have to be released explicitly by calling MDL_context::release_lock().

A) "unobtrusive" lock types
            1) Each type from this set should be compatible with all other
               types from the set (including itself).
            2) These types should be common for DML operations
          Our goal is to optimize acquisition and release of locks of this
          type by avoiding complex checks and manipulations on m_waiting/
          m_granted bitmaps/lists. We replace them with a check of and
          increment/decrement of integer counters.
          We call the latter type of acquisition/release "fast path".
          Use of "fast path" reduces the size of critical section associated
          with MDL_lock::m_rwlock lock in the common case and thus increases
          scalability.
          The amount by which acquisition/release of specific type
          "unobtrusive" lock increases/decreases packed counter in
          MDL_lock::m_fast_path_state is returned by this function.
B) "obtrusive" lock types
            1) Granted or pending lock of those type is incompatible with
               some other types of locks or with itself.
            2) Not common for DML operations
          These locks have to be always acquired involving manipulations on
          m_waiting/m_granted bitmaps/lists, i.e. we have to use "slow path"
          for them. Moreover in the presence of active/pending locks from
          "obtrusive" set we have to acquire using "slow path" even locks of
          "unobtrusive" type.

/** Type of metadata lock. */
  enum enum_mdl_type type; //需求的類型
  /** Duration for requested lock. */
  enum enum_mdl_duration duration; //持續(xù)時間
  /**
    Pointers for participating in the list of lock requests for this context.
  */
  MDL_request *next_in_list; //雙向鏈表實現(xiàn)
  MDL_request **prev_in_list;
  /**
    Pointer to the lock ticket object for this lock request.
    Valid only if this lock request is satisfied.
  */
  MDL_ticket *ticket; //注意這里如果申請成功(沒有等待)，會指向一個實際的TICKET，否則為NULL
  /** A lock is requested based on a fully qualified name and type. */
  MDL_key key;//注意這里是一個MDL_KEY類型，主要的就是前面說的NAMESPACE+DB+OBJECT_NAME

  uint16 m_length;
  uint16 m_db_name_length;
  char m_ptr[MAX_MDLKEY_LENGTH];//放到了這里

/**
    Pointers for participating in the list of lock requests for this context.
    Context private.正如解釋這里是context中鏈表鏈表的形成，是線程私有的
  */
  MDL_ticket *next_in_context;
  MDL_ticket **prev_in_context;
  /**
    Pointers for participating in the list of satisfied/pending requests
    for the lock. Externally accessible.正如解釋這里是MDL_LOCK中鏈表鏈表的形成，是全局的
  */
  MDL_ticket *next_in_lock;
  MDL_ticket **prev_in_lock;
/**
    Context of the owner of the metadata lock ticket. Externally accessible.
    很明顯這里指向了這個ticket的擁有者也就是MDL_context，它是線程的屬性
  */
  MDL_context *m_ctx;


  /**
    Pointer to the lock object for this lock ticket. Externally accessible.
    很明顯這里是一個指向MDL_LOCK的一個指針
  */
  MDL_lock *m_lock;


  /**
    Indicates that ticket corresponds to lock acquired using "fast path"
    algorithm. Particularly this means that it was not included into
    MDL_lock::m_granted bitmap/list and instead is accounted for by
    MDL_lock::m_fast_path_locks_granted_counter
    這里就代表了是否是FAST PATH從注釋來看fast path方式不會在MDL LOCK中
    占用granted位圖和鏈表取而代之代之的是一個統(tǒng)計器m_fast_path_locks_granted_counter
    這樣一來開銷肯定更小
  */
  bool m_is_fast_path;


  /**
    Indicates that ticket corresponds to lock request which required
    storage engine notification during its acquisition and requires
    storage engine notification after its release.
  */
  bool m_hton_notified;

/** The key of the object (data) being protected. */
  MDL_key key;
/** List of granted tickets for this lock. */
  Ticket_list m_granted;
/** Tickets for contexts waiting to acquire a lock. */
  Ticket_list m_waiting;

/**
    If our request for a lock is scheduled, or aborted by the deadlock
    detector, the result is recorded in this class.
  */
  MDL_wait m_wait;
/**
    Lists of all MDL tickets acquired by this connection.
    這是一個不同MDL lock持續(xù)時間的一個鏈表數(shù)組。實際就是
    MDL_STATEMENT一個鏈表
    MDL_TRANSACTION一個鏈表
    MDL_EXPLICIT一個鏈表
  */
Ticket_list m_tickets[MDL_DURATION_END];
//這是一個父類指針指向子類對象，虛函數(shù)重寫的典型,實際他就指向了一個線程
/*
class THD :public MDL_context_owner,
           public Query_arena,
           public Open_tables_state
*/
MDL_context_owner *m_owner;

PSI_stage_info MDL_key::m_namespace_to_wait_state_name[NAMESPACE_END]=
{
  {0, "Waiting for global read lock", 0},
  {0, "Waiting for tablespace metadata lock", 0},
  {0, "Waiting for schema metadata lock", 0},
  {0, "Waiting for table metadata lock", 0},
  {0, "Waiting for stored function metadata lock", 0},
  {0, "Waiting for stored procedure metadata lock", 0},
  {0, "Waiting for trigger metadata lock", 0},
  {0, "Waiting for event metadata lock", 0},
  {0, "Waiting for commit lock", 0},
  {0, "User lock", 0}, /* Be compatible with old status. */
  {0, "Waiting for locking service lock", 0},
  {0, "Waiting for backup lock", 0},
  {0, "Waiting for binlog lock", 0}
};

mysql>   select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: test
OBJECT_INSTANCE_BEGIN: 140734412907760
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6314
      OWNER_THREAD_ID: 39
       OWNER_EVENT_ID: 241

bool
MDL_context::acquire_lock(MDL_request *mdl_request, ulong lock_wait_timeout)
{
  if (mdl_request->ticket) //獲取成功獲得ticket
  {
    /*
      We have managed to acquire lock without waiting.
      MDL_lock, MDL_context and MDL_request were updated
      accordingly, so we can simply return success.
    */
    //REQUESET獲取TICKET成功 此處打印
    return FALSE;
  }
  /*
    Our attempt to acquire lock without waiting has failed.
    As a result of this attempt we got MDL_ticket with m_lock
    member pointing to the corresponding MDL_lock object which
    has MDL_lock::m_rwlock write-locked.
  */
  //獲取不成功加入MDL_lock 等待隊列
  lock= ticket->m_lock;

  lock->m_waiting.add_ticket(ticket);

  will_wait_for(ticket); //死鎖檢測

  /* There is a shared or exclusive lock on the object. */
  DEBUG_SYNC(get_thd(), "mdl_acquire_lock_wait");

  find_deadlock();
  
  //此處打印TICKET進入了等待流程
 
  if (lock->needs_notification(ticket) || lock->needs_connection_check())
  {
   }
  done_waiting_for();//等待完成對死鎖檢測等待圖進行調(diào)整去掉本等待邊edge(無向圖)

  //當然到這里也是通過等待后獲得成功了狀態(tài)為GRANTED
  DBUG_ASSERT(wait_status == MDL_wait::GRANTED);

  m_tickets[mdl_request->duration].push_front(ticket);

  mdl_request->ticket= ticket;

  mysql_mdl_set_status(ticket->m_psi, MDL_ticket::GRANTED);
  //此處打印通過等待REQUEST獲得了TICKET
  return FALSE;
}

void MDL_ticket::downgrade_lock(enum_mdl_type new_type)
{
  
  /* Only allow downgrade from EXCLUSIVE and SHARED_NO_WRITE. */
  DBUG_ASSERT(m_type == MDL_EXCLUSIVE ||
              m_type == MDL_SHARED_NO_WRITE);

//此處打印出降級前的TICKET

  if (m_hton_notified)
  {
    mysql_mdl_set_status(m_psi, MDL_ticket::POST_RELEASE_NOTIFY);
    m_ctx->get_owner()->notify_hton_post_release_exclusive(m_lock->key);
    m_hton_notified= false;
    mysql_mdl_set_status(m_psi, MDL_ticket::GRANTED);
  }
//函數(shù)結(jié)尾答應(yīng)出降級后的TICKET
}

bool
MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket,
                                 enum_mdl_type new_type,
                                 ulong lock_wait_timeout)
{
  MDL_REQUEST_INIT_BY_KEY(mdl_new_lock_request,
                          mdl_ticket->m_lock->key, new_type,
                          MDL_TRANSACTION);//構(gòu)造一個request

 //此處打印出來的TICKET類型
  
  if (acquire_lock(mdl_new_lock_request, lock_wait_timeout)) //嘗試使用新的LOCK_TYPE進行加鎖
    DBUG_RETURN(TRUE);
  
  is_new_ticket= ! has_lock(mdl_svp, mdl_new_lock_request.ticket);

  lock= mdl_ticket->m_lock;

  //下面進行一系列對MDL_LOCK的維護并且對所謂的合并操作
  /* Code below assumes that we were upgrading to "obtrusive" type of lock. */
  DBUG_ASSERT(lock->is_obtrusive_lock(new_type));

  /* Merge the acquired and the original lock. @todo: move to a method. */
  mysql_prlock_wrlock(lock->m_rwlock);

  if (is_new_ticket)
  {
    m_tickets[MDL_TRANSACTION].remove(mdl_new_lock_request.ticket);
    MDL_ticket::destroy(mdl_new_lock_request.ticket);
  }
 //此處打印出來的升級后TICKET類型
  DBUG_RETURN(FALSE);
}

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SH        | +   +   +   +    +    +   +    +    +    -  |
mysql> desc test.testsort10;

       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SR        | +   +   +   +    +          +      +        +    -    -  |
mysql> select * from test.testsort10 limit 1;

       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SW        | +   +   +   +    +    +   -    -    -    -  |
mysql> select * from test.testsort10 limit 1 for update;

       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SWLP      | +   +   +   +    +    +   -    -    -    -  |
mysql> update  LOW_PRIORITY  test.testsort10 set id1=1000 where id1= 96282;

       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SU        | +   +   +   +    +    -   +    -    -    -  |

mysql>  alter table testsort12 add column it int not null;
Query OK, 0 rows affected (6.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

       Request  |  Granted requests for lock                   
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SNW       | +   +   +   -    -    -   +    -    -    -  |

      Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SRO       | +   +   +   -    -    +   +    +    -    -  |

       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SNRW      | +   +   -   -    -    -   -    -    -    -  |

       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      X         | -   -   -   -    -    -   -    -    -    -  |

enum enum_mdl_type {
  /*
    An intention exclusive metadata lock. Used only for scoped locks.
    Owner of this type of lock can acquire upgradable exclusive locks on
    individual objects.
    Compatible with other IX locks, but is incompatible with scoped S and
    X locks.
  */
  MDL_INTENTION_EXCLUSIVE= 0,
  /*
    A shared metadata lock.
    To be used in cases when we are interested in object metadata only
    and there is no intention to access object data (e.g. for stored
    routines or during preparing prepared statements).
    We also mis-use this type of lock for open HANDLERs, since lock
    acquired by this statement has to be compatible with lock acquired
    by LOCK TABLES ... WRITE statement, i.e. SNRW (We can't get by by
    acquiring S lock at HANDLER ... OPEN time and upgrading it to SR
    lock for HANDLER ... READ as it doesn't solve problem with need
    to abort DML statements which wait on table level lock while having
    open HANDLER in the same connection).
    To avoid deadlock which may occur when SNRW lock is being upgraded to
    X lock for table on which there is an active S lock which is owned by
    thread which waits in its turn for table-level lock owned by thread
    performing upgrade we have to use thr_abort_locks_for_thread()
    facility in such situation.
    This problem does not arise for locks on stored routines as we don't
    use SNRW locks for them. It also does not arise when S locks are used
    during PREPARE calls as table-level locks are not acquired in this
    case.
  */
  MDL_SHARED,
  /*
    A high priority shared metadata lock.
    Used for cases when there is no intention to access object data (i.e.
    data in the table).
    "High priority" means that, unlike other shared locks, it is granted
    ignoring pending requests for exclusive locks. Intended for use in
    cases when we only need to access metadata and not data, e.g. when
    filling an INFORMATION_SCHEMA table.
    Since SH lock is compatible with SNRW lock, the connection that
    holds SH lock lock should not try to acquire any kind of table-level
    or row-level lock, as this can lead to a deadlock. Moreover, after
    acquiring SH lock, the connection should not wait for any other
    resource, as it might cause starvation for X locks and a potential
    deadlock during upgrade of SNW or SNRW to X lock (e.g. if the
    upgrading connection holds the resource that is being waited for).
  */
  MDL_SHARED_HIGH_PRIO,
  /*
    A shared metadata lock for cases when there is an intention to read data
    from table.
    A connection holding this kind of lock can read table metadata and read
    table data (after acquiring appropriate table and row-level locks).
    This means that one can only acquire TL_READ, TL_READ_NO_INSERT, and
    similar table-level locks on table if one holds SR MDL lock on it.
    To be used for tables in SELECTs, subqueries, and LOCK TABLE ... READ
    statements.
  */
  MDL_SHARED_READ,
  /*
    A shared metadata lock for cases when there is an intention to modify
    (and not just read) data in the table.
    A connection holding SW lock can read table metadata and modify or read
    table data (after acquiring appropriate table and row-level locks).
    To be used for tables to be modified by INSERT, UPDATE, DELETE
    statements, but not LOCK TABLE ... WRITE or DDL). Also taken by
    SELECT ... FOR UPDATE.
  */
  MDL_SHARED_WRITE,
  /*
    A version of MDL_SHARED_WRITE lock which has lower priority than
    MDL_SHARED_READ_ONLY locks. Used by DML statements modifying
    tables and using the LOW_PRIORITY clause.
  */
  MDL_SHARED_WRITE_LOW_PRIO,
  /*
    An upgradable shared metadata lock which allows concurrent updates and
    reads of table data.
    A connection holding this kind of lock can read table metadata and read
    table data. It should not modify data as this lock is compatible with
    SRO locks.
    Can be upgraded to SNW, SNRW and X locks. Once SU lock is upgraded to X
    or SNRW lock data modification can happen freely.
    To be used for the first phase of ALTER TABLE.
  */
  MDL_SHARED_UPGRADABLE,
  /*
    A shared metadata lock for cases when we need to read data from table
    and block all concurrent modifications to it (for both data and metadata).
    Used by LOCK TABLES READ statement.
  */
  MDL_SHARED_READ_ONLY,
  /*
    An upgradable shared metadata lock which blocks all attempts to update
    table data, allowing reads.
    A connection holding this kind of lock can read table metadata and read
    table data.
    Can be upgraded to X metadata lock.
    Note, that since this type of lock is not compatible with SNRW or SW
    lock types, acquiring appropriate engine-level locks for reading
    (TL_READ* for MyISAM, shared row locks in InnoDB) should be
    contention-free.
    To be used for the first phase of ALTER TABLE, when copying data between
    tables, to allow concurrent SELECTs from the table, but not UPDATEs.
  */
  MDL_SHARED_NO_WRITE,
  /*
    An upgradable shared metadata lock which allows other connections
    to access table metadata, but not data.
    It blocks all attempts to read or update table data, while allowing
    INFORMATION_SCHEMA and SHOW queries.
    A connection holding this kind of lock can read table metadata modify and
    read table data.
    Can be upgraded to X metadata lock.
    To be used for LOCK TABLES WRITE statement.
    Not compatible with any other lock type except S and SH.
  */
  MDL_SHARED_NO_READ_WRITE,
  /*
    An exclusive metadata lock.
    A connection holding this lock can modify both table's metadata and data.
    No other type of metadata lock can be granted while this lock is held.
    To be used for CREATE/DROP/RENAME TABLE statements and for execution of
    certain phases of other DDL statements.
  */
  MDL_EXCLUSIVE,
  /* This should be the last !!! */
  MDL_TYPE_END};


/** Duration of metadata lock. */

enum enum_mdl_duration {
  /**
    Locks with statement duration are automatically released at the end
    of statement or transaction.
  */
  MDL_STATEMENT= 0,
  /**
    Locks with transaction duration are automatically released at the end
    of transaction.
  */
  MDL_TRANSACTION,
  /**
    Locks with explicit duration survive the end of statement and transaction.
    They have to be released explicitly by calling MDL_context::release_lock().
  */
  MDL_EXPLICIT,
  /* This should be the last ! */
  MDL_DURATION_END };

/**
    Object namespaces.
    Sic: when adding a new member to this enum make sure to
    update m_namespace_to_wait_state_name array in mdl.

    Different types of objects exist in different namespaces
     - GLOBAL is used for the global read lock.
     - TABLESPACE is for tablespaces.
     - SCHEMA is for schemas (aka databases).
     - TABLE is for tables and views.
     - FUNCTION is for stored functions.
     - PROCEDURE is for stored procedures.
     - TRIGGER is for triggers.
     - EVENT is for event scheduler events.
     - COMMIT is for enabling the global read lock to block commits.
     - USER_LEVEL_LOCK is for user-level locks.
     - LOCKING_SERVICE is for the name plugin RW-lock service
    Note that although there isn't metadata locking on triggers,
    it's necessary to have a separate namespace for them since
    MDL_key is also used outside of the MDL subsystem.
    Also note that requests waiting for user-level locks get special
    treatment - waiting is aborted if connection to client is lost.
  */
  enum enum_mdl_namespace { GLOBAL=0,
                            TABLESPACE,
                            SCHEMA,
                            TABLE,
                            FUNCTION,
                            PROCEDURE,
                            TRIGGER,
                            EVENT,
                            COMMIT,
                            USER_LEVEL_LOCK,
                            LOCKING_SERVICE,
                            BACKUP,
                            BINLOG,
                            /* This should be the last ! */
                            NAMESPACE_END };