MySQL(Lock)

2019独角兽企业重金招聘Python工程师标准>>>

锁表语法

  LOCK TABLES
      tbl_name [[AS] alias] lock_type
      [, tbl_name [[AS] alias] lock_type] ...

  lock_type:
      READ [LOCAL]
    | [LOW_PRIORITY] WRITE

锁语句说明

lock tables tb_name read
读锁定: 如果一个线程获得在一个表上的read锁，那么该线程和所有其他线程只能从表中读数据，不能进行任何写操作

lock tables tb_name write
写锁定: 如果一个线程在一个表上得到一个   WRITE   锁，那么只有拥有这个锁的线程可以从表中读取和写表。其它的线程被阻塞

unlock tables
释放当前线程所有锁

事务隔离级别

  Read Uncommit　允许脏读, 也就是说本事务中可能读到其他事务中未提交的修改数据.
  Read Commit　只能读到已经提交的数据. Oracle等多数数据库默认都是该级别(不可重复读).
  Repeatable Read　可重复读, 在同一个事务内的查询都是事务开始时刻的一致性数据, InnoDB默认的事务隔离级别. 该隔离级别消除了不可重复读, 但是还存在幻想读.
  Serializable　完全串行化的读, 每次读都需要获取表级别的共享锁, 读写相互阻塞.

锁相关变量
  show variables like "%tx%";

  +---------------+-----------------+
  | Variable_name | Value           |
  +---------------+-----------------+
  | tx_isolation  | REPEATABLE-READ |
  | tx_read_only  | OFF             |
  +---------------+-----------------+

  show engine innodb status\G;

transaction-isolation={READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE}

锁的相关表

  information_schema.innodb_lock_waits
  information_schema.innodb_trx

MVCC(多版本并发控制)
 

 基于锁的并发控制机制, 悲观机制;
 基于版本的并发控制机制, 乐观机制;

  读不阻塞写, 写也不阻塞读, 等到提交的时候才检验是否有冲突. 由于没有锁, 所以读写不会相互阻塞, 从而大大提升了并发性能锁的分类
  锁的类型:

    共享锁
    排他锁
    意向共享锁
    意向排他锁

  锁的粒度:

    表锁
    行锁
  （InnoDB存储引擎层实现了行锁(Record Lock), gap lock, next-key lock）

  持的时间:

    Statement 语句执行期间获取的lock, 语句执行结束时自动释放的.
    Transaction 在一个事务中, 此事务所设计到的所有表均要获取MDL, 一直到事务commit/rollback释放.
      两阶段加锁协议:
          事务执行分为两个阶段,
          第一个阶段获得封锁;
          第二个阶段释放封锁;
    Explicit 需要MDL_context::release_lock(), 譬如: lock table; flush tables with read lock;

  MDL的状态

      global read lock
      commit lock
      schema metadata lock
      table metadata lock
      stored function metadata lock
      stored procedure metadata lock
      trigger metadata lock
      event metadata lock

  锁的粒度
    scope锁(优先级: X ->S ->IX (IS和其他类型的锁都是兼容的))

        IS    意向共享锁
        IX    意向排他锁
        S    共享锁
        X    排他锁

   object锁

MDL_INTENTION_EXCLUSIVE	仅适用scoped locks, 可升级IX->X, 和其他IX兼容; 但是和scoped S and X不兼容.
MDL_SHARED	共享锁. 访问字典对象(table metadata), 不能访问数据
MDL_SHARED_HIGH_PRIO	高优先级shared mdl, 不像shared lock那样, 在申请时会忽略X lock的等待; 用于访问metadata(no date), 填充INDORMATION_SCHEMA表. 兼容SNRW
MDL_SHARED_READ	共享读锁. 能读table metadata, 也可读表数据(如select), 譬如: SELECTs, subqueries, and LOCK TABLE ... READ
MDL_SHARED_WRITE	共享写锁. 读取metadata, modify/read table data, (INSERT, UPDATE, DELETE) statements,SELECT ... FOR UPDATE. but not LOCK TABLE ... WRITE or DDL)
MDL_SHARED_UPGRADABLE	alter table第一阶段获取. 读取metadata, modify/read table data(升级到SNW, 获取row_level lock), 可升级SHU->SNW/X.
MDL_SHARED_NO_WRITE	alter table第一阶段获取; 可以并发read table, 但不可以update; 可升级SNW->X.
MDL_SHARED_NO_READ_WRITE	读取表metadata, modify/read table data. 但是阻止对表的读写操作. 用于LOCK TABLES WRITE statement. 可升级SNRW->X, 除S/SH外, 不兼容任何mdl.
MDL_EXCLUSIVE	最高级别MDL锁, 通常用于Drop/Create/Rename等操作. 也用于其它对象创建或者删除时, 譬如: create trigger等

锁的数据结构

      MDL_context: 字典锁上下文. 包含一个事物所有的字典锁请求.
      MDL_request: 字典锁请求. 包含对某个对象的某种锁的请求.
      MDL_ticket: 字典锁排队. MDL_request就是为了获取一个ticket.
      MDL_lock: 锁资源. 一个对象全局唯一, 可以允许多个可以并发的事物同时获得.

锁的流程

  select statement
    opening tables阶段, 加共享读锁
      加MDL_INTENTION_EXCLUSIVE锁
      加MDL_SHARED_READ锁
    commit阶段, 释放MDL锁
      释放MDL_INTENTION_EXCLUSIVE锁
      释放MDL_SHARED_READ锁



  DML statement
    opening tables阶段, 加共享写锁
      加MDL_INTENTION_EXCLUSIVE锁
      加MDL_SHARED_WRITE锁
    commit阶段, 释放MDL锁
      释放MDL_INTENTION_EXCLUSIVE锁
      释放MDL_SHARED_WRITE锁



  DDL(alter table) statement
    opening tables阶段, 加共享写锁
      加MDL_INTENTION_EXCLUSIVE锁
      加MDL_SHARED_UPGRADABLE锁, 升级到MDL_SHARED_NO_WRITE
    读取数据, copy data, 流程:
      create {tmp} table, 定义tmp表结构
      copy data from old table to tmp(new) table
    tmp表替换老表(rename)
      将MDL_SHARED_NO_WRITE升级到MDL_EXCLUSIVE
    commit阶段, 释放MDL锁
      释放MDL_INTENTION_EXCLUSIVE锁
      释放MDL_EXCLUSIVE锁

      
Note

lock tables 命令是为当前线程锁定表, 当前线程关闭时,自动退出封闭空间,释放所有表锁,无论有没有执行 unlock tables
获得锁的过程中，lock table 命令可能会锁定比指定的表以外更多的表。这是因为，如果表中有trigger，那么为了功能能正常进行，在trigger中涉及的表也会被lock

转载于:https://my.oschina.net/igooglezm/blog/884645