如何锁“住”MySQL

2021-01-05 22:32:51 浏览数 (1)

MySQL锁概述

1. 锁

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

在数据库中，除传统的计算资源的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的一个重要的因素。

2. 分类

「从数据操作类型区分：」

  • 读锁。又称共享锁：针对同一份数据，多个读操作可以同时进行而不会互相影响
  • 写锁。又称排它锁：当前写操作没有完成前，他会阻断其他写锁和读锁

「从数据操作颗粒度：」

  • 表锁。在进行数据操作的时候，将一张表全部加锁。即悲观锁
  • 行锁。仅仅对操作的行进行加锁，不同行之间没有影响
2.1 表锁（偏读）

MyISAM的锁就是表锁。

MyISAM在执行查询查询语句之前默认给所有的表加读锁，在执行增删改操作之前，会自动给涉及到的表加写锁

  • 加读锁。
代码语言：javascript复制
lock table 表名 read ;
  • 加写锁。
代码语言：javascript复制
lock table 表名 write ;
  • 查看表中的所有锁
代码语言：javascript复制
show open tables ;
  • 释放表锁
代码语言：javascript复制
unlock tables ;
2.1.1 加读锁
  • 给表mylock加读锁
  • 当前MySQL会话中执行查询当前加锁表。「可以查询」
  • 当前MySQL会话中执行修改当前加锁表。「不能修改」
  • 当前MySQL会话中执行查询其他未加锁表。「不能查询」
  • 其他会话中执行查询加锁表。「可以查询」
  • 其他会话中执行修改加锁表。「进入阻塞状态」。当释放锁以后就会立即查询出数据
  • 其他会话中执行查询其他未加锁表。「可以查询」
2.1.2 加写锁
  • 添加写锁
  • 当前会话中查询加锁表。「可以查询」
  • 当前会话中查询其他未加锁表。「不能查询」
  • 当前会话中修改加锁表。「可以更改」
  • 其他会话中读取加锁表。「进入阻塞」
2.1.3 表锁加读写锁的总结
  • 对MyISAM表的读操作，不会阻塞其他进程对同一表的读操作，但会阻塞对同一个表的写操作。只有当读锁被释放以后，才可以进行写操作。
  • 对MyISAM表的写操作，会阻塞其他进程对同一表的读操作，只有当写锁释放以后，才可以进行读操作
  • 对于其他进程：读锁会阻塞写不会阻塞读，写锁将读和写都阻塞
  • 对于当前进程：读锁会禁止其他表读取，当前表写操作
2.1.4 分析表锁定
代码语言：javascript复制
show status like 'table%';
  • table_locks_immediate :产生表级锁定的次数。标识可以立即获取锁的查询次数，没立即获取锁值加一
  • table_locks_waited:出现标记锁定争用而发生的等待次数（不能获取立即获取锁的次数，没等待一次就加一）
  • table-open_cache_hits:从table share的free list中找到一个表的缓存，如果找到则加一
  • table_open_cache_misses:和上面的hits相反，如果在缓存中找不到实例则需要重新实例化，每次加一
  • table_open_cache_overflows:超过缓存区大小的实例个数
  • MyISAM的读写锁调度是以写优先，这也是MyISAM不适合做主表的引擎。因为写锁后，其他线程不能做任何的操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
2.2 行锁（偏写）

InnoDB使用行锁，锁定粒度低，发生锁冲突的概率最低，并发度也最高。

「行锁演示：」

  • MySQL在InnoDB引擎下的自动隔离级别为「事务」，每一次请求都相当于提交一次事务
  • 当前会话，取消自动提交，修改一行的值。
  • 另一个会话，读取这一行的值。「读取到的是原数据」
  • 另一个会话，更新这一行。「进程阻塞」
  • 当前进程提交事务。「另一个会话，恢复」
2.2.1 由于出现索引失效导致行锁变表锁
  • 模拟索引字段类型转换导致索引失效。
  • 插入一个整形的name，底层出现索引失效。
  • 另一个会话，出现进程阻塞

这个的主要原因是：「由于出现索引失效导致只能从全表读取，导致行锁变表锁」

2.2.2 由于出现范围写操作导致出现间隙锁
  • 更新一个范围内的字段
  • 其他会话对这个范围内的数据进行写操作

间隙锁：「MySQL在执行范围写操作的时候，会默认将这个范围内的数据全部加锁，如果当前进行没有进行提交，那么其他进程访问这个范围内的数据将会被阻塞。」

2.2.3 手动开启行锁
代码语言：javascript复制
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql>select * from 表名 where id = 3 for update #锁定id=3的行

mysql> commit ; # 如果不进行提交，那么其他进程将不能对该行进行写操作
2.2.4 分析行锁定
代码语言：javascript复制
mysql> show status like 'innodb_row_lock%';
 ------------------------------- -------- 
| Variable_name                 | Value  |
 ------------------------------- -------- 
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 397427 |
| Innodb_row_lock_time_avg      | 39742  |
| Innodb_row_lock_time_max      | 51038  |
| Innodb_row_lock_waits         | 10     |
 ------------------------------- -------- 
5 rows in set (0.00 sec)
  • Innodb_row_lock_current_waits。当前正在等待锁定的数量
  • Innodb_row_lock_time。锁定以后进程等待时间总和
  • Innodb_row_lock_time_avg。每次等待的平均时间
  • Innodb_row_lock_time_max。等待的最大时间
  • Innodb_row_lock_waits。等待次数
2.2.5 行锁优化
  • 尽可能让所有的数据检索都通过索引来完成，避免无索引导致行锁升级为表锁
  • 合理设计索引，缩小锁的范围
  • 尽可能使用准确的数值检索或者范围较小，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可能低级别事务隔离
3. 页锁

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

MySQL主从复制

1. MySQL主从复制过程

  • master将改变记录记录到二进制文件中。
  • slave将master的二进制日志文件中的记录拷贝到它的中继日志文件relay log中
  • slave读取这个中继文件，将改变应用到自己的数据库中。MySQL的复制是异步且串行化的

2. 主从复制的基本原则

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

3. 配置主从复制

3.1 主master

window和linux的配置相同，只不过修改的配置文件不一样而已。在window中修改my.ini，在linux中修改my.cnf文件。

  • 设置服务器唯一ID。server-id=1
  • 启动二进制日志文件。log-bin=一个路径/mysqlbin
  • 启动错误日志文件。log-err=一个路径/mysqlerr「可选参数」
  • 设置MySQL根目录。basedir='安装目录'「可选参数」
  • 设置临时目录。tmpdir=「可选参数」
  • 设置主机的读写情况。read-only=0读写均可
  • 设置不需要复制的数据库。binlog-lgnore-db=数据库名「可选参数」
  • 设置需要复制的数据库名字。binlog-do-db「可选参数」
3.2 从slave
  • 设置服务器唯一ID。server-id=2
  • 启用二进制文件。
3.3 其他配置
  • 重启mysql服务
  • 关闭防火墙。Linux关闭命令service iptable stop
  • 主机添加授权账户，并刷新；
代码语言：javascript复制
GRANT REPLICATION SLAVE  ON*.* TO 'zhangsan'@'从机器数据库IP‘ IDENTIFIED BY '123456';
flush privileges;
  • 查询master状态。记录File和position的值
代码语言：javascript复制
show master status ;
  • 从机配置需要启动服务的主机
代码语言：javascript复制
CHANGE MASTER TO MASTER_HOST='主机IP',
        MASTER_USER='zhangsan'，
        MASTER_PASSWORD='123456',
        MASTER_LOG_FILE='File名字'，
        MASTER_LOG_POS=Position数字；
  • 从机启动slave
代码语言：javascript复制
start slave;
# 当Slave_IO_Running:YES
# Slave_SQL_Running:YES
# 这两个字段均为YES的时候才说明主从复制配置成功

stop slave;
#停止主从复制
云数据库SQLServer 其他 数据库 sql

0 人点赞