MySQL锁:01.总览




  • 锁如何发生,如何解除。

  • 了解死锁的发生,和解决。

  • 为什么数据库要有锁?作用和影响是什么?没有锁会怎样?

  • MySQL里都有什么锁?

    • MyISAM锁
    • InnoDB锁
      • 锁类型
      • InnoDB锁实现
      • InnoDB锁案例
      • InnoDB死锁
      • InnoDB锁优化
    • 锁状态监控

锁总览

锁的作用

避免并发请求时对同一个数据对象同时修改,导致数据不一致。

加锁流程

  1. 事务T1在对某个数据对象R1操作之前,先向系统发出请求,对其加锁L1
  2. 之后,事务T1对该数据对象R1有了相应的控制,在T1释放L1之前,其它事务不能修改R1

锁对数据库的影响

锁等待

  • 锁L1锁定某个对象R1,锁L2等待该锁释放,如果不释放,会一直等待,或者达到系统预设的超时阈值后报错,整个事务回滚,或只回滚当前SQL。

    • 可以配置参数,行锁超时后事务会被回滚。

      mysql> show global variables like '%rollback%';
      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | innodb_rollback_on_timeout | OFF   |
      | innodb_rollback_segments   | 128   |
      +----------------------------+-------+
      2 rows in set (0.00 sec)
      

死锁

  • 锁资源请求产生了回路,如:L1等待L2释放,L2等待L3释放,L3等待L1释放,死循环。

锁类型

  • 排他锁(X)
  • 共享锁(S)

锁范围

  • 全局锁(global lock)instance级别

  • 表级锁(table lock)

  • 行级锁(row lock)

  • 还有mutex,不属于锁,InnoDB内部保护机制,不可控,无法手动解锁,只能调整参数优化。

锁方式

  • 悲观锁

    • “不信任”其它事务,为了以防万一,总是先对数据对象加锁。

    • 事先不加锁,冲突检测过程中才加锁。(就是当前加锁后,并未真正锁,当另一个session尝试锁或其它行为时发生冲突检测,才察觉到加锁了。如同用书占座,只有当其他人想坐过来的时候,帮占座的人才会出面提出该座位已经被占用(被锁))

      select .. for update/for share 就是悲观锁。

  • 乐观锁

    • 不急着加锁,而是等到真的需要进行更新漏了,才再次查询并更新数据。

    • 提前加锁。MGR、PXC先在本节点加锁更新,再广播出去——如果其它节点加锁失败,那么本节点再回滚回去。

      • MGR、PXC用的就是乐观锁。

全局锁

全局读锁

  • global read lock 全局读锁
  • 一般是因为mysqldump、xtrabackup等备份请求发起
  • FTWRL,关闭instance下所有表,并加上全局读锁,防止被修改,直到提交unlock tables;
  • (实例中可能有不同的表引擎,在发起整个实例备份前,先将表关闭一下, 避免有其它会话正在修改表结构。关闭再打开并加全局读锁,防止表被DDL修改。)
  • 若用mysqldum之备份InnoDB无需记录binlog时,可以只加--single-transaction,不加--master-data选项。传送门:一致性备份:所有的表状态都在一个时间点。
  • xtrabackup时可以分开备份InnoDB和MyISAM。
  • 全局读锁时,任何innodb事务不能被提交。
  • 如果都是InnoDB表,可以无需锁表在线热备。

全局QC锁

QC已经永远说再见了。

  • 全局query cache锁(mutex)。最好关闭query cache
  • 对QC中的数据有更新时都会引发query cache lock,对应状态为:waiting for query cache lock
  • 关闭QC:query_cache_type=0 , query_cache_size=0

QC锁存在的问题:

  • 全局任何表数据的更新都会造成请求全局QC锁。
  • 当发起请求时,会将sql及结果放入qc,下次执行同样sql会从qc返回结果。
  • 但是两次执行之间如果发生数据修改,需要更新qc的时候,MySQL简单粗暴的处理方式:只要表数据更新,就会更新qc里关于这个表的所有的cache。所以只要表发生更新,就会请求全局qc这个排他锁,当实例有大量并发更新时,qc就会发生很严重的锁等待。

备份锁 backup lock

  • 8.0新增功能,为了保证备份一致性,需要backup_admin角色权限
  • 发起备份前,执行lock instance for backup,备份结束后执行unlock instance解锁
  • backup lock的作用时备份期间依旧允许DML操作,以及session级别的DDL操作,例如生成临时表。
    (但是建表、改表、删表、repair、truncate、optimize等都被禁止!)
  • 多个会话可以并行持有该锁。
  • 备份锁:降低全局读锁的影响。8.0开始所有数据都用innodb存储,发起备份锁便可以一致性备份。

A backup lock acquired by LOCK INSTANCE FOR BACKUP is independent of transactional locks and locks

taken by FLUSH TABLES tbl_name [, tbl_name****] ... WITH READ LOCK, and the following sequences of statements are permitted:

LOCK INSTANCE FOR BACKUP;
FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
UNLOCK TABLES;
UNLOCK INSTANCE;

FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK;
LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;
UNLOCK TABLES;

(但是建表、改表、删表、repair、truncate、optimize等都被禁止!)

mysql1> lock instance for backup;
Query OK, 0 rows affected (0.00 sec)

mysql3> select  * from metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-----------
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE    
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-----------
| BACKUP LOCK | NULL               | NULL           | NULL        |       139618985816432 | SHARED      | EXPLICIT      | GRANTED     | sql_backup
| TABLE       | performance_schema | metadata_locks | NULL        |       139619055733920 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-----------
2 rows in set (0.00 sec)

mysql2> truncate table k0;
--hang

mysql3> select  * from metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+---
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SO
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+---
| BACKUP LOCK | NULL               | NULL           | NULL        |       139618985816432 | SHARED              | EXPLICIT      | GRANTED     | sq
| SCHEMA      | kk                 | NULL           | NULL        |       139618851698880 | INTENTION_EXCLUSIVE | EXPLICIT      | GRANTED     | dd
| GLOBAL      | NULL               | NULL           | NULL        |       139618851437968 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sq
| BACKUP LOCK | NULL               | NULL           | NULL        |       139618851457584 | INTENTION_EXCLUSIVE | TRANSACTION   | PENDING     | sq
| TABLE       | performance_schema | metadata_locks | NULL        |       139619055733920 | SHARED_READ         | TRANSACTION   | GRANTED     | sq
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+---
5 rows in set (0.00 sec)


mysql1> lock instance for backup;
Query OK, 0 rows affected (0.00 sec)

mysql1> truncate table k0;    --- 但是session1 自己却能DDL
Query OK, 0 rows affected (0.84 sec)

作为对比,对比一下FTWRL时:当前session也会被阻塞

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

mysql1> truncate table k0; 
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock 

MDL锁

meta data lock

  • 5.5开始引入
  • 备份时防止对象被修改
  • tablesspace/schema、表、function/procedure/trigger/event等多种对象上加的锁
  • 事务开启后,会锁定表的meta data,其它会话对表有DDL操作时,均需等待MDL释放后方可继续。
  • 锁超时阈值定义参数:lock_wait_timeout,默认锁等待一年……强烈建议调低。避免加MDL锁忘记释放后导致超长阻塞。
  • 即便是只读查询、只读事务,也要尽快结束以释放MDL

MDL锁类型

INTENTION_EXCLUSIVE 意向排他锁,只用于范围上锁,例如lock table write.
SHARED 共享锁,用于访问字典对象,而不访问数据,例如 create table a like b
SHARED_HIGH_PRIO 只访问字典对象,例如 desc table_a
SHARED_READ 共享读锁,用于读取数据,如事务中的select rows
SHARED_WRITE 共享写锁,用于修改数据,如事务中的update rows
SHARED_NO_WRITE 共享非写锁,允许读取数据,阻塞其它TX修改数据,用在ALTER TABLE第一阶段
SHARED_NO_READ_WRITE 用于访问字典,读写数据,阻塞其它TX读写数据,例如lock table write
SHARED_READ_ONLY 只读锁,常见于lock table x read
EXCLUSIVE 排他锁,可以修改字典和数据,例如alter table

MDL锁的兼容性

IX S SH SR SW SNW SNRW X
IX
S ×
SH ×
SR × ×
SW × × ×
SNW × × × ×
SNRW × × × × ×
X × × × × × × ×

确认MDL锁源头以及消除方法

上锁后便可以在performance_schema.metadata_locks 查询到。
上锁后发生锁等待时,可以在sys.schema_table_lock_waits 查询。
有没有锁,和有没有锁等待是两个不同的概念。

  • 启用MDL的P_S统计

    • p_s.setup_consumers.global_instrumentation
    • p_s.setup_instruments.wait/lock/metadata/sql/mdl

    打开可以动态打开,立即生效。
    但是关闭的话,只有新的session才能有效果(等待亲测)

  • 查询MDL锁状态信息:

    • show proesslist;
    • select * from performance_schema.metadata_locksG
  • 通过metadata_locks查看MDL锁事件

    mysql1> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'G
    Empty set (0.00 sec)
    
    mysql2> select *,sleep(1000) from k1 limit 2;
    
    mysql1> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'G
    *************************** 1. row ***************************
              OBJECT_TYPE: TABLE
            OBJECT_SCHEMA: kk
              OBJECT_NAME: k1
              COLUMN_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 139618985616624   #是μs
                LOCK_TYPE: SHARED_READ
            LOCK_DURATION: TRANSACTION
              LOCK_STATUS: GRANTED
                   SOURCE: sql_parse.cc:6052
          OWNER_THREAD_ID: 52
           OWNER_EVENT_ID: 54
    1 row in set (0.00 sec)
    
    mysql3> select *,sleep(100) from k1 limit 2;
    
    mysql1> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'G
    *************************** 1. row ***************************
              OBJECT_TYPE: TABLE
            OBJECT_SCHEMA: kk
              OBJECT_NAME: k1
              COLUMN_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 139618985616624
                LOCK_TYPE: SHARED_READ
            LOCK_DURATION: TRANSACTION
              LOCK_STATUS: GRANTED
                   SOURCE: sql_parse.cc:6052
          OWNER_THREAD_ID: 52
           OWNER_EVENT_ID: 56
    *************************** 2. row ***************************
              OBJECT_TYPE: TABLE
            OBJECT_SCHEMA: kk
              OBJECT_NAME: k1
              COLUMN_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 139619052075760
                LOCK_TYPE: SHARED_READ
            LOCK_DURATION: TRANSACTION
              LOCK_STATUS: GRANTED
                   SOURCE: sql_parse.cc:6052
          OWNER_THREAD_ID: 53
           OWNER_EVENT_ID: 13
    
  • 如何拿着metadata lock视图查到的thread_id 找到对应的process?

    mysql1> show processlist;
    +----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------------+
    | Id | User            | Host      | db   | Command | Time   | State                  | Info                                 |
    +----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------------+
    |  4 | event_scheduler | localhost | NULL | Daemon  | 260212 | Waiting on empty queue | NULL                                 |
    | 12 | root            | localhost | NULL | Query   |      0 | starting               | show processlist                     |
    | 13 | root            | localhost | kk   | Query   |     37 | User sleep             | select *,sleep(1000) from k1 limit 2 |
    | 14 | root            | localhost | kk   | Query   |     27 | User sleep             | select *,sleep(100) from k1 limit 2  |
    +----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------------+
    4 rows in set (0.00 sec)
    
    mysql1> select * from performance_schema.threads where PROCESSLIST_ID in (13,14);
    +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
    | THREAD_ID | NAME                      | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO                     | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP |
    +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
    |        52 | thread/sql/one_connection | FOREGROUND |             13 | root             | localhost        | kk             | Query               |              149 | User sleep        | select *,sleep(1000) from k1 limit 2 |             NULL | NULL | YES          | YES     | Socket          |          691 | USR_default    |
    |        53 | thread/sql/one_connection | FOREGROUND |             14 | root             | localhost        | kk             | Query               |              139 | User sleep        | select *,sleep(100) from k1 limit 2  |             NULL | NULL | YES          | YES     | Socket          |          693 | USR_default    |
    +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+--------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
    
    
    mysql2> begin ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql2> update k1 set id=22 where id=2;
    Query OK, 1793 rows affected (0.01 sec)
    Rows matched: 1793  Changed: 1793  Warnings: 0
    
    mysql3> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql3> update k1 set id=22 where id=2;
    
    
    mysql> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'G
    *************************** 1. row ***************************
              OBJECT_TYPE: TABLE
            OBJECT_SCHEMA: kk
              OBJECT_NAME: k1
              COLUMN_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 139618985616624
                LOCK_TYPE: SHARED_WRITE
            LOCK_DURATION: TRANSACTION
              LOCK_STATUS: GRANTED    #拿到了MDL锁
                   SOURCE: sql_parse.cc:6052
          OWNER_THREAD_ID: 52
           OWNER_EVENT_ID: 66
    *************************** 2. row ***************************
              OBJECT_TYPE: TABLE
            OBJECT_SCHEMA: kk
              OBJECT_NAME: k1
              COLUMN_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 139619052075760
                LOCK_TYPE: SHARED_WRITE
            LOCK_DURATION: TRANSACTION
              LOCK_STATUS: GRANTED    #拿到了MDL锁
                   SOURCE: sql_parse.cc:6052
          OWNER_THREAD_ID: 53
           OWNER_EVENT_ID: 19
    2 rows in set (0.00 sec)
    
    mysql> show processlist;
    +----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------+
    | Id | User            | Host      | db   | Command | Time   | State                  | Info                           |
    +----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------+
    |  4 | event_scheduler | localhost | NULL | Daemon  | 261969 | Waiting on empty queue | NULL                           |
    | 12 | root            | localhost | NULL | Query   |      0 | starting               | show processlist               |
    | 13 | root            | localhost | kk   | Sleep   |    277 |                        | NULL                           |
    | 14 | root            | localhost | kk   | Query   |     14 | updating               | update k1 set id=22 where id=2 |
    +----+-----------------+-----------+------+---------+--------+------------------------+--------------------------------+
    4 rows in set (0.00 sec)
    
    mysql> select * from sys.schema_table_lock_waits;
    Empty set (0.01 sec)
    
    
  • 为什么都拿到了MDL锁?
    通过前面的MDL锁兼容性表格可知,SW和SW可以兼容并存的。
    理解一下可以得知:行锁虽然被阻塞,但是更新所需的MDL锁是拿到的——允许同时加(获取)shared_write锁, 只不过在等待行锁而已。

只有对表进行表级别锁时,才会互斥

  • 表级别锁阻塞实验

    mysql2> lock table k1 read;
    Query OK, 0 rows affected (2 min 37.02 sec)
    
    mysql1> select * from performance_schema.metadata_locks where object_schema != 'performance_schema'
    *************************** 1. row ***************************
              OBJECT_TYPE: TABLE
            OBJECT_SCHEMA: kk
              OBJECT_NAME: k1
              COLUMN_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 139618985329072
                LOCK_TYPE: SHARED_READ_ONLY
            LOCK_DURATION: TRANSACTION
              LOCK_STATUS: GRANTED
                   SOURCE: sql_parse.cc:6052
          OWNER_THREAD_ID: 52
           OWNER_EVENT_ID: 69
    1 row in set (0.00 sec)
    
    mysql3> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql3> update k1 set id=22 where id=2;
    --hang住了。
    
    mysql1> mysql> select * from performance_schema.metadata_locks where object_schema != 'performance_sGhema' 
    *************************** 1. row ***************************
              OBJECT_TYPE: TABLE
            OBJECT_SCHEMA: kk
              OBJECT_NAME: k1
              COLUMN_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 139618985329072
                LOCK_TYPE: SHARED_READ_ONLY
            LOCK_DURATION: TRANSACTION
              LOCK_STATUS: GRANTED
                   SOURCE: sql_parse.cc:6052
          OWNER_THREAD_ID: 52
           OWNER_EVENT_ID: 69
    *************************** 2. row ***************************
              OBJECT_TYPE: TABLE
            OBJECT_SCHEMA: kk
              OBJECT_NAME: k1
              COLUMN_NAME: NULL
    OBJECT_INSTANCE_BEGIN: 139619052222560
                LOCK_TYPE: SHARED_WRITE
            LOCK_DURATION: TRANSACTION
              LOCK_STATUS: PENDING # 加SW锁被阻塞
                   SOURCE: sql_parse.cc:6052
          OWNER_THREAD_ID: 53
           OWNER_EVENT_ID: 28
    2 rows in set (0.00 sec)
    
    
  • 查询锁等待信息,利用sys schema查询MDL等待信息

    mysql> select * from sys.schema_table_lock_waitsG
    *************************** 1. row ***************************
                   object_schema: kk
                     object_name: k1
               waiting_thread_id: 53
                     waiting_pid: 14
                 waiting_account: root@localhost
               waiting_lock_type: SHARED_WRITE
           waiting_lock_duration: TRANSACTION
                   waiting_query: update k1 set id=22 where id=2
              waiting_query_secs: 56
     waiting_query_rows_affected: 0
     waiting_query_rows_examined: 0
              blocking_thread_id: 52
                    blocking_pid: 13
                blocking_account: root@localhost
              blocking_lock_type: SHARED_READ_ONLY
          blocking_lock_duration: TRANSACTION
         sql_kill_blocking_query: KILL QUERY 13
    sql_kill_blocking_connection: KILL 13
    1 row in set (0.00 sec)
    
    mysql> show processlist;
    +----+-----------------+-----------+------+---------+--------+---------------------------------+--------------------------------+
    | Id | User            | Host      | db   | Command | Time   | State                           | Info                           |
    +----+-----------------+-----------+------+---------+--------+---------------------------------+--------------------------------+
    |  4 | event_scheduler | localhost | NULL | Daemon  | 263713 | Waiting on empty queue          | NULL                           |
    | 12 | root            | localhost | NULL | Query   |      0 | starting                        | show processlist               |
    | 13 | root            | localhost | kk   | Sleep   |    434 |                                 | NULL                           |
    | 14 | root            | localhost | kk   | Query   |    218 | Waiting for table metadata lock | update k1 set id=22 where id=2 |
    +----+-----------------+-----------+------+---------+--------+---------------------------------+--------------------------------+
    4 rows in set (0.00 sec)
    
    

  • 由此可知,shared_writeshared_read_only互斥。

  • 由查询结果可知,thread 52 阻塞了thread 53的请求,并且给出了处理办法:

    • kill query 只能将id里当前在跑的sql中止在这个实验里, kill query 是没用的——show processlist看到,pid 13当前没有语句进行。

    • 只有 kill 13才有效。

    • 当然了, 也可以在13中结束事务,释放锁:

      mysql2> unlock tables; 
      Query OK, 0 rows affected (0.00 sec) 
      
      mysql3> update k1 set id=22 where id=2; 
      Query OK, 0 rows affected (5 min 40.74 sec) 
      Rows matched: 0  Changed: 0 Warnings: 0 
      

表锁

表锁一般在server层面实现。
InnoDB表还有IS/IX表级锁,以及auto-inc锁。

读锁

lock table t1 read;

  • 持有读锁的会话可以读表,但是不能写表。
  • 允许多个会话同时持有读锁

写锁

lock table t1 write

  • 持有写锁的会话既可以读表,也可以写表
  • 只有持有写锁的会话才可以读写该表
  • 其它会话访问该表或者请求加锁都会被阻塞,直到锁释放。

加锁

  • lock table t1 read;
    • 多个session都可以对一个表加读锁
  • lock table t1 write
    • 也许需要先解除之前上的读锁。(自己试一下)
    • 写锁是X锁,同一时间一个表只能被一个session加写锁。

释放锁

MySQL解除表级锁目前是解除全部表级锁,无法单独解除某个锁。(help get_lock 好像可以,自己研究一下。)

  • unlock tables;
  • 断开连接或者被kill
  • 【错误,需要验证版本】执行lock table
  • 【错误,需要验证版本】显示开启一个事务

MyISAM锁

  • 默认是表锁,读写互斥,仅只读共享
  • 读锁,lock table xxx read,自身只读,不能写。其它线程仍可读,不能写。多个线程都可提交read lock
  • 写锁,lock table xxx write,自身可读写,其它线程完全不可读写。
  • 写锁优先级高于读锁
  • select自动加读锁(共享锁)
  • 其它DML、DDL自动加写锁(排他锁)
  • 释放锁见上文。

放弃MyISAM吧,都是表锁。

原文地址:https://www.cnblogs.com/konggg/p/14133916.html