mysql优化参考(六)-隔离性（锁）

一、隔离级别参考：https://www.cnblogs.com/gabin/p/13457612.html

二、锁

• 类型：
  • 表共享读锁（MyIsAM）：只与共享锁共存
  • 表独占写锁（MyIsAM）：任何锁都不能共存，只能等
  • 共享锁（S）- InnoDB：只能与共享锁共存
  • 排它锁（X）- InnoDB：任何锁都不能共存，只能等
  • 意向共享锁（由mysql自行处理，只是代表想要加共享锁，先打个招呼）
  • 意向排它锁（由mysql自行处理，只是代表想要加排它锁，先打个招呼）
  • 间隙锁（参考）：为了解决可重复读隔离级别中的幻读问题引入的，比如查询id 在4~10之间，此时只有4~9有数据，但是锁只锁住了4~9，如果此时另外一个事务提交了10的数据，则重复读取一样的范围查询会出现不同的结果
  • 自增锁：自增字段用来保证ID唯一
• 特性：
  • MyIsAM 都是表锁
  • InnoDB锁的是索引，没有索引则上升为表锁
  • MyIsAM支持并发锁，通过配置参数：concurrent_insert，参考
  • 锁表语句：

    lock table t1 write;
    lock tables t1 t2 write;
    lock table t1 read;
    lock tables t1 t2 read;
    lock table t1 read local;
    lock tables t1 t2 read local;
    unlock tables;

• 常见集中问题
  • 死锁
    • 表t1，主键id，记录：1和2
    • 客户端1，客户端2；都关闭自动提交
    • 客户端1：

      mysql> select * from t1 where id=1 for update;
      +----+
      | id |
      +----+
      |  1 |
      +----+
      1 row in set (0.00 sec)

    • 客户端2：

      mysql> select * from t1 where id=2 for update;
      +----+
      | id |
      +----+
      |  2 |
      +----+
      1 row in set (0.00 sec)

    • 客户端1(注意这边的时间比较长，实际上在等待客户端2释放锁)：

      mysql> select * from t1 where id=2 for update;
      +----+
      | id |
      +----+
      |  2 |
      +----+
      1 row in set (10.81 sec)

    • 客户端2：由于资源依赖死循环，直接造成死锁了

      mysql> select * from t1 where id=1 for update;
      ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  • 脏读
    • 在mysql的配置文件的[mysqld]下方增加，这边用的是未提交读，但其实这个模式下存在脏读、幻读、重复读问题[READ-UNCOMMITTED  REPEATABLE-READ SERIALIZABLE READ-COMMITTED]

      transaction_isolation = READ-UNCOMMITTED

    •  重启mysql-server

    • 创建一个表

      create table t1(id int primary key);

    • 开启第二个客户端连接
    • 两个客户端都关闭自动提交

      set autocommit=0;

    • 第一个客户端，insert一个记录

      insert into t1 values (1);

    • 第二个客户端，select all,查询到了第一个客户端未提交的记录

      mysql> select * from t1;
      +----+
      | id |
      +----+
      |  1 |
      +----+
      1 row in set (0.00 sec)

    • 第一个客户端rollback
    • 第二个客户端再次查询，发现记录为空
  • 重复读
    • 配置隔离级别为提交读（不可重复读：存在重复读和幻读问题），这个配置方式参考上面
    • 同样开启两个客户端，并且关闭自动提交
    • 还是上面的t1表
    • 客户端1

      insert into t1 values (1);

    • 客户端2：select * from t1，记录为空，说明解决了脏读的问题
    • 客户端1 提交

      commit;

    • 客户端2：

      mysql> select * from t1;
      +----+
      | id |
      +----+
      |  1 |
      +----+

    • 客户端1，删除表记录
    • 客户端2，再次查询（未提交情况下），查询记录为空
    • 出现在同一个事务中，同样的sql语句出现不同的查询结果（不可重复读问题）；这个例子其实最好用update来体现，就是有点懒
  • 幻读
    • 配置隔离级别为提交读（可重复读在mysql的innodb中使用了间隙锁+行锁的方式解决了幻读问题：），这个配置方式参考上面
    • 同样开启两个客户端，并且关闭自动提交
    • 还是上面的t1表
    • 客户端1 

      insert into t1 values (1);

    • 客户端2

      mysql> select * from t1 where id > 0;
      +----+
      | id |
      +----+
      |  1 |
      +----+
      1 row in set (0.00 sec)

    • 客户端1

      mysql> insert into t1 values (2);
      Query OK, 1 row affected (0.00 sec)
      
      mysql> commit;
      Query OK, 0 rows affected (0.00 sec)

    • 客户端2（注意这里是没提交的情况下，查出来的记录有两条；这个其实和不可重复读一样的，只是幻读是insert类型的）

      mysql> select * from t1 where id > 0;
      +----+
      | id |
      +----+
      |  1 |
      |  2 |
      +----+
      2 rows in set (0.00 sec)