扩展：行锁介绍和row lock wait（行锁等待）的处理案例

a.数据库锁定机制就是为了保证数据的一致性，使得各种共享资源在被并发访问时变得有序。

mysql支持三种级别的锁定机制：表级锁定(table-level)，行级锁定(row-level)，页级锁定(page-level)。
1.表级锁定(table-level)
表级锁是mysql中锁粒度最大的锁定机制，一次会将整张表锁定，不会出现死锁问题

2.行级锁定(row-level)
行级锁是锁粒度最小的锁定机制。由于锁粒度最小，所以锁定资源发生的争用也就最小，这样提高了应用程序并发处理的能力同时提高了整个系统的性能，但是每次获取锁和释放锁都要做很多事情，带来的消耗也就增加，也最容易发生死锁。

3.页级锁定(page-level)
页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样
是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

mysql这三种锁的特性可以大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

innodb存储引擎与myisam最大的不同就是：innodb支持事务；采用行级锁
可以通过检查Innodb_row_lock变量来分析系统上行锁的争夺情况：

如果Innodb_row_lock_time_avg和Innodb_row_lock_current_waits两个值偏高，则表示锁争用严重可以通过查询information_schema库中相关的表(INNODB_LOCKS)来查看锁情况。

innodb行级锁的模式和加锁方法
innodb实现了两种模型的行锁：
1.共享锁(S)：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁
2.排他锁(X)：允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享锁和排他锁
innodb同时为了支持表锁和行锁的共存，实现多粒度的锁定机制，还有两种意向锁(intention locks)：
1.意向共享锁(IS):事务打算给数据行加行共享锁，事务在给一个数据行加锁前必须获得该表的IS锁
2.意向排他锁(IX): 事务打算给数据行加行排他锁，事务在给一个数据行加锁前必须获得该表的IX锁
四种锁的兼容情况如图：

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁（S）：SELECT … LOCK IN SHARE MODE
排他锁（X)：SELECT … FOR UPDATE

innodb行锁的实现方式
innodb行锁是通过给索引上的索引项加锁来实现的，如果没有索引，innodb将通过隐藏的聚簇索引来对记录加锁。innodb的行锁分为三种： Record lock、Gap lock、Next-Key Locks
Record lock
单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个
隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁
Gap lock
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
Next-Key Locks
在默认情况下，mysql的事务隔离级别是可重复读，并且innodb_locks_unsafe_for_binlog参数为0，这时默认采用next-key locks，所谓Next-Key Locks，就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。
例如：某普通索引列当前值有：1, 10, 20，那么此时它的防插入锁区间分别是：
(-∞, 1], (1, 10], (10, 20], (20, +∞)

注意：innodb通过范围条件加锁时使用next-key锁外，如果使用相等条件请求给一个不存在的记录加锁，innodb会使用next-key锁。
下列SQL语句自带的行锁级别为：
insert——记录锁、update——防插入锁、delete——防插入锁
此外，若查询的列包含唯一索引或主键，则行锁将被自动降级到记录锁
（1）在不通过索引条件查询时，innodb会锁定表中的所有记录
（2）mysql的行锁是针对索引加的锁，所以虽然是访问不同行的记录，如果使用相同的索引键，是会出现锁冲突的
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引，唯一索引或普通索引，innodb都会使用行锁来对数据加锁
（4）即使在条件中使用了索引字段，但是否使用索引来检索数据是由mysql通过判断不同计划的代价来决定的，如果mysql认为全表扫描效率更高，它就不会使用索引，这种情况下innodb会对所有记录加锁。

b. 行锁监控及分析

# 查询锁等待详细信息
select * from sys.innodb_lock_waits;   ----> blocking_pid(锁源的连接线程)

# 通过连接线程找SQL线程
select * from performance_schema.threads;

# 通过SQL线程找到 SQL语句
select * from performance_schema.events_statements_history;

------行锁等待的触发
模拟问题：
session1;
begin；
select * from city where id<10 for update;
但没提交

session2;
begin;
select * from city where id<100 for update;
会发现会话会被堵塞，发生锁等待

解决步骤：
 
1.查看进程等待的信息

show processlist；

2.查看有没有锁等待的问题（查询锁等待详细信息）

mysql> select * from sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2021-01-09 17:58:25
                    wait_age: 00:00:07
               wait_age_secs: 7
                locked_table: `world`.`city`
         locked_table_schema: world
           locked_table_name: city
      locked_table_partition: NULL
   locked_table_subpartition: NULL
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 4180
         waiting_trx_started: 2021-01-09 17:55:17
             waiting_trx_age: 00:03:15
     waiting_trx_rows_locked: 2
   waiting_trx_rows_modified: 0
                 waiting_pid: 28     processlist_id是多少
               waiting_query: select * from world.city where id<100 for update   在等待的语句是谁
             waiting_lock_id: 140235353380712:3:6:2:140235245710760
           waiting_lock_mode: X
             blocking_trx_id: 4179
                blocking_pid: 27   阻塞别人的id
              blocking_query: NULL
            blocking_lock_id: 140235353379864:3:6:2:140235245704320
          blocking_lock_mode: X
        blocking_trx_started: 2021-01-09 17:53:55
            blocking_trx_age: 00:04:37
    blocking_trx_rows_locked: 10
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 27    处理方案 
sql_kill_blocking_connection: KILL 27
1 row in set (0.00 sec)

如果阻塞的是大事务，要慎重处理评估，如果kill的话，也会执行很长时间，kill会回滚，此时只能等待----->事后处理优化的是建立索引

************************************************************
查询什么语句堵塞的和谁堵塞的
3.# 通过连接线程processlist_id找SQL线程，找到THREAD_ID
mysql> select * from performance_schema.threads;
*************************** 47. row ***************************
          THREAD_ID: 67
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 27
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 761
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: select * from world.city where id<10 for update
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 6362
     RESOURCE_GROUP: USR_default

4.# 通过SQL线程THREAD_ID找到 SQL语句

mysql> select * from performance_schema.events_statements_history where THREAD_ID=67\G
*************************** 3. row ***************************
              THREAD_ID: 67
               EVENT_ID: 4
           END_EVENT_ID: 4
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:95
            TIMER_START: 2193745710919302000
              TIMER_END: 2193745711648731000
             TIMER_WAIT: 729429000
              LOCK_TIME: 373000000
               SQL_TEXT: select * from world.city where id<10 for update
                 DIGEST: 99612e3115aa3004e5ee69c105630eb2a596b42133f2690966c015a64caa93bd
            DIGEST_TEXT: SELECT * FROM `world` . `city` WHERE `id` < ? FOR UPDATE
       NESTING_EVENT_ID: 3
     NESTING_EVENT_TYPE: TRANSACTION
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 6301
3 rows in set (0.00 sec)