MySQL高级--MySQL锁机制

1. 概述
   
   • 定义：

     • 锁是计算机协调多个进程或线程并发访问某一资源的机制。
       在数据库中，除传统的计算资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发
       访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说
       锁对数据库而言显得尤其重要，也更加复杂。

2. 锁的分类：
   • 从对数据操作的类型(读\写)分：

     1. 读锁(共享锁):针对同一-份数据，多个读操作可以同时进行而不会互相影响。

     2. 写锁(排它锁):当前写操作没有完成前，它会阻断其他写锁和读锁。

   • 从对数据操作的粒度分：

     1. 表锁

     2. 行锁

3. 三锁：开销、加锁速度、死锁、粒度、并发性能只能就具体应用的特点来说哪种锁更合适
   • 表锁（偏读）：

     1. 特点：偏向MyISAM存储引擎，开销小，加锁快;无死锁;锁定粒度大，发生锁冲突的概率最高,并发度最低。

     2. 案例分析

        • 建表插入数据
     1. 1. 
        2. session1为表mylock加读锁。
        3. 手动上锁：lock table 表名字  read(write), 表名字2 read(write),其他；
        4. 查看表中所有的锁：show open tables;
        5. 释放表：unlock tables;
        6. 

        7. 

     • 表锁上加写锁：针对同一份数据，多个读操作可以同时进行而不会互相影响。

       1. session01	session02
          为mylock加读锁	session1为表mylock加读锁。 lock table mylock read;	连接终端
          读mylock	当前可以读mylock表:  select * from mylock;	其他session也可以查mylock表: select * from mylock;
          读其他表 写其他表	当前session不能查询其他的表:select * from user; 当前session不能更新其他的表：insert into user(name,age) values('zzz',18);	其他session可以查询其他未枷锁的表:　　select * from user; 其他的session可以写其他未加锁的表： insert into user(name,age) values('ylm',18);
          更新myclock	当前session插入或更新mylock都会报错: insert into mylock(name) values('zs');	其他session插入或更新mylock会一直等待获得锁(阻塞): insert into mylock('name')values('ls');
          释放锁	释放锁:unlock tables;	获得锁，插入或更新操作完成

     1. • 表锁上写锁(排它锁):当前写操作没有完成前，它会阻断其他写锁和读锁。

          1. session01	session02
             添加写锁	当前session为mylock表添加写锁：lock table mylock write;	打开终端
             写mylock	当前session为mylock插入数据： insert into mylock(name) values('sdf');	其他session写mylock表等待（阻塞）： insert into mylock(name) values('sdf');
             释放锁	当前session释放写锁：unlock tables;	插入成功：
             添加写锁	当前session为mylock表添加写锁：lock table mylock write;	打开终端
             读mylock	当前sessoin读mylock表：	其他session读mylock表等待：select * from mylock;
             释放锁	当前session释放读锁：unlock tables；	获得锁读取mylock完成：
             添加锁	当前session添加读锁：lock table mylock write;	打开终端
             读取其他的没有加锁的表	当前的session读取和插入其他未加索的表： select * from user; insert into user(name,age) values('fdsf',18); 不能读取和插入数据	当前的session读取和插入其他未加索的表： select * from user; insert into user(name,age) values('fdsf',18); 可以插入和读取数据

     2. 案例结论

        • MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁

        • MySQL的表级锁有两种模式:
          表共享读锁(Table Read L ock)
          表独占写锁(Table Write Lock)

        • 锁类型	是否可兼容	读锁	写锁
          读锁	是	是	否
          写锁	是	否	否

        • 结论:
          结合上表，所以对MyISAM表进行操作，会有以下情况:
          1、对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
          2、对MyISAM表的写操作(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操

        • 简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读锁和写锁都堵塞。

     3. 表锁分析：

        • 看看哪些表被加锁了
          mysq|l>show open tables; ;

        • 如何分析表锁定
          可以通过检查table_ locks_ waited和table_ locks_ immediate状态变量来分析系统上的表锁定:
          SQL: show status like 'table%';

        • 这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下:
          Table_ _locks_ immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1 ;
          Table_locks__waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况;

        • 此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

   • 行锁（偏读）：
     • 特点：
       1. 向InnoDB存储引擎， 开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
       2. InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION) ;二是采用了行级锁

     • 由于行锁支持事务，复习老知识

       1. 事务(Transaction) 及其ACID属性：

          • 事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

          • 原子性(Atomicity) :事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

          • 一致性(Consistent) :在事务开始和完成时，数据都必须保持一致状态。 这意味着所有相关的数据规则都必须应用于事务的修改以保持数据的完整性;事务结束时，所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。

          • 隔离性(Isolation) :数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

          • 持久性(Durable) :事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

       2. 并发事务处理带来的问题:

          • 更 新丢失(Lost Update)

          • 脏读(Dirty Reads)

          • 不可重复读(Non-Repeatable Reads)

          • 幻读(Phantom Reads)

       3. 事务隔离级别

     • 案例分析

       1. 建表SQL 建表SQL创建索引： 

          • create table user(

          • id int(5) primary key auto_increment,

          • name varchar(5),

          • age int(3));

          • alter table user add  index  idx_user_name (name);

          • create index idx_user_age on user(age);　　

       2. 行锁定基本演示 行锁定基本演示

          • session_01	session_02
            设置自动提交未‘0‘	set autocommit=0;	set autocommit=0;
            更新数据写数据 不提交查询， 提交后在查询，	更新user 表： update user set age=23 where id =1; select * from user;	select * from user;
            更新同一行数据 第二个进入阻塞状态，需等待第一个提交之后在进行提交
            更新不同行的数据 不会相互干扰。 会各自执行

       3. 无索引行锁升级为表锁 无索引行锁升级为表锁

          • session01	session02
            使用正确的语句，索引不失效， 会时两个session可以同时修改不同列的数据
            使用错错误的查询语句，是索引失效，行锁变成了表锁， session1写入表的时候，session2处于阻塞状态

       4. 间隙锁危害 间隙锁危害

          • 

          • 

       5. 面试题:常考如何锁定一行 面试题:常考如何锁定一行

          • select xxx ...for update 锁定某一行后，其他的操作会被阻塞，知道锁定行的会话提交commit：select * from user where id=1 for update;　　
          • 

     • 案列结论

       • 

     • 行锁分析

       • [如何分析行锁定]
         通过检查|InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
         mysq|>show status like '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: 系统启动后到现在总共等待的次数;

       • 对于这5个状态变量，比较重要的主要是

       • Innodb_ _row_ lock_ time_ _avg (等待平均时长)，

       • Innodb_ _row_ lock_ waits ( 等待总次数)

       • Innodb_ row_ lock_ _time (等待总时长)这三项。
         尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着
         手指定优化计划。

     • 优化建议

       1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

       2. 合理设计索引，尽量缩小锁的范围

       3. 尽可能较少检索条件，避免间隙锁

       4. 尽量控制事务大小，减少锁定资源量和时间长度

       5. 尽可能低级别事务隔离

   •  

     页锁：

     • 开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度- -般。

     • 了解一下即可