mysql学习（六）mysql锁机制

1.mysql的四个特性：原子性、一致性、隔离性、持久性。

1.原子性：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部操作成功，要么全部失败回滚，这就是事务的原子性。

2.一致性：数据库总数从一个一致性的状态转换到另一个一致性状态。

3.隔离性：一个事物所做的修改在最终提交以前，对其他事务是不可见的。

4.持久性：一旦事务提交，则其所做的修改就会永久的保存到数据库中，即使系统崩溃，修改的数据也不会丢失。

2.锁机制的作用：解决因资源共享造成的并发问题；

示例：买最后一件衣服

A:  买操作：X加锁  ->试衣服->下单.....付款.....打包   ，X解锁；

B:  买操作：发现X已加锁，等待X解锁...............X解锁，发现X已售空。

锁分类：

　　操作类型：

　　　　a.读锁（共享锁）：当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排他锁，但允许上读锁。

　　　　b.写锁（排他锁）：当一个事务对某几行上写锁时，不允许其他事务写操作，不允许读，更不允许其他事务给这几行上任何锁，包括写锁。

　　按锁的粒度划分：

　　　　a.行级锁：行级锁是mysql中锁定粒度最细的锁。InnoDB引擎支持行级锁和表级锁，只有在通过索引条件检索时，才使用行级锁，否则就使用表级锁。行级锁开销大，加锁慢，锁定粒度最细，发生锁冲突概率最低，并发度最高。

　　　　b.表级锁：表级锁开销小，上锁快，锁定粒度最粗，发生冲突概率最高，并发度最低。MyISAM和Memory存储引擎采用表级锁。

　　　　c.页级锁：页级锁是mysql中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁上锁速度快、但锁冲突概率大，行级锁上锁速度慢、锁冲突概率小。所以取了折中的页级锁，一次给相邻的一组数据上锁。BDB支持页级锁和表级锁，默认是页锁。

3.表锁

　　创建使用存储引擎为myisam的表：

　　create table tablelock(
　　　　id int primary key auto_increment ,
　　　　name varchar(20)
　　)engine myisam;

　　insert into tablelock(name) values(‘a1‘);
　　insert into tablelock(name) values(‘a2‘);
　　insert into tablelock(name) values(‘a3‘);
　　insert into tablelock(name) values(‘a4‘);
　　insert into tablelock(name) values(‘a5‘);
　　commit;

　　加锁方式：

　　lock table 表名 read/write

　　查看加锁的表：
    　show open tables ;

　　会话：session :每一个访问数据的dos命令行、数据库客户端工具  都是一个会话

　　===加读锁：
        会话0：
            lock table  tablelock read ;
            select * from tablelock; --读（查），可以
            delete from tablelock where id =1 ; --写（增删改），不可以
            select * from emp ; --读其他表，不可以
            delete from emp where eid = 1; --写其他表，不可以
            结论1：
            --如果某一个会话 对A表加了read锁，则 该会话 可以对A表进行读操作、不能进行写操作； 且 该会话不能对其他表进行读、写操作。
            --即如果给A表加了读锁，则当前会话只能对A表进行读操作。

        会话1（其他会话）：
            select * from tablelock;   --读（查），可以
            delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放
            select * from emp ;  --读（查），可以
            delete from emp where eno = 1; --写，可以
            结论2：
            --总结：
                会话0给A表加了锁；其他会话的操作：a.可以对其他表（A表以外的表）进行读、写操作
                                　　　　　　　　　　　　　b.对A表：读-可以；  写-需要等待释放锁。
        释放锁: unlock tables ;

　　===加写锁：
        会话0：
            lock table tablelock write ;
            当前会话（会话0） 可以对加了写锁的表  进行任何操作（增删改查）；但是不能 操作（增删改查）其他表
        其他会话：
            对会话0中加写锁的表 可以进行增删改查的前提是：等待会话0释放写锁

　　释放锁: unlock tables ;

　　MySQL表级锁的锁模式
　　MyISAM在执行查询操作（DQL）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。
　　所以对MyISAM表进行操作，会有以下情况：
　　　　a、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
　　　　b、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

4.分析表锁定
    查看哪些表加了锁：   show open tables ;  1代表被加了锁
    分析表锁定的严重程度： show status like ‘table%‘ ;
            Table_locks_immediate :即可能获取到的锁数
            Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)
    一般建议：
        Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎

5.行表（Innodb）

　　create table linelock(
　　　　id int(5) primary key auto_increment,
　　　　name varchar(20)
　　)engine=innodb ;
　　insert into linelock(name) values(‘1‘)  ;
　　insert into linelock(name) values(‘2‘)  ;
　　insert into linelock(name) values(‘3‘)  ;
　　insert into linelock(name) values(‘4‘)  ;
　　insert into linelock(name) values(‘5‘)  ;

　　注意：--mysql默认自动commit;    oracle默认不会自动commit ;

　　为了研究行锁，暂时将自动commit关闭;  set autocommit =0 ; 以后需要通过commit

行锁操作一行数据：
    会话0： 写操作
        insert into linelock values( ‘a6‘) ;  
    会话1： 写操作 同样的数据
        update linelock set name=‘ax‘ where id = 6;
    对行锁情况：
        1.如果会话x对某条数据a进行 DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后  才能对数据a进行操作。
        2.表锁 是通过unlock tables，也可以通过事务解锁 ; 行锁 是通过事务解锁。
行锁，操作不同数据：
    会话0： 写操作
        insert into linelock values(8,‘a8‘) ;
    会话1： 写操作， 不同的数据
        update linelock set name=‘ax‘ where id = 5;
        行锁，一次锁一行数据；因此 如果操作的是不同数据，则不干扰。

行锁的注意事项：
    a.如果没有索引，则行锁会转为表锁
    　　show index from linelock ;
    　　alter table linelock add index idx_linelock_name(name);

    　　会话0： 写操作
       　　 update linelock set name = ‘ai‘ where name = ‘3‘ ;       
   　　 会话1： 写操作， 不同的数据
       　　 update linelock set name = ‘aiX‘ where name = ‘4‘ ;

    　　会话0： 写操作
        　　update linelock set name = ‘ai‘ where name = 3 ;       
   　　 会话1： 写操作， 不同的数据
        　　update linelock set name = ‘aiX‘ where name = 4 ; -- 写操作阻塞
        
    　　--可以发现，数据被阻塞了（加锁）
   　　 -- 原因：如果索引类 发生了类型转换，则索引失效。 因此 此次操作，会从行锁 转为表锁。

    b.行锁的一种特殊情况：间隙锁：值在范围内，但却不存在
    　　 --此时linelock表中 没有id=7的数据
    　　update linelock set name =‘x‘ where id >1 and id<9 ;   --即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。
    　　间隙：Mysql会自动给 间隙 加索 ->间隙锁。即 本题 会自动给id=7的数据加 间隙锁（行锁）。
    　　行锁：如果有where，则实际加索的范围 就是where后面的范围（不是实际的值）

如何仅仅是查询数据，能否加锁？ 可以   for update
    研究学习时，将自动提交关闭：
        set autocommit =0 ;
        start transaction ;
        begin ;
     select * from linelock where id =2 for update ;
    通过for update对query语句进行加锁。

    行锁：
    InnoDB默认采用行锁；
    缺点： 比表锁性能损耗大。
    优点：并发能力强，效率高。
    因此建议，高并发用InnoDB，否则用MyISAM。

    行锁分析：
      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 ：    等待次数。从系统启到现在一共等待的次数

mysql学习（六）mysql锁机制

原文：https://www.cnblogs.com/Aaron-cell/p/12943813.html