MySQL锁机制

msql锁机制

概述

锁的定义

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

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

RAM：Random Access Memory，随机存取存储器。

锁的分类

XXXXXXXX

MyISAM的锁机制
MYSQL事务
1
2
3
4
5
6
7
8
9
10
1、用 BEGIN, ROLLBACK, COMMIT 来实现

BEGIN 或 START TRANSACTION：开用于开始一个事务。
ROLLBACK 事务回滚，取消之前的更改。
COMMIT：事务确认，提交事务，使更改永久生效。

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

表锁（偏读）

1
2
3
4
表锁
读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰。加锁的会话只能对此表进行读操作,其他会话也只能进行读操作。MyISAM的读默认是加读锁。

加写锁（互斥锁）：当前能读和写，阻塞其他线程的读和写（等待）。

建表sql

释放表锁

unlock tables;(上表in_use项全部变为0)

加读锁

总结

总之就是：读锁会阻塞写，但不会堵塞读；写锁则会把读和写都阻塞；

表锁分析

lnnoDB的锁机制

行锁(偏写)

1
2
3
4
5
6
7
8
9
10
共享锁（读锁）：简称S锁，当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，允许其他事务上读锁，但不允许给这几行上写锁。

排他锁（写锁）：简称X锁，当一个事务对某几个上写锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作。也不允许其他事务给这几行上任何锁。

注意：
1.两个事务不能锁同一个索引。
2.insert ，delete ， update在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
4.InnoDb表锁和MyISAM差别不大 。注意：开启一个新事务的时候会解锁表
参考：https://blog.csdn.net/FMC_WBL/article/details/88831294

什么情况会加锁：

1
2
3
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁；
对于普通SELECT语句，InnoDB不会加任何锁；
共享锁，只有主动触发才会加锁，比如加了lock in share mode 的 select 语句

参考：https://www.cnblogs.com/hi3254014978/p/17107610.html

事物的ACID

并发事务会导致数据脏读、不可重复读、幻读、更新丢失

更新丢失（Lost Update）

两个事务在并发下同时进行更新，后一个事务的更新覆盖了前一个事务更新的情况，丢失更新是数据没有保证一致性导致的。比如，事务A 修改了一条记录，事务B 在 事务A 提交的同时也进行了一次修改并且提交。当事务A查询的时候，会发现刚才修改的内容没有被修改，好像丢失了更新。

解决办法：

1悲观锁

2乐观锁

https://blog.csdn.net/sun8112133/article/details/89853755

事物隔离级别与脏读、不可重复读、幻读的关系

mysql的InnoDB事务的隔离级别 默认是“可重复读”（REPEATABLE READ）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1）未提交读（READ UNCOMMITTED）。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）( 隔离级别最低，并发性能高 )。
2）提交读（READ COMMITTED）。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）。会出现不可重复读、幻读问题（锁定正在读取的行）
3）可重复读（REPEATABLE READ）。在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象（稍后解释）。会出幻读（锁定所读取的所有行）。
4）串行化（SERIALIZABLE）。读操作会隐式获取共享锁，可以保证不同事务间的互斥（锁表）。serializable时会锁表，因此不会出现幻读的情况，包括更新丢失也能解决，但这种隔离级别并发性极低，开发中很少会用到

四个级别逐渐增强，每个级别解决一个问题。
1）脏读。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据。
2）不重复读。解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致。
3）幻读。解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

脏读和幻读的区别：脏读是事务b修改了数据，幻读是事务b新增了数据

隔离级别越高的数据库越安全，能解决的并发*一致性*问题也就越多。
隔离级别越高，就代表着越安全，但是同时性能效率也就越低。

参考链接：https://blog.csdn.net/ccfeng2008/article/details/51760350

InnoDB引擎通过MVCC，解决了脏读、不可重复读，通过MVCC + Next-Key Lock(临键锁)来解决幻读，实现了事务的隔离级别Repeatable Read（可重复读）。

可重复读隔离级别下，并发问题需要靠开发者显式或隐式使用锁来自行解决。

RR防止幻读的讨论：

https://github.com/Yhzhtk/note/issues/42

结论：Innodb 的 RR 隔离界别对范围会加上 GAP，理论上不会存在幻读，但是是否有例外，还需要进一步求证。

nosql CAP 理论

http://www.cnblogs.com/gudi/p/8183548.html

一致性C（Consistency）、可用性A（Availability）和分区容错性P（Tolerance of network Partition）

保CP还是保AP，大网站只能保AP

行锁基本演示

建表

取消自动提交就是设置了行锁，不提交前具有己读性（只有自己能看到修改后的数据，其他人看旧数据）

若两边操作不同的行，则不阻塞，提交后能看到修改的数据

无索引，行锁升级为表锁：

设置了行锁，但session1更新时b为varchar却没有加单引号，导致索引失效，未提交，导致升级为表锁，session2出现等待，session1提交后，session2恢复

间隙锁

范围检索数据时，对于键值在条件范围内但并不存在的记录，叫做"间隙（ GAP )"， InnoDB 也会对这个"间隙”加锁，叫间隙锁。这种锁机制就是所谓的Next - Key 锁。

Record Lock：即行锁或排他锁，锁定一个记录上的索引，而不是记录本身。如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks依然可以使用。

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。是一个左开右闭的区间，即(x,y]的形式。

Next-Key Lock：锁定的是当前值和前面的范围，即锁定一个范围的记录和间隙，是Record Lock和Gap Lock的组合。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

GAP锁和Next Key Lock区别

区别在于锁定范围，Gap锁只锁定索引记录之间的间隙，而Next Key Lock则同时锁定索引记录和间隙。另外，Next Key Lock是MySQL中的一种锁机制，它是在Gap锁的基础上引入的。

当我们用范围条件，而不是使用相等条件检索数据（select），并请求共享或排他锁时， InnoDB 会给符合条件的已有数据进行加锁；即，一个事务在一个范围内的索引记录上执行SELECT ... FOR UPDATE语句时，MySQL会在该范围内的所有记录和间隙上设置Next Key Lock。

例子：

1
2
3
4
5
6
7
8
>set autocommit =0;
>update products set name= 'nums' where id < 10;
>commit;

会话1：用update语句更新符合条件的数据的某个字段值，更新条件为id<10，目前表中id只有1、2、3、4、6、9的数据，那么id=5,7,8的行数据也会被锁，即间隙行被锁（虽然实际数据不存在）
会话2：插入不存在但是处以id<10条件的行数据时，会进入阻塞状态，直到会话1提交事务。
如何避免：
尽量缩小where后面条件的范围。

如何主动锁定一行：

select … … for update（排它锁）; select … lock in share mode(共享锁)

结论

1555903650349

如何分析行锁

页锁

知道有这么个锁就可以，用的比较少

主从复制

基本原理

基本原则

最大问题

延时

一主一从常见配置

分布式数据库服务器的四层架构：

访问层：接收访问信息并按负荷智能的分配给中转服务器，接受数据结果并返回客户端。

中转层：接收访问服务器发来的数据访问指令，从总储存服务器寻找数据分布所在的储存服务器，发送指令。

表头层：储存数据的表头信息，以确定储存服务器位置。

处理层：分布式数据储存服务器，接收指令并执行，然后返回数据给访问服务器。

https://www.cnblogs.com/dawnstar/p/5054136.html

mysql版本一致且后台以服务运行(最少此次本号要一致)->ip地址不同，各自中ping一下对方地址-> 根据文件修改（server-id=1注释，server-id=2开启）->从机的mysql重启

在windows主机上建立账户并授权slave

1
2
3
4
5
grant replication slave on *.* to 'zhangsan'@'从机数据库ip' identified by '123456';
授权 复制 从机以 zhansan的用户名和123456的密码登录主机
登录mysql中复制入上面代码
flush privileges刷新命令
show master status；查看主机状态

File(二进制日志文件)

Position(位置)

Binlog_Do_DB需要复制的数据库（没写，代表之后新建的数据库都要复制）

Binlog_Ignore_DB不需要复制的数据库

从机从二进制日志文件mysqlbin.000035的第341开始抄

在linux从机上配置需要复制的主机

1
2
3
4
5
6
7
change master to master_host='主机ip',
master_user='zhansan',master_password='123456',
master_log_file='file名字'，master_log_pos=position数字；
linux登录mysql,复制入上面代码
start slave;启动从服务器复制功能
show slave status\G
执行后下图2个yes必须存在！否则不会成功

尝试：主机新建库、新建表、insert记录，从机复制

如何停止从服务复制功能（有时候主机的数据，从机不需要，这时用到，以及以下情况）

每次从来需要主机show master status,因为position不一样