MySQL的锁 – Z不知

InnoDB的锁机制

InnoDB存储引擎即支持行级锁也支持表级锁，默认情况下会采用行级锁。

行级锁

共享（S）锁

Share，又叫读锁

S锁允许持有该锁的事务读取行

 LOCK IN SHARE MODE

数据行被加了共享锁后，其他事务可以读，但要想申请排它锁就会被阻塞

排它（X）锁

eXclusive，又叫写锁

X锁允许持有该锁的事务更新或删除行

 FOR UPDATE

数据行被加了排它锁后，其他事务就不能在被锁定的行上加任何锁了，要不然会被阻塞

表级锁

在锁机制的实现过程中，为了让行级锁定和表级锁定共存，InnoDB使用了意向锁的概念，意向锁就是表级锁，目的是为了防止DDL和DML的并发问题。

意向共享锁

IS Lock

事务想要获得一张表中的某几行的共享锁，即事务有意向去给一张表中的几行加S锁。

意向排它锁

LX Lock

事务想要获得一张表中的某几行的排他锁，即事务有意向去给一张表中的几行加X锁。

意向锁存在的意义和元数据锁（metadata lock，MDL）很相似。

DDL和DML并发的问题

SESSION1	SESSION2
begin
select * from xxx;
	Drop table xxx; // 阻塞
select * from xxx;

如果没有保障措施，会话2会直接执行drop语句，会话1再执行select就会出错。

意向锁和元数据锁类似，就是为了防止在事务进行DDL语句的操作而导致数据不一致。

意向锁是当我们有意向给一张表的几行数据加S读锁时，它会给表加意向锁防止DDL的发生。

MySQL在5.5.3引入了元数据锁，在MySQL5.7的版本中对其实现方式算法进行了彻底优化，解决了Server层表锁的性能问题。

行锁的范围

对数据上锁的锁定范围不同，行级锁又分为下面三种：

记录锁

Record Locks，单行记录的锁，锁定索引中的一条记录，比如id=1的记录。

区间锁

（Gap Locks，又称间隙锁）
锁定一个范围。区间锁是锁定索引记录之间的区间，或锁定在第一个或最后一个索引几率之前的区间。

Next-key Lock

锁定一个范围的记录并包含记录本身。Next-key Lock是记录锁和区间锁的组合；当InnoDB扫描索引记录时，会先对选中的索引记录加上记录锁，再对索引记录两边的区间加上区间锁。

InnoDB的行级锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据才使用。在不通过索引条件检索数据时，InnoDB使用的是表级锁。
Repeatable-Read这个事务级别，为了避免幻读现象引入了区间锁，例如：

表中score字段上有索引，第一个事务中查询表score < 80 lock in share mode，第二个事务插入 score=70会被阻塞，因为score<80的区间内不允许有任何数据插入，区间锁的功能得到体现。

表锁和行锁哪个执行的更快？参考：https://blog.csdn.net/persistencegoing/article/details/94441863

锁等待

锁等待是指一个事务过程中产生的锁，其他事务需要等待上一个事务释放它的锁才能占用该资源。如果改事务一直不释放，就需要一直等下去直到超过锁等待时间，会报一个等待超时的的错误。

查看锁等待超时时间

 > show variables like '%innodb_lock_wait%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | innodb_lock_wait_timeout | 50    |    # 单位秒
 +--------------------------+-------+

查看锁等待情况

 > select * from sys.innodb_lock_waits \G;
 
 *************************** 1. row ***************************
                 wait_started: 2022-04-07 14:38:36
                     wait_age: 00:00:02
                wait_age_secs: 2
                 locked_table: `test`.`test`
          locked_table_schema: test
            locked_table_name: test
       locked_table_partition: NULL
    locked_table_subpartition: NULL
                 locked_index: PRIMARY
                  locked_type: RECORD
               waiting_trx_id: 931705
          waiting_trx_started: 2022-04-06 21:33:29
              waiting_trx_age: 17:05:09
      waiting_trx_rows_locked: 4
    waiting_trx_rows_modified: 0
                  waiting_pid: 11        # 等待事务的线程pid
                waiting_query: update test set score=2 where name='bz'   # 等待锁释放的sql语句
              waiting_lock_id: 140201002638688:31:4:23:140201213453856
            waiting_lock_mode: X
              blocking_trx_id: 931704
                 blocking_pid: 10        # 阻塞事务的线程pid
               blocking_query: NULL      # 阻塞事务的sql语句
             blocking_lock_id: 140201002637840:31:4:23:140201213448216
           blocking_lock_mode: X
         blocking_trx_started: 2022-04-06 21:33:22
             blocking_trx_age: 17:05:16
     blocking_trx_rows_locked: 8
   blocking_trx_rows_modified: 1
      sql_kill_blocking_query: KILL QUERY 10
 sql_kill_blocking_connection: KILL 10
 1 row in set (0.00 sec)
 
 ERROR:
 No query specified
 
 > show full processlist\G;
 *************************** 1. row ***************************
      Id: 4
    User: event_scheduler
    Host: localhost
      db: NULL
 Command: Daemon
    Time: 103411
   State: Waiting on empty queue
    Info: NULL
 *************************** 2. row ***************************
      Id: 10                 # 阻塞事务的线程pid，上一个语句结果中的 blocking_pid
    User: root
    Host: localhost:58010
      db: test
 Command: Sleep
    Time: 448
   State:
    Info: NULL
 *************************** 3. row ***************************
      Id: 11                 # 等待事务的线程pid，上一个语句结果中的 waiting_pid
    User: root
    Host: localhost:58014
      db: test
 Command: Query
    Time: 2
   State: updating
    Info: update test set score=2 where name='bz'
 *************************** 4. row ***************************
      Id: 12
    User: root
    Host: localhost:65479
      db: NULL
 Command: Query
    Time: 0
   State: starting
    Info: show full processlist
 4 rows in set (0.00 sec)
 
 ERROR:
 No query specified

通过上面的输出，发现pid=10锁住了表，造成pid=11的线程等待，但是pid=10的阻塞事务对应的sql是NULL。可以通过以下语句获取对应的阻塞语句。

 select SQL_TEXT from performance_schema.events_statements_current where THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=10);
 
 +-----------------------------------------+
 | SQL_TEXT                                |
 +-----------------------------------------+
 | update test set score=3 where name='wk' |
 +-----------------------------------------+

死锁

在MySQL中，两个或两个以上的事务相互持有和请求锁，并行程一个循环的依赖关系，就会产生死锁，也就是锁资源请求产生了死循环现象。

InnoDB会自动检测事务死锁，立即回滚其中某个事务，并且返回一个错误。

根据某种机制来选择那个最简单（代价最小）的事务进行回滚。

死锁报错：ERROR 1213(40001):deadlock found when trying to get lock; try restarting transaction

死锁记录日志

MySQL5.6版本之前，只有最新的死锁信息可以使用 show engine innodb status 命令查看。使用 Percona Toolkit 工具包中的 pt-deadlock-logger 可以从 show engine innodb status 的结果中得到指定的时间范围内的死锁信息，同时写入文件或者表中，等待后续分析。

 show engine innodb status;

MySQL5.6以上版本，可以启用参数 innodb_print_all_deadlocks,可以把所有发生的死锁信息都记录到错误日志里。

 set global innodb_print_all_deadlocks = 1;

避免死锁

不同程序会并发存取多个表或涉及多行记录时，尽量约定以相同的顺序访问表，可以降低死锁的机会。

对应用程序进行调整，在某些情况下，通过把大事务分解成多个小事务，使得锁能够更快被释放，及时提交或者回滚事务，可以减少死锁发生的概率。

在同一个事务中，尽可能做到一次性锁定所需要的资源，减少死锁产生的概率。

为表添加合理的索引，不用索引将会为表的每一行记录添加上锁，死锁的概率则会加大。

对于非常容易产生死锁的业务部分，可以尝试升级锁粒度，通过表锁定来减少死锁产生的概率。

监控

查看所有sql的状态

 show full processlist\G;
 # 可以查看当前MySQL是否有压力、在跑什么语句、当前语句耗时多久、多少链接数、那些线程有问题（有问题的线程可以kill掉，临时解决突发问题）。

 *************************** 1. row ***************************
      Id: 4
    User: event_scheduler
    Host: localhost
      db: NULL
 Command: Daemon
    Time: 105919
   State: Waiting on empty queue
    Info: NULL
 *************************** 2. row ***************************
      Id: 10
    User: root
    Host: localhost:58010
      db: test
 Command: Sleep
    Time: 2956
   State:
    Info: NULL

查看是否存在锁表情况

 show engine innodb status\G;

 *************************** 1. row ***************************
   Type: InnoDB
   Name:
 Status:
 =====================================
 2022-04-07 15:29:00 0x70000f822000 INNODB MONITOR OUTPUT
 =====================================
 Per second averages calculated from the last 15 seconds
 -----------------
 BACKGROUND THREAD
 -----------------
 srv_master_thread loops: 24 srv_active, 0 srv_shutdown, 52070 srv_idle
 srv_master_thread log flush and writes: 0
 ----------
 SEMAPHORES
 ----------
 OS WAIT ARRAY INFO: reservation count 2
 OS WAIT ARRAY INFO: signal count 4
 RW-shared spins 2, rounds 2, OS waits 1
 RW-excl spins 4, rounds 34, OS waits 1
 RW-sx spins 0, rounds 0, OS waits 0
 Spin rounds per wait: 1.00 RW-shared, 8.50 RW-excl, 0.00 RW-sx
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 931711
 Purge done for trx's n:o < 931711 undo n:o < 0 state: running but idle
 History list length 36
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 421675979350192, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 421675979347648, not started
 0 lock struct(s), heap size 1136, 0 row lock(s)
 ---TRANSACTION 931704, ACTIVE 64538 sec
 2 lock struct(s), heap size 1136, 8 row lock(s), undo log entries 1
 MySQL thread id 10, OS thread handle 123145561890816, query id 225 localhost 127.0.0.1 root
 --------
 FILE I/O
 --------
 I/O thread 0 state: waiting for i/o request (insert buffer thread)
 I/O thread 1 state: waiting for i/o request (log thread)
 I/O thread 2 state: waiting for i/o request (read thread)
 I/O thread 3 state: waiting for i/o request (read thread)
 I/O thread 4 state: waiting for i/o request (read thread)
 I/O thread 5 state: waiting for i/o request (read thread)
 I/O thread 6 state: waiting for i/o request (write thread)
 I/O thread 7 state: waiting for i/o request (write thread)
 I/O thread 8 state: waiting for i/o request (write thread)
 I/O thread 9 state: waiting for i/o request (write thread)
 Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
  ibuf aio reads:, log i/o's:, sync i/o's:
 Pending flushes (fsync) log: 0; buffer pool: 0
 1181 OS file reads, 946 OS file writes, 387 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
 -------------------------------------
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -------------------------------------
 Ibuf: size 1, free list len 0, seg size 2, 0 merges
 merged operations:
  insert 0, delete mark 0, delete 0
 discarded operations:
  insert 0, delete mark 0, delete 0
 Hash table size 34679, node heap has 2 buffer(s)
 Hash table size 34679, node heap has 1 buffer(s)
 Hash table size 34679, node heap has 0 buffer(s)
 Hash table size 34679, node heap has 0 buffer(s)
 Hash table size 34679, node heap has 0 buffer(s)
 Hash table size 34679, node heap has 0 buffer(s)
 Hash table size 34679, node heap has 1 buffer(s)
 Hash table size 34679, node heap has 4 buffer(s)
 0.00 hash searches/s, 0.00 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number          1074083819
 Log buffer assigned up to    1074083819
 Log buffer completed up to   1074083819
 Log written up to            1074083819
 Log flushed up to            1074083819
 Added dirty pages up to      1074083819
 Pages flushed up to          1074083819
 Last checkpoint at           1074083819
 225 log i/o's done, 0.00 log i/o's/second
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total large memory allocated 137363456
 Dictionary memory allocated 585873
 Buffer pool size   8192
 Free buffers       6875
 Database pages     1309
 Old database pages 503
 Modified db pages  0
 Pending reads      0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 2, not young 0
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 1154, created 155, written 607
 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 No buffer pool page gets since the last printout
 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
 LRU len: 1309, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 0 read views open inside InnoDB
 Process ID=84630, Main thread ID=0x70000f0f1000 , state=sleeping
 Number of rows inserted 76, updated 347, deleted 52, read 7257
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================

MySQL将事务和锁信息记录在 information_schema 数据库中

涉及到的表主要有三个：innodb_trx、innodb_locks、innodb_lock_waits

表：innodb_trx

trx_id：唯一的事务ID号
trx_state：当前事务的状态（LOCK_WAIT 锁等待、... ... ）
trx_wait_started：事务开始等待时间
trx_mysql_thread_id：线程ID，与 show full processlist 相对应
trx_query：事务运行的SQL语句
trx_operation_state：事务运行的状态

表：innodb_locks

事务锁的具体情况，包括事务正在申请的和事务已经加的锁

表：innodb_lock_waits

锁阻塞情况

requesting_trx_id：请求锁的事务ID（等待方）
blocking_trx_id：阻塞该锁的事务ID（持有方，待释放）