MYSQL 表锁与存储引擎交互

以下是我对于mysql的表锁的一些理解，可能会存在一些错误，欢迎指正。

---

mysql的表锁是在sql层上锁的，但锁是从存储引擎层获得的。与其相关的数据结构主要为THR_LOCK_DATA,THR_LOCK,与存储引擎交互的主要函数为external_lock，store_lock。
下面分别介绍（MYSQL 版本为5.7.4）

数据结构

一般而言THR_LOCK为存储引擎中Handler_share的子类（如Example_share）的成员变量，而THR_LOCK_DATA在handler的子类（如ha_example）中，就像Handler_share与handler关系一样，每个表只有一个THR_LOCK,但有多个THR_LOCK_DATA。多线程并发访问时每个线程都要有一个THR_LOCK_DATA。详情如下：

class Example_share : public Handler_share {public:  THR_LOCK lock;  Example_share();...};class ha_example: public handler{  THR_LOCK_DATA lock;      ///< MySQL lock  Example_share *share;    ///< Shared lock info  Example_share *get_share(); ///< Get the share...};

一般在handler::open()中调用
get_share()时通过thr_lock_init(THR_LOCK*) 初始化THR_LOCK（可在share的构造函数中初始化（achive）或者get_share中（innodb5.6））
之后调用thr_lock_data_init（THR_LOCK*，THR_LOCK_DATA*）初始化THR_LOCK_DATA。

下面对数据结构的介绍主要参考MySQL的表级别线程锁机制

THR_LOCK

THR_LOCK由每个表的多个实例共享。

typedef struct st_thr_lock {  LIST list;  mysql_mutex_t mutex;  struct st_lock_list read_wait;  struct st_lock_list read;  struct st_lock_list write_wait;  struct st_lock_list write;  /* write_lock_count is incremented for write locks and reset on read locks */  ulong write_lock_count;  uint read_no_write_count;  void (*get_status)(void*, int);   /* When one gets a lock */  void (*copy_status)(void*,void*);  void (*update_status)(void*);     /* Before release of write */  void (*restore_status)(void*);         /* Before release of read */  my_bool (*check_status)(void *);} THR_LOCK;struct st_lock_list {  THR_LOCK_DATA *data,**last;};

其中：
- list 链接到全局链表 thr_lock_thread_list 中的list node, 使用 mysqladmin debug 命令可以打印出所有的lock信息。
- mutex 用于保护THR_LOCK对象自身的互斥量
- read_wait/read/write_wait/write 持有以及等待读写锁的链表.
- write_lock_count 用于记录连续满足的写请求数, 存在max_write_lock_count，以允许读请求在多个写请求期间得到满足不至于饿死. 因为默认某些写操作的优先级高于读, 连续的写请求会导致读请求饿死.
- read_no_write_count 记录 TL_READ_NO_INSERT 锁类型的数目

THR_LOCK_DATA

typedef struct st_thr_lock_data {  THR_LOCK_INFO *owner;  struct st_thr_lock_data *next,**prev;  struct st_thr_lock *lock;  mysql_cond_t *cond;  enum thr_lock_type type;  void *status_param;           /* Param to status functions */  void *debug_print_param;  struct PSI_table *m_psi;} THR_LOCK_DATA;

其中：
- owner 为线程锁信息，包含线程ID,及一个用于唤醒该线程的条件变量suspend。
- next/prev THR_LOCK_DATA为双向链表结构
- lock 指向所属的THR_LOCK。
- cond 多线程并发条件变量，等待锁时指向owner->suspend,以唤醒该线程。
- type 锁类型，之后介绍

锁类型

enum thr_lock_type {              TL_IGNORE=-1,             TL_UNLOCK,         /* UNLOCK ANY LOCK */                     /*                       Parser only! At open_tables() becomes TL_READ or                       TL_READ_NO_INSERT depending on the binary log format                       (SBR/RBR) and on the table category (log table).                       Used for tables that are read by statements which                       modify tables.                     */                     TL_READ_DEFAULT,             TL_READ,           /* Read lock */             TL_READ_WITH_SHARED_LOCKS,             /* High prior. than TL_WRITE. Allow concurrent insert */             TL_READ_HIGH_PRIORITY,             /* READ, Don't allow concurrent insert */             TL_READ_NO_INSERT,             /*             Write lock, but allow other threads to read / write.            Used by BDB tables in MySQL to mark that someone is            reading/writing to the table.              */             TL_WRITE_ALLOW_WRITE,                     /*                       parser only! Late bound low_priority_flag.                       At open_tables() becomes thd->insert_lock_default.                     */                     TL_WRITE_CONCURRENT_DEFAULT,             /*               WRITE lock used by concurrent insert. Will allow               READ, if one could use concurrent insert on table.             */             TL_WRITE_CONCURRENT_INSERT,                     /*                        parser only! Late bound low_priority flag.                        At open_tables() becomes thd->update_lock_default.                     */                     TL_WRITE_DEFAULT,             /* WRITE lock that has lower priority than TL_READ */             TL_WRITE_LOW_PRIORITY,             /* Normal WRITE lock */             TL_WRITE,             /* Abort new lock request with an error */             TL_WRITE_ONLY};

抛开parser-only类型, 这些类型的优先级从低到高排列如下:
- TL_WRITE_ALLOW_WRITE
- TL_WRITE_CONCURRENT_INSERT
- TL_WRITE_LOW_PRIORITY
- TL_READ
- TL_WRITE
- TL_READ_HIGH_PRIORITY
- TL_WRITE_ONLY

由于TL_WRITE_ALLOW_WRITE锁类型允许其他线程读/写，也即相当于不加锁，故在store_lock中设置TL_WRITE_ALLOW_WRITE表明由存储引擎自己实现锁机制。

函数

锁表相关函数调用顺序

mysql_lock_tables()    get_lock_data()        store_lock()    lock_external()        ha_external_lock()    thr_multi_lock()        thr_lock()

thr_lock

由于本文侧重于表锁与存储引擎交换，故对于thr_lock不在详细介绍，thr_lock详细实现可参考MySQL数据结构分析—THR_LOCK

store_lock

每一个处理客户端SQL命令的线程都会先向Table Lock Manager申请要访问的数据库表的锁，Table Lock Manager 在决定如何分配锁时会先调用store_lock(),询问存储引擎应如何加锁，若存储引擎使用默认加锁机制，则直接将查询操作需要的锁类型赋给存储引擎的锁，如Memory引擎：

THR_LOCK_DATA **ha_heap::store_lock(THD *thd,                    THR_LOCK_DATA **to,                    enum thr_lock_type lock_type){  /*    This method should not be called for internal temporary tables    as they don't have properly initialized THR_LOCK and THR_LOCK_DATA    structures.  */  DBUG_ASSERT(!internal_table);  if (lock_type != TL_IGNORE && file->lock.type == TL_UNLOCK)    file->lock.type=lock_type;  *to++= &file->lock;  return to;}

若不想使用默认机制，如存储引擎想使用行锁，则可通过把锁类型设为TL_WRITE_ALLOW_WRITE,Table LockManager如果发现锁类型为TL_WRITE_ALLOW_WRITE，则无论任何加锁，都直接分配申请的锁，由存储引擎进行锁管理。例子如下：

/*   Below is an example of how to setup row level locking.*/THR_LOCK_DATA **ha_archive::store_lock(THD *thd,                                       THR_LOCK_DATA **to,                                       enum thr_lock_type lock_type){  if (lock_type != TL_IGNORE && lock.type == TL_UNLOCK)   {    /*       Here is where we get into the guts of a row level lock.      If TL_UNLOCK is set       If we are not doing a LOCK TABLE or DISCARD/IMPORT      TABLESPACE, then allow multiple writers     */    if ((lock_type >= TL_WRITE_CONCURRENT_INSERT &&         lock_type <= TL_WRITE) && !thd_in_lock_tables(thd)        && !thd_tablespace_op(thd))      lock_type = TL_WRITE_ALLOW_WRITE;    /*       In queries of type INSERT INTO t1 SELECT ... FROM t2 ...      MySQL would use the lock TL_READ_NO_INSERT on t2, and that      would conflict with TL_WRITE_ALLOW_WRITE, blocking all inserts      to t2. Convert the lock to a normal read lock to allow      concurrent inserts to t2.     */    if (lock_type == TL_READ_NO_INSERT && !thd_in_lock_tables(thd))       lock_type = TL_READ;    lock.type=lock_type;  }  *to++= &lock;  return to;}

external_lock

external_lock 是用来通知存储引擎加锁情况的，同时也会进行一些其他处理，如innodb中，注册事务、加表锁（设置为由innodb处理）等，同时其会中在external_lock记录表锁数量，当调用external_lock(F_UNLOCK)时，数量为0时，根据autocommit设置，判断是否要commit。（由于上层不会自动调用，故需在引擎调用）

note ：

  When not using LOCK TABLES:  - For each SQL statement mysql_lock_tables() is called for all involved    tables.    - mysql_lock_tables() will call      table_handler->external_lock(thd,locktype) for each table.      This is followed by a call to thr_multi_lock() for all tables.  - When statement is done, we call mysql_unlock_tables().    This will call thr_multi_unlock() followed by    table_handler->external_lock(thd, F_UNLCK) for each table.  - Note that mysql_unlock_tables() may be called several times as    MySQL in some cases can free some tables earlier than others.  - The above is true both for normal and temporary tables.  - Temporary non transactional tables are never passed to thr_multi_lock()    and we never call external_lock(thd, F_UNLOCK) on these.  When using LOCK TABLES:  - LOCK TABLE will call mysql_lock_tables() for all tables.    mysql_lock_tables() will call    table_handler->external_lock(thd,locktype) for each table.    This is followed by a call to thr_multi_lock() for all tables.  - For each statement, we will call table_handler->start_stmt(THD)    to inform the table handler that we are using the table.    The tables used can only be tables used in LOCK TABLES or a    temporary table.  - When statement is done, we will call ha_commit_stmt(thd);  - When calling UNLOCK TABLES we call mysql_unlock_tables() for all    tables used in LOCK TABLES  If table_handler->external_lock(thd, locktype) fails, we call  table_handler->external_lock(thd, F_UNLCK) for each table that was locked,  excluding one that caused failure. That means handler must cleanup itself  in case external_lock() fails.

• 当不使用LOCK TABLES 时，在执行每个语句时mysql_lock_tables会被调用，也即external_lock会被调用。
• 当使用LOCK TABLES 时，mysql_lock_tables只在开始调用，在每个语句执行前调用start_stmt()。

    在innodb的external_lock中对于autocommit=1，提交事务，=0，释放本语句的read_view    /* If the MySQL lock count drops to zero we know that the current SQL    statement has ended *///autocommit    if (trx->n_mysql_tables_in_use == 0) {        trx->mysql_n_tables_locked = 0;        m_prebuilt->used_in_HANDLER = FALSE;        if (!thd_test_options(                thd, OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) {            if (trx_is_started(trx)) {                innobase_commit(ht, thd, TRUE);            } else {                /* Since the trx state is TRX_NOT_STARTED,                trx_commit() will not be called. Reset                trx->is_dd_trx here */                ut_d(trx->is_dd_trx = false);            }        } else if (trx->isolation_level <= TRX_ISO_READ_COMMITTED               && MVCC::is_view_active(trx->read_view)) {            mutex_enter(&trx_sys->mutex);            trx_sys->mvcc->view_close(trx->read_view, true);            mutex_exit(&trx_sys->mutex);        }    }

以下为加表锁判断（external_lock innodb）

        /* Starting from 4.1.9, no InnoDB table lock is taken in LOCK        TABLES if AUTOCOMMIT=1. It does not make much sense to acquire        an InnoDB table lock if it is released immediately at the end        of LOCK TABLES, and InnoDB's table locks in that case cause        VERY easily deadlocks.        We do not set InnoDB table locks if user has not explicitly        requested a table lock. Note that thd_in_lock_tables(thd)        can hold in some cases, e.g., at the start of a stored        procedure call (SQLCOM_CALL). */        if (m_prebuilt->select_lock_type != LOCK_NONE) {            if (thd_sql_command(thd) == SQLCOM_LOCK_TABLES                && THDVAR(thd, table_locks)                && thd_test_options(thd, OPTION_NOT_AUTOCOMMIT)                && thd_in_lock_tables(thd)) {                dberr_t error = row_lock_table_for_mysql(                    m_prebuilt, NULL, 0);                if (error != DB_SUCCESS) {                    DBUG_RETURN(                        convert_error_code_to_mysql(                            error, 0, thd));                }            }            trx->mysql_n_tables_locked++;        }

note

在mysql5.7中innodb不在使用thr_lock来进行表锁控制，而是使用MDL锁
主要通过：

uintha_innobase::lock_count(void) const/*===============================*/{        return 0;}

返回0到上层函数get_lock_data，那么对于Innodb表，将不会再为其创建thr lock锁。如此简单的避免了为Innodb表创建THR LOCK.

在5.6版中innodb的store_lock 实现如下

if (lock_type != TL_IGNORE && lock.type == TL_UNLOCK) {        /* Starting from 5.0.7, we weaken also the table locks        set at the start of a MySQL stored procedure call, just like        we weaken the locks set at the start of an SQL statement.        MySQL does set in_lock_tables TRUE there, but in reality        we do not need table locks to make the execution of a        single transaction stored procedure call deterministic        (if it does not use a consistent read). */        if (lock_type == TL_READ            && sql_command == SQLCOM_LOCK_TABLES) {            /* We come here if MySQL is processing LOCK TABLES            ... READ LOCAL. MyISAM under that table lock type            reads the table as it was at the time the lock was            granted (new inserts are allowed, but not seen by the            reader). To get a similar effect on an InnoDB table,            we must use LOCK TABLES ... READ. We convert the lock            type here, so that for InnoDB, READ LOCAL is            equivalent to READ. This will change the InnoDB            behavior in mysqldump, so that dumps of InnoDB tables            are consistent with dumps of MyISAM tables. */            lock_type = TL_READ_NO_INSERT;        }        /* If we are not doing a LOCK TABLE, DISCARD/IMPORT        TABLESPACE or TRUNCATE TABLE then allow multiple        writers. Note that ALTER TABLE uses a TL_WRITE_ALLOW_READ        < TL_WRITE_CONCURRENT_INSERT.        We especially allow multiple writers if MySQL is at the        start of a stored procedure call (SQLCOM_CALL) or a        stored function call (MySQL does have in_lock_tables        TRUE there). */        if ((lock_type >= TL_WRITE_CONCURRENT_INSERT             && lock_type <= TL_WRITE)            && !(in_lock_tables             && sql_command == SQLCOM_LOCK_TABLES)            && !thd_tablespace_op(thd)            && sql_command != SQLCOM_TRUNCATE            && sql_command != SQLCOM_OPTIMIZE            && sql_command != SQLCOM_CREATE_TABLE) {            lock_type = TL_WRITE_ALLOW_WRITE;        }        /* In queries of type INSERT INTO t1 SELECT ... FROM t2 ...        MySQL would use the lock TL_READ_NO_INSERT on t2, and that        would conflict with TL_WRITE_ALLOW_WRITE, blocking all inserts        to t2. Convert the lock to a normal read lock to allow        concurrent inserts to t2.        We especially allow concurrent inserts if MySQL is at the        start of a stored procedure call (SQLCOM_CALL)        (MySQL does have thd_in_lock_tables() TRUE there). */        if (lock_type == TL_READ_NO_INSERT            && sql_command != SQLCOM_LOCK_TABLES) {            lock_type = TL_READ;        }        lock.type = lock_type;    }*to++= &lock;}

锁详情可参考:
MySQL 5.7: 为innodb引擎彻底移除thr_lock

WL#6671: Improve scalability by not using thr_lock.c locks for InnoDB tables

参考资料

1. MySQL锁系列2 表锁
2. MySQL数据结构分析—THR_LOCK
3. MySQL的表级别线程锁机制
4. Mysql+Innodb源代码调试跟踪分析+何登成
5. MySQL 5.7: 为innodb引擎彻底移除thr_lock
6. WL#6671: Improve scalability by not using thr_lock.c locks for InnoDB tables