锁分类

  1. 从数据操作类型上分类
    1. 读锁：又称共享锁，针对同一份数据，多个读操作可以同时进行而不互相影响。
      1. 在表上加读锁：lock table 表名1 read, 表名2 read;
      2. 加锁之后的影响：
        1. 当前会话可以读被加锁的表，其他会话也可以读被加锁的表
        2. 当前会话不可以读其他未加锁的表（会报错），但其他会话可以读其他未加锁的表
        3. 当前会话不可以操作（增删改）被加锁的表（会报错），其他会话也不可以操作被加锁的表（会阻塞）
      3. 查看锁：show open tables;

mysql> show open tables; In_use 字段若为1，代表该表被加锁
+——————————+———————————————————————————+————+——————-+
| Database | Table | In_use | Name_locked |
+——————————+———————————————————————————+————+——————-+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
| performance_schema | replication_connection_status | 0 | 0 |

  1.   4. 释放锁：unlock tables;
  1. 写锁：又称排他锁，当前操作完成前，会阻塞其他写操作和读操作。
    1. 在表上加写锁：lock table 表名1 write, 表名2 write;
    2. 加锁之后的影响：
      1. 当前会话可以对被加锁表进行增删改查，但其他会话的增删改查操作会被阻塞
      2. 当前会话不可以对其他未加锁的表进行增删改查，但其他会话可以对其他未加锁的表进行增删改查
  2. 总结：
    1. 读锁不会阻塞其他会话（进程）的读操作，但会阻塞它们的写操作，写锁会阻塞其他进程的读操作和写操作。
    2. MyISAM只支持表锁，偏读，是写优先的（总是优先挑选下一个申请写锁的进程执行），因此MyISAM不适合做写为主表的数据库引擎。
    3. 从数据操作粒度上分类：分为表锁和行锁。
  3. 事务的ACID特性中C：一致性consistence，事务从开始到结束，从一个一致性状态转换到另一个一致性状态，那么什么是一致性状态呢？
    1. 一方面，所有的数据库规则（主外键、唯一性、非空）均应用于事务的修改，以保证数据库的完整性。
    2. 一方面，内部数据（B+树、双向链表）必须是正确的。
  4. 并发事务产生的问题：实质是数据一致性的问题
    1. 更新丢失：更新被覆盖了。
    2. 脏读：事务A读取到了事务B已经修改但未提交的数据
    3. 不可重复读：事务A读取到了事务B已提交的数据，发现之前读取的数据不一致或记录已删除
    4. 幻读：事务A按相同条件读取到了事务B新增的数据。
    5. 脏读和幻读的区别：脏读读取到了修改（更新）的数据，幻读读取到了新增（插入）的数据
  5. mysql通过事务隔离解决数据一致性问题，提供了4中隔离级别
    1. 查看当前数据库隔离级别：show variables like ‘%tx_isolation%’;
    2. 读未提交
    3. 读已提交：解决了脏读的问题
    4. 可重复读：解决了脏读、不可重复读的问题，默认隔离级别
    5. 可序列化：解决了脏读、不可重复读、幻读的问题

MySQL锁的相关命令

  1. 表锁：
    1. 加读锁或写锁：lock table 表名1 read(create), 表名2 read(create);
    2. 释放锁：unlock tables;
    3. 查看锁：show open tables; In_use 字段若为1，代表该表被加锁
    4. 分析加锁的性能：show status like ‘table%’;

mysql> show status like ‘table%’;
+——————————————+———-+
| Variable_name | Value |
+——————————————+———-+
| Table_locks_immediate | 115 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 0 |
| Table_open_cache_misses | 0 |
| Table_open_cache_overflows | 0 |
+——————————————+———-+

  1.   1. Table_locks_immediate：可以立即产生表级锁定的次数，每立即获取表锁，该值加1
  2.   1. Table_locks_waited：出现表级锁定争用而发生等待的次数，此值高，代表存在严重的表级争用的情况。
  1. 行锁：只存在于Innodb中
    1. Innodb在事务中当进行update更新操作时自动加上行锁
    2. 手动加行锁：至于加的是读锁还是写锁，有操作（select、update）决定。
      1. 关闭自动提交开启事务
      2. begin;
      3. select * from stu where id = 1 for update;
    3. 查看行锁性能（争夺行锁情况）：show status like ‘%innodb_row_lock%’;
      1. Innodb_row_lock_current_waits：当前阻塞线程数
      2. Innodb_row_lock_time_avg：阻塞平均时间
      3. Innodb_row_lock_waits：总共阻塞（争抢）线程数

image.png


  1. 重要案例演示

  1. 默认可重复读的隔离级别下演示行锁：
    1. 会话A取消自动提交后开启事务，更新id = 1的行，更新成功，并且获取到该行的行锁

image.png

  1. 会话B取消自动提交后开启事务，并且也更新id = 1的行，由于该行被加上了行锁，所以操作被阻塞

image.png

  1. 只有等到会话A手动提交了事务，同时也释放了该行的锁，会话B的更新操作才会被执行。
  2. 但是会话A更新id = 1的行，并给该行加上行锁，而会话B去更新id = 2的行，可以正常执行而不被阻塞，并为id = 2的行加上了行锁。
    1. 无索引或索引失效后，行锁会变成表锁：
  3. 会话A更新department_id = 2的行，更新成功，由于department_id不是索引列，会为整张表加上表锁。
  4. 此时会话B即便更新department_id = 3的行，也会被阻塞，直到会话A释放表锁为止。
  5. 或者当索引失效后，也会行锁变表锁，

如：update sut set age = 18 where name = 2000; name是varchar类型索引列，由于自动转型，索引失效，会为stu表加上表锁。

  1. 间隙锁演示：
    1. 什么是间隙gap：当事务A以范围条件而不是相等条件申请行锁（可能是这些行上的读锁或写锁），会将这个范围内的所有键值都给加锁，而这个范围内不存在的键值（或行）称为间隙。
    2. 原始表数据如下：键值2是间隙GAP（对于id 介于 1-6这个范围）

mysql> select * from stu;
+——+————-+———————-+———+
| id | name | department_id | age |
+——+————-+———————-+———+
| 1 | Jack666 | 1 | 20 |
| 3 | Jane666 | 3 | 30 |
| 4 | Smith | 2 | 18 |
| 5 | Michael | 3 | 20 |
| 6 | Jude | 1 | 22 |
+——+————-+———————-+———+

  1. 会话A更新id 属于 (1, 6) 的行，并且给这个范围内的所有行加上了行锁

image.png

  1. 此时会话B插入id = 2的新行，会由于间隙锁而被阻塞

image.png