MySQL锁机制
一、概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
锁的分类
从对数据库操作的类型（读/写）分
-
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
-
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分
-
表锁
-
行锁
二、表锁（偏读）
1. 特点
表锁偏向 MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低
2. 案例分析
数据准备
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;
相关命令
# 查看数据库表锁的命令
SHOW OPEN TABLES;
# 手动添加表锁
LOCK TABLES 表名字 READ(WRITE),表名字2 READ(WRITE),其他;
# 释放给表添加的锁
UNLOCK TABLES;
读锁案例
开两个session进行操作
1. session1中为mylock表添加读锁。
LOCK TABLES `mylock` READ;
2. 读被上锁的表（左session1，右session2）
可以看到session1和2都能够读取被上锁的表
3. 读没有被上锁的表（左session1，右session2）
可以看到session1不能查询其他没有锁定的表，session2都能够读取其他没有锁定的表
4. 插入或更新锁定的表（左session1，右session2）
可以看到session1不能插入或更新锁定的表，会报错，session2会被阻塞一直等待
5. 释放锁（左session1，右session2）
session1释放锁，session2获取锁结束阻塞，完成之前的更新操作
写锁案例
1. 为session1的mylock加写锁
LOCK TABLES mylock WRITE;
2. 对被锁定的表进行查询（左session1，右session2）
session1能进行读操作，session2被阻塞
注意：如果可以，请换成不同的id进行测试，因为mysql聪明有缓存，第2次的条件会从缓存取得，影响锁的效果
3. 对没有锁定的表进行查询（左session1，右session2）
可以看到session1不能查询其他没有锁定的表，session2都能够读取其他没有锁定的表
4. 对被锁定的表插入或更新（左session1，右session2）
session1能进行写操作，session2被阻塞
5. 释放锁（左session1，右session2）
session1释放锁，sesson2获取锁进行操作
3. 案例结论
MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
-
表共享读锁（Table Read Lock）
-
表独占写锁（Table Write Lock）
结论
対MyISAM表进行操作，会有以下情况
-
対MyISAM表的读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作。
-
対MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。
-
简言之，就是读锁会阻塞写，但不会阻塞读。而写锁会把读跟写都阻塞。
4. 表锁分析
看到哪些表被加锁了
show open tables
分析表锁定
SHOW STATUS LIKE 'table%';
可以通过 Table_locks_immediate 和 Table_locks_waited 状态变量来分析系统上的表锁定。具体说明如下
Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。
此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。
三、行锁（偏写）
1. 特点
- 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
- InnoDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁**
2. 老知识（行锁支持事务）
2.1 事务（Transaction）及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性
- 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
- 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（包括B树索引或双向链表）也都必须是正确的。
- 隔离性（Isolation）：数据库系统提供了一定的隔离机制，保证事务在不受外部并发影响的“独立”环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
- 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，及时出现系统故障也能够保持。
2.2 并发事务处理带来的问题
-
更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新改行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新
-
脏读（Dirty Reads）：事务A读到了事务B已修改但未提交的数据。若B事务回滚，A读取的数据无效，不符合一致性要求
-
不可重复读（Non-Repeatable Reads）：事务A读到了事务B已经提交的修改数据，不符合隔离性
-
幻读（Phanton Reads）：事务A读到了事务B已经提交的新增数据，不符合隔离性
2.3 事务隔离级别
脏读、不可重复读、幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的。比如许多应用对不可重复读和幻读并不敏感，可能更关心数据并发访问的能力
MySQL的隔离级别为可重复读
查看数据库的事务隔离级别：show variables like 'transaction_isolation'
3. 案例分析
数据
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
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(1,'b1');
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
SET autocommit=0;
3.1 基本演示
3.1.1 设置手动提交
set autocommit = 0;
3.1.2 session1先更新但不提交
session1
session2
可以看到session1能读到自己未提交但已修改的数据，而session2读不到
这也验证了mysql没有脏读
即，读己之所写
3.1.3 session1提交但session2未提交
可以看到 session1提交但session2未提交仍无法读到session1修改的数据
3.1.4 session2 提交
session2提交后可以读到session1修改的数据，这也验证了mysql不存在可重复读
3.1.5 session1修改后未提交，session2也对同一行进行修改
可以看到session2被阻塞，当session1提交后，session2才能进行修改，如下
3.1.6 session1修改后未提交，session2也对别的一行进行修改
可以发现都能成功
3.2 无索引行锁升级为表锁
由于b字段是字符串，但是没有加单引号导致索引失效
update test_innodb_lock set a = 44 where b = 4001;
update test_innodb_lock set b = '9001' where a = 9;
可以看到修改的是不同行，但是session2却被阻塞了，可以得出行锁升级为表锁
3.3 间隙锁危害
session1
update test_innodb_lock set b = 4002 where a > 1 and a < 6;
session2
insert into test_innodb_lock values(2,'2000');
可以看到虽然表中没有第2行，session1并没有对第二行进行修改，但session2被阻塞了，session1提交后，session2成功插入，如下
什么是间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做"间隙(GAP)"。
InnoDB也会对这个"间隙"加锁，这种锁的机制就是所谓的"间隙锁"。
间隙锁的危害
因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。
间隙锁有一个比较致命的缺点，就是**当锁定一个范围的键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。**在某些场景下这可能会対性能造成很大的危害。
3.4 面试题：如何锁定一行
SELECT .....FOR UPDATE
在锁定某一行后，其他写操作会被阻塞，直到锁定的行被 COMMIT
示例
session1
begin;
select * from test_innodb_lock where a = 8 for update;
session2
update test_innodb_lock set b = '8001' where a = 8;
可以看到session2无法更新被阻塞
mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。
4. 案例结论
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
5. 行锁分析
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：系统启动后到现在总共等待的次数（重要）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略。
6. 优化建议
-
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
-
合理设计索引，计量缩小锁的范围
-
尽可能较少检索条件，避免间隙锁
-
尽量控制事务大小，减少锁定资源量和时间长度
-
尽可能低级别事务隔离
四、页锁
开销和加锁时间介于表锁和行锁之间，会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
主从复制
一、复制基本原理
slave会从master读取binlog来进行数据同步
原理图
步骤
-
Master将改变记录到二进制日志(Binary Log)。这些记录过程叫做二进制日志事件，Binary Log Events；
-
Slave将Master的Binary Log Events拷贝到它的中继日志(Replay Log);
-
Slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的。
二、复制的就基本原则
-
每个Slave只有一个Master。
-
每个Slave只能有一个唯一的服务器ID。
-
每个Master可以有多个Salve。
三、复制的最大问题
延时
四、一主一从常见配置
-
基本要求：Master和Slave的MySQL服务器版本一致且后台以服务运行。
-
主从配置都是配在[mysqld]节点下，都是小写
-
主机配置文件
# Master配置
[mysqld]
server-id=1 # 必须 主服务器唯一ID
log-bin=本地路径/mysql-bin # 必须 启用二进制日志
log-err=本地路径/mysql-err # 可选 启用错误日志
basedir=本地路径 # 可选 根目录
tmpdir=本地路径 # 可选 临时目录
datadir=本地路径/Data/ # 可选 数据目录
read-only=0 # 主机 读写都可以
binlog-ignore-db=mysql # 可选 设置不要复制的数据库
binlog-do-db=ww2 # 可选 设置需要复制的数据库
- 从机配置文件
# Slave配置
[mysqld]
server-id=2 # 必须 从服务器唯一ID
log-bin=/var/lib/mysql/mysql-bin # 可选 启动二进制文件
- 重启mysql服务器
windows 管理员权限下
net stop mysql
net start mysql
linux
service mysql restart
- 关闭防火墙
windows手动关闭
linux service iptables stop
- 在主机上建立账户并授权slave
# 1、建立账户并授权slave
GRANT REPLICATION SLAVE ON *.* TO 'username'@'从机IP地址' IDENTIFIED BY 'password';
版本是8,报错，原因是要先创建用户再进行赋权，不能同时进行，所以我使用如下方法
create user 'username'@'从机IP地址' identified by 'password';
grant REPLICATION SLAVE on *.* to 'username'@'从机IP地址';
# 2、刷新命令
FLUSH PRIVILEGES;
# 3、查看master状态，记录下File和Position
# 每次配从机的时候都要SHOW MASTER STATUS;查看最新的File和Position
SHOW MASTER STATUS;
- 在从机上配置需要复制的主机
CHANGE MASTER TO MASTER_HOST='主机IP',
MASTER_USER='刚刚建立并授权的用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='File名字',
MASTER_LOG_POS=Position数字;
示例
CHANGE MASTER TO MASTER_HOST='主机IP',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000013',
MASTER_LOG_POS=1666;
启动从服务器复制功能
start slave;
查看主从复制是否成功
SHOW SLAVE STATUS\G
Slave_IO_Running 和 Slave_SQL_Running 必须同时为Yes 说明主从复制配置成功！
- 主机新建库，新建表，插入数据，看查看丛机是否复制
主表创建数据库
create database test_copy;
从表能够查到该数据库
主表创建表，并插入数据
create table dog(id int not null,name varchar(60));
insert into dog values(1,"aaa");
select * from dog;
从表查询该表，发现能够查找到
- 停止从服务复制功能
stop slave;
重启slave的话需要重新查询主机的show master status;，得到新的File和Position
424
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
