Mysql06-MySQL锁

Mysql06-MySQL锁
    
    数据库锁理论
    索引失效行锁变表锁
    间隙锁危害
    如何锁定一行
    主从复制
    
一、数据库锁理论
    1.锁是计算机协调多进程或多线程并发访问某一资源的机制
    2.分类
        1)从对数据的操作类型分
            读锁（共享锁）：同一份数据，多个读操作可以同时进行而不会相互影响
            写锁（排它锁）：当前写操作没完成前，它会阻断其他写锁和读锁
        2)从对数据操作的粒度分为 行锁 和 表锁
    3.三锁
        1)表锁，偏读
            · 特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
            · 建表分析：
            CREATE TABLE mylock(
                id int not null primary key auto_increment,
                name varchar(20)
            )ENGINE myisam;
             
            insert into mylock(name) values('a');
            insert into mylock(name) values('b');
            insert into mylock(name) values('c');
            insert into mylock(name) values('d');
            insert into mylock(name) values('e');
            · 查看表的锁情况
            show open tables;  // in use 表示被锁
             
            · 手动增加表锁，逗号分隔
            lock table 表名 read/write, 表名 read/write;
             
            · 释放锁
            unlock tables;

            · 对mylock表加读锁：
            session1> lock table mylock read; // 当前会话可以查询当前表，但是不能更新当前表，也不能查询其他未锁定的表，当增删改查其他未锁定的表时，如book表，会报book表未被锁定的错误
            session2> 可以查询 mylock 表，但是修改 mylock 表会阻塞等待session1释放锁；更新其他未锁定的表没问题
            
            · 对mylock表加写锁：
            session1> lock table mylock write; // 当前会话可以增删改查该锁定表
            session2> 增删改查 mylock 表都会阻塞，直到 session1 释放对 mylock 表的写锁
            
            即，读锁会阻塞写，不会阻塞读；写锁会把读和写都阻塞
            · 表锁分析可以通过使用 show status like 'table%';
                Table_locks_immediate：产生标记锁定的次数，表示可以立即获取锁的查询次数
                Table_locks_waited：出现表锁定争用而发生的等待次数，每等待一次锁+1，这个值高时，说明存在严重的表级锁争用情况
        2)行锁，偏写
            · 特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
            · InnoDB与MyISAM最大的不同有两点：InnoDB支持事务和采用行级锁
            · 事务的ACID属性
            
            · 并发事务处理带来的问题
                更新丢失：每个事务不知道其他事务的存在，最后的更新覆盖了其他事务提交的更新
                脏读：事务A读取到了B修改但尚未提交的数据，不符合隔离性
                不可重复读：事务A在读取某个数据后的某个时间，再次读以前读过的数据，缺发现被B事务修改，不能再读到之前的数据，不符合隔离性
                幻读：事务A读取到了B新增的数据，不符合隔离性
            · 事务的隔离级别
            
            · 不同会话更新不同的行，互相不影响
            · 行锁分析可以通过使用 show status like 'innodb_row_lock%'; 行锁分析各状态量说明：
            
            · 行锁相关优化建议：
                > 尽可能让所有数据检索都通过索引完成，避免无索引行锁升级为表锁；
                > 合理设计索引，尽量缩小锁的范围；
                > 极可能较少检索条件，避免间隙锁；
                > 尽量控制事务大小，减小锁定资源量和时间长度；
                > 尽可能低级别事务隔离
        3)页锁，开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于两者之间，并发度一般
    4.mysql默认的隔离级别是 可重复读 
    
二、索引失效行锁变表锁
    1.索引失效的话，会导致行锁升级为表锁
    启动两个session，表中a字段是number，b字段是varchar
    session1操作第4行，其中b字段故意不写单引号，先不commit
    session2操作第9行，正确写sql语句，发现一执行就阻塞了。是因为session1导致索引失效，行锁变成表锁
    session1手动commit
    session2接着执行，然后手动commit
    
三、间隙锁危害
    1.间隙锁，当使用范围条件而不是相等条件检索数据，并请求共享或排它锁时，Innodb会给符合条件的已有数据记录的索引项加锁
      对于键值在条件范围内但不存在的记录，叫做间隙（GAP）
    
    
四、如何锁定一行
    select * from table where a = 1 for update;
    
五、主从复制
    1.复制的基本原理
        1)slave会从master读取binlog来进行数据同步
        2)mysql复制过程3步：
        
    2.复制的基本原则
        1)每个slave只有一个master
        2)每个slave只有一个唯一的服务器ID
        3)每个master可以有多个slave
    3.复制的最大问题：延时
    4.一主一从常见配置：
        1)主从都配置在[mysqld]节点下，都是小写
        2)主机修改my.ini配置文件
            [必须]主服务器唯一ID
            [必须]启用二进制日志:log-bin=自己本地的路径/mysqlbin
            [可选]启用错误日志:log-err=自己本地的路径/mysqlerr
            [可选]根目录:basedir=自己的本地路径
            [可选]临时目录:tmpdir=自己的本地路径
            [可选]数据目录:datadir=自己的本地路径/Data
            read-only=0 主机读写都可以
            [可选]设置从机忽略，不复制的主机数据库;binlog-ignore-db=mysql;从机不复制主机的mysql库
            [可选]设置需要复制的主数据库名字;binlog-do-db=库名
        3)从机修改my.cnf配置文件
            [必须]从服务器唯一ID
            [可选]启用二进制日志:log-bin=mysql-bin
        4)主机、从机都重启mysql服务
        5)主机、从机都关闭防火墙
        6)在Windows主机上建立账户并授权slave
            GRANT REPLICATION SLAVE ON *.* '用户名Y' @ '从机IP地址' IDENTIFIED BY '密码123';
            flush privileges;
            查询master状态：show master status;
        7)在Linux从机上配置需要复制的主机
            CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='Y',MASTER_PASSWORD='123',MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=Position数字;
            
            启动从服务器复制功能:start slave;
            show slave status\G
                Slave_IO_Running:Yes
                Slave_SQL_Running:Yes
        8)主机新建库，新建表，insert记录，从机复制
        9)如何停止从机复制功能：从机的命令行中输 stop slave;