1. MySQL 锁机制

1.1 概述

定义

锁是计算机协调多个进程或线程并发访问某个资源的机制。

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

生活购物案例

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq69.jpg)

锁的分类

对数据操作的类型分（读/写）
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响
- 写锁（排他锁）：当前写操作没有完成之前，它会阻隔其他写锁和读锁
对数据操作的粒度来分
- 表锁
- 行锁

1.2 三锁

1.2.1 表锁（偏写）

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

案例分析

建表SQL

use big_data;

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) 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');

select * from mylock;

1.2.2 行锁（偏读）

特点

1
2
3

偏向 InnoDB 存储引擎，开销大，加锁慢； 会出现死锁；锁的粒度最小，发生锁冲突的频率也最低，并发度也最高。

InnoDB 与 MyISAM 的最大不同有亮点：一是支持事务（TRANSACTION）; 而是采用了行级锁

行锁支持事务

事务 (Transcation 及其 ACID 属性)

**事务及其 ACID 属性**

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

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

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

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

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

并发事务处理带来的问题

1. 更新丢失 （Lost Update）
当两个或多个事务选择同一行，然后基于最初选定的指更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题— 最后的更新覆盖了其他事务所做的更新。

例如：两个程序员修改同一个 java 文件， 每个程序员，独立更改其副本，然后保存更改后的副本，这样就覆盖了原始文档，最后保存其更改副本的编辑人员覆盖了前一个程序员所做的更改。

如果在一个程序员完成并提交事务之前，另一个程序员不能访问该文件，则可以避免该问题。


2. 脏读 （Dirty Reads）

一个事务正在对记录做修改，在这个事务完成提交之前。这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加以控制，第二个事务读取了这些“脏”数据，并据此做进步一的处理，就会产生提交的数据依赖关系， 这种现象被形象的叫做 “脏读”。

一句话：事务A读取事务B 已修改未提交的 数据，还在这个数据的基础上做了修改。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

3. 不可重复读（Non-Repeatable Reads）

一个事务在读取这些数据后的某个时间， 再次读取这之前读过的数据，却发现其读出的数据已经发生了变化，或某写记录已经被删除了，这种现象叫做“不可重复读”

一句话： 事务A，读取到了事务 B已经提交的修改数据，不符合隔离性

4. 幻读（Phantom Reads）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现了其他事务插入了满足条件的新数据，这种称为 “幻读” 

一句话：事务A读取到了事务B提交的新数据，不符合隔离性。

幻读和脏读的区别
脏读是事务B里面修改了数据。
幻读是事务B里面新增了数据。

事务隔离级别

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq74.jpg)

show variables like '%tx_isolation%';

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq75.jpg)

案例分析

建表SQL

use big_data;

create table test_innodb_lock (
id int primary key auto_increment,
b varchar(16)
)engine = innodb;

insert into test_innodb_lock values (1, 'b2');
insert into test_innodb_lock values (2, '3');
insert into test_innodb_lock values (3, '3000');
insert into test_innodb_lock values (4, '4000');
insert into test_innodb_lock values (5, '5000');
insert into test_innodb_lock values (6, '6000');
insert into test_innodb_lock values (7, '7000');
insert into test_innodb_lock values (8, '8000');
insert into test_innodb_lock values (9, '9000');
insert into test_innodb_lock values (10, 'b1');


select * from test_innodb_lock;
#show open tables;

create index idx_test_innodb_lock_b on test_innodb_lock (b);

行锁的基本演示

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq76.jpg)

无索引行锁升级为表锁

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq77.jpg)

间隙锁的危害

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq78.jpg)
](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq79.jpg)

面试题：如何锁定一行

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq80.jpg)

案例结论

Innodb 的存储引擎由于实现了行级锁，虽然在锁定机制的实现方面带来了一定的性能损耗可能要比表级锁定会更高一些，但是在整体并发处理方面要远远优于 MyISAM表级的锁定。当系统并发较高的时候， InnoDB 的整体性能和MyISAM 相比就会比较有明显的优势了。


但是，InnoDB 的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让 InnoDB 的整体表现不仅不能比 MyISAM 高，甚至可能会更差。

行锁分析


【如何分析行锁定】
通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况。

 Mysql > 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 页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

2. 主从复制

2.1 复制的基本原则

slave 会从 master 读取 binlog 来进行数据同步

三个步骤 + 原理图

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq82.jpg)

MySQL复制过程分为三步：

master 将改变记录到二进制日志（binary log）. 这些记录过程叫做二进制日志时间， binary log events;
slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）;
slave 重做中继日志中的时间，将改变应用到自己的数据库中。MySQL 复制是一步的且串行化的。

复制的基本原则

每个 slave 只有一个 master
每个 slave 只能有一个唯一的服务器ID
每个 master 可以有多个 slave

2.2 一主一从常见配置

2.2.1 mysql 版本一致且后台以服务运行

2.2.2 主从都配置在 [mysqld] 节点下，都是小写

2.2.3 主库修改 my.ini 配置文件

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq83.png)

1. [必须] 主机服务器唯一 ID -------  server-id=1


2. [必须] 启用二进制日志
log-bin = 自己的本地的路径/mysqlbin
log-bin=D:/devsoft/mysql5.7/data/mysqlbin

3 [可选] 启用错误日志
log-err = 自己的本地的路径/mysqlerr
log-err=D:/devsoft/mysql5.7/data/mysqlerr

4. [可选] 根目录
basedir = "自己本地路径"
basedir = "D:/devsoft/mysql5.7/"

5. [可选] 临时目录

tempdir = "自己本地路径"
tempdir = "D:/devsoft/mysql5.7/"


6. [可选] 数据目录
datadir = "自己本地路径/data"
datadir = "D:/devsoft/mysql5.7/data"


7. read-only = 0 --------主机读写都可以

8. [可选] 设置不要复制的数据库
binlog-ignore-db=mysql

9. [可选]设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字

2.2.4 从库修改 my.conf 配置文件

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq84.png)

1
2
3

1. [必须] 主机服务器唯一 ID

2. [可选] 启用二进制日志

因修改过配置文件，请主机 + 从机都重启后台 mysql 服务

主机从机关闭防火墙

2.2.5 在 Windows 及其上建立账户并授权 slave

grant replication slave on *.* to 'zhangsan'@'从库IP' identified by '123456';

flush privileges;

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq85.png)

查询 master 状态

show master status;

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq88.png)

记录 File 和 Postition 值

执行完此步骤后再操作数主服务器 MySQL , 防止主服务器状态值变化

2.2.6 在 LIunx 上配置需要复制的主机

CHANGE MASTER  TO  MASTER_HOST='主机 IP'
MASTER_USER=‘zhangsan’
MASTER_PASSWORD='123456'
MASTER_LOG_FILE='mysqlbin.具体数字'， MASTER_LOG_POS=具体值；

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq86.png)

启动从服务其复制功能

start slave

show slave status\G

](https://zhangxin-blog.oss-cn-beijing.aliyuncs.com/blog/mysql/zzyy/mq87.png)

Slave_IO_Running: Yes
Slave_IO_Running: NO
如果上面的两个参数都是 : Yes 说明配置成功！！

主机新建库、新建表、insert 记录、从机复制

如何重启从服务器的复制 - —stop slave

1. MySQL 锁机制

1.1 概述

1.2 三锁

1.2.1 表锁（偏写）

案例分析

加读锁

加写锁

案例结论

表锁分析

1.2.2 行锁（偏读）

行锁支持事务

案例分析

1.2.3 页锁

2. 主从复制

2.1 复制的基本原则

2.2 一主一从常见配置

2.2.1 mysql 版本一致且后台以服务运行

2.2.2 主从都配置在 [mysqld] 节点下，都是小写

2.2.3 主库修改 my.ini 配置文件

2.2.4 从库修改 my.conf 配置文件

2.2.5 在 Windows 及其上建立账户并授权 slave

2.2.6 在 LIunx 上配置需要复制的主机