«

MySQL锁机制

时间：2023-2-27 22:44     作者：wen     分类： MySQL

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题?

这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类

  1. 从对数据操作的类型（读、写）分（读锁，写锁）
    读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
    写锁(排它锁)：当前写操作没有完成前，它会阻断其它写锁和读锁。
  2. 从对数据操作的粒度分（表锁、行锁）

  3. 三锁

    1. 表锁(偏读)
      特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
      【查看表上加过得锁】
      show open tables
      In_use=1是代表表以上锁
      加锁
      lock table 表名字 read(write),表名字2 read(write),其它;
      解锁
      unlock tables;
      加读锁：A进程给book表加读锁，A不能查看其它的表，也不能更新book表的数据。B进程能读取book表的数据，也能查看其它表的数据，B更新book表的数据，需要等待book表的读锁释放
      加写锁：A进程给book表加写锁，A能读book的数据，也能更新book的数据，不能读、写其它表的数据。B进程能读其它表的数据，当读、写book表数据时，会等待book的写锁释放
      结论：

      1. 对MyISAM表的读操作（加读锁），不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
      2. 对MyISAM表的写操作（加写锁），会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。简而言之，就是读锁会阻塞写，但不是堵塞读。而写锁则会把读和写都堵塞。
        【如何分析表锁定】
        可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定：
        SQL：show status like 'table%';
        show status like table
        Table_locks_immediate：参数表级锁定的次数表示可以立即获取锁的查询次数，每立即获取锁值加1；
        Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；
        此外，MyISAM的读写锁调度室写优先，这也是MyISAM不适合做些为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久堵塞。

    2. 行锁(偏写)
      特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁
      MySQL事务 由于行锁支持事务，并发处理带来的问题

      1. 更新丢失（Lost Update）
      2. 脏读（Dirty Reads）
      3. 不可重复读（Non-Repeatable Reads）
      4. 幻读（Phantom Reads）
        【默认自动事务,关闭事务自动提交，改为手动事务，一般不用动这个配置，这里只是介绍一下，演示时可以用】
        set autocommit=0;
        无索引或索引失效行锁升级为表锁
        间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB与会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
        间隙锁危害：因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在多大的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
        锁定某一行： 
        BEGIN; -- 开始
SELECT * FROM tp_user WHERE id = 1 FOR UPDATE; -- 锁定
COMMIT; -- 提交

        结论：InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定，当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
        【如何分析行锁定】
        通过检查InnoDB_row_lock状态变量来分析系统上的行锁定的争夺情况
        SQL： show status like 'innodb_row_lock%';
        innodb_row_lock
        InnoDB_row_lock_current_waits：当前正在等待锁定的数量；
        Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
        Innodb_row_lock_time_avg：每次等待所花平均时间；
        Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；
        Innodb_row_lock_waits：系统启动到现在总共等待的次数；
        优化建议：

        1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。varchar类型不加单引号是重罪；
        2. 合理设计索引，尽量缩小锁的范围。
        3. 尽可能较少检索条件，避免间隙锁。
        4. 尽量控制事务大小，减少锁定资源量和时间长度。
        5. 尽可能低级别事务隔离。

    3. 页锁
      开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。了解一下即可。
      开销、加锁速度、死锁、粒度、并发性能只能就具体应用的特点来说那种锁更合适

标签: mysql优化