九、MySQL锁机制 十、复制

九、MySQL锁机制

1、表锁

MylSAM引擎使用表锁，并且不支持事务

SQL语句

--展示表是否加锁
SHOW OPEN TABLES;

--加锁 read (读锁) write (写锁)
LOCK TABLE table1 read(write), table2 read(write)...

--全部解锁
UNLOCK TABLES;

读锁

• 主机A给表加上**表锁（读锁）**以后
  • 主机A和其他主机都可以读取该表的信息
  • 主机A不能读取库中其他表的信息，但其他主机可以读取库中所有表的信息
  • 如果要修改被锁表的信息
    • 主机A如果对表进行修改，会修改失败
    • 其他主机对表进行修改，会被阻塞，直到锁被释放

演示

• 给dept表加锁并查询状态

  LOCK TABLE dept READ;
  
  SHOW OPEN TABLES;
  

  

读取

• 两个客户端分别读取dept表的信息，都能读出来

  SELECT id FROM dept WHERE id = 1;
  

• 客户端A（加锁端）A读取其他表信息，读取失败

  SELECT * FROM t_emp;
  

  

• 其他客户端读取度其他表信息，读取成功

  SELECT * FROM t_emp;
  

  

修改

• 客户端A对表中内容进行修改，修改失败

  DELETE FROM dept WHERE id = 1;
  

  

• 客户端B对表中内容进行修改，进入阻塞状态

  DELETE FROM dept WHERE id = 1;
  

• 从客户端A解锁后，客户端B修改成功

  UNLOCK TABLES;
  

  

写锁

主机A给表加上**表锁（写锁）**以后

• 主机A可以读取该表信息，但其他主机读取时，会进入阻塞状态，知道读锁被释放
• 主机A不能读取库中其他表的信息，但其他主机可以读取库中除该表以外所有表的信息
• 如果要修改被锁表的信息
  • 主机A如果对表进行修改，修改成功
  • 其他主机对表进行修改，会被阻塞，直到锁被释放

演示

• 给dept表加上写锁并查看

  LOCK TABLE dept WRITE;
  
  SHOW OPEN TABLES;
  

  

读取

• 客户端A查询该表内容，查询成功；读取其他表，读取失败

  SELECT * FROM dept;
  
  SELECT * FROM t_emp;
  

  

• 其他表读取该表信息，进入阻塞状态

  SELECT * FROM dept;
  

  

• 释放后，读取成功

  UNLOCK TABLES;
  

修改

• 客户端A修改该表内容，修改成功

  DELETE dept WHERE id = 2;
  

  

• 客户端A修改其他表内容，修改失败

  DELETE FROM t_emp WHERE id = 2;
  

  

• 其他客户端修改该表内容，进入阻塞状态

  DELETE FROM t_emp WHERE id = 2;
  

总结

读锁不会阻塞读，只会阻塞写。但是写锁会阻塞读和写。

2、行锁

InnoDB使用行锁，并且支持事务，事务相关可参考 MySQL基础

特点

如果两个客户端对同一条记录进行修改

• 客户端A修改后，未提交（未commit），此时客户端B修改，则会阻塞
• 客户端A修改后，提交后，客户端B再修改，则不会阻塞

如果两个客户端分别对不同的记录进行修改，则不会被阻塞

修改同一条记录

--关闭自动提交
SET autocommit = 0;

--客户端A、B查询id=2的记录
SELECT * FROM t_emp WHERE id = 2;

--客户端A进行修改操作（将年龄改为了80），但未提交
UPDATE t_emp SET age = 80 WHERE id = 2;

--客户端A进行查询
SELECT * FROM t_emp WHERE id = 2;

--客户端B进行查询
SELECT * FROM t_emp WHERE id = 2;

--客户端B进行修改（客户端A未提交）
UPDATE t_emp SET age = 90 WHERE id = 2;

--客户端A提交
COMMIT;

--客户端B提交
COMMIT;

对应结果

客户端A查询结果

客户端B查询结果

客户端A修改后A查询

客户端A修改后B查询

客户端A修改，未提交，此时B进行修改，被阻塞

客户端A提交后，B修改成功

修改不同记录

--客户端A对id=2的年龄进行修改
UPDATE t_emp SET age = 90 WHERE id = 2;

--客户端B对id=3的年龄进行修改
UPDATE t_emp SET age = 30 WHERE id = 3;

--客户端A，B分别提交
COMMIT;
COMMIT;

因为InnoDB使用行锁，对于不同行的操作，不会出现阻塞现象

索引失效

索引失效，行锁变表锁

当索引失效后，即使多个客户端操作的不是同一条记录，如果未提交，其他客户端也会进入阻塞状态

所以要避免索引失效

比如对varchar类型的字段加了索引，但是在sql语句中却没有用单引号，尽管sql语句能够执行成功，但是索引失效，从而导致行锁变表锁，即便是别人修改其它不同的行，也会导致阻塞。这是重罪。

间隙锁危害

概念

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁

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

危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

演示

--查询表记录，此处没有id=2的记录
SELECT * FROM t_emp;

--客户端A进行范围查询，但是范围内没有id=2的记录
UPDATE t_emp SET deptId = 1 WHERE id>1 AND id < 6;

--客户端B进行插入数据，插入一条id=2的记录
INSERT t_emp VALUES(2, '岳不群', 11, 2, 100002); 

--客户端A提交
COMMIT;

--客户端B提交
COMMIT;

客户端B进入阻塞状态

提交后，插入成功

结论：可以看到表中本来没有id=2的记录，但是在客户端A进行范围修改时，客户端B对在范围内但不存在的数据进行插入时，客户端B进入了阻塞状态

锁住指定的一行

BEGIN;

--锁住指定的一行，如果进行更新操作就是 ... FOR UPDATE，删除操作就是 ... FOR DELETE 以此类推
SELECT * FROM t_emp WHERE id = 1 FOR UPDATE;

--进行修改操作
UPDATE t_emp SET NAME = '风车车' WHERE id = 1;

--提交
COMMIT;

如果当某一行被锁住后，其他客户端对改行进行操作，会被阻塞

总结

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些， 但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MylSAM高，甚至可能会更差。

十、复制

1、主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
• I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
• SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

2、读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作

读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用
• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销
• 增加冗余，提高可用性

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器