网站建设公司需要什么,wordpress获取文章别名,phpcms做汽车网站,网站建设与搜索引擎营销有什么关系锁的类型MySQL 找那个根据加锁的范围#xff0c;大致可以分成全局锁#xff0c;表级锁和行级锁。全局锁全局锁#xff0c;就是对整个数据库加锁。加锁flush tables with read lock解锁unlock tables全局锁会让整个库处于只读状态#xff0c;之后所有的更新操作都会被阻塞大致可以分成全局锁表级锁和行级锁。全局锁全局锁就是对整个数据库加锁。加锁flush tables with read lock解锁unlock tables全局锁会让整个库处于只读状态之后所有的更新操作都会被阻塞数据更新语句数据的增删改数据定义语句包括建表、修改表结构等和更新类事务的提交语句。缺点如果对主库加锁那么执行期间就不能执行更新业务基本上就停摆了如果对从库加锁那么执行期间从库就不能执行主库同步过来的 binlog会导致主从延迟。适用范围全局锁的典型使用场景是做全库逻辑备份。也就是把整库每个表都select出来存成文本。不过为什么要在备份的时候加锁不加锁的话备份系统备份的得到的库不是一个逻辑时间点这个视图是逻辑不一致的。官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候导数据之前就会启动一个事务来确保拿到一致性视图。而由于 MVCC 的支持这个过程中数据是可以正常更新的。对于 MyISAM 这种不支持事务的引擎mysqldump 工具就不能用了所以 全局锁 虽然缺点很多但是还是有存在的必要。表级锁MySQL 中的表级别的锁包括:表锁元数据锁(meta data lockMDL)。比如 InnoDB 中的意向锁和自增锁AUTO-INC Locks也都是表级别的锁。下面来一一分析下表锁表锁就是会锁定整张表它是 MySQL 中最基本的锁策略并不依赖于存储引擎被大部分的 MySQL 引擎支持MyISAM 和 InnoDB 都支持表级锁由于表级锁一次会将整个表锁定所以可以很好的避免死锁问题。当然锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高导致并发率大打折扣。表级别的锁和全局锁一样可以使用 unlock tables 主动释放锁也可以在客户端断开的时候自动释放锁。加锁// 表级别的共享锁也就是读锁lock tables t1 read// 表级别的排它锁也就是写锁 lock tables t1 write释放锁unlock tables表锁除了会限制其它线程的读写还会限制当前线程接下来的操作。如果一个线程加了表级别的读锁其他线程对该表的写操作都会被阻塞同时当前线程接下来对该表的写入操作也不能执行会报错当前表有一个读锁直到表锁的读锁被释放。元数据锁MDLmetadata lock) 元数据也是表级别的锁。MDL 锁主要使用来维护表元数据的数据一致性MDL 不需要显式使用在访问一个表的时候会被自动加上避免在进行读取或者写入的时候其它线程对数据表做出修改造成写入或者读取的结果异常。因此在 MySQL 5.5 版本中引入了MDL当对一个表做增删改查操作的时候加MDL读锁当要对表做结构变更操作的时候加 MDL 写锁。读锁之间不互斥因此你可以有多个线程同时对一张表增删改查读写锁之间、写锁之间是互斥的用来保证变更表结构操作的安全性。因此如果有两个线程要同时给一个表加字段其中一个要等另一个执行完才能开始执行。给一个表加字段会可能会导致整个库崩掉为什么呢来分析下上面的栗子1、session A 首先启动事务使用了一个查询因为是查询所以对 user 表会加一个 MDL 读锁2、session B 因为也是查询所以也是加 MDL 读锁读读不互斥所以改该查询就正常进行了3、session C 是一个对表添加字段的操作会加 MDL 写锁因为 session A 中的读锁还没有提交读写互斥该线程就会被阻塞了4、session D 也是一个读锁不过因为 session C 加了一个写锁这时候 session D 的读锁会被 session C 阻塞因为申请 MDL 锁的操作会形成一个队列队列中写锁获取优先级高于读锁一旦出现 MDL 写锁等待会阻塞后续该表的所有 CRUD 操作。这样后面申请 MDL 读锁都会被阻塞因为对表的增删改查操作都需要先申请MDL读锁基本上这个表就完全不能读写了。如果某个表上的查询语句频繁而且客户端有重试机制也就是说超时后会再起一个新 session 再请求的话这个库的线程很快就会爆满。MDL 锁会需要等到事务提交的时候才会被释放这样在给表加字段的时候遇到一个长事务就可能会导致数据表崩掉。如何安全的对数据表添加字段呢设置 alter table 语句中的等待时间如果在指定时间没有拿到 MDL 写锁直接退出不至于长时间阻塞后面的业务操作。失败就后面多尝试几次。意向锁InnoDB 存储引擎支持多粒度锁这种锁定允许事务在行级别上的锁和表级别上的锁同时存在。这种锁就是意向锁。意向锁有两种1、意向共享锁在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前需要先在表级别加上一个「意向共享锁」2、意向独占锁在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前需要先在表级别加上一个「意向独占锁」意向锁的作用意向锁是放置在资源层次结构的一个级别上的锁以保护较低级别资源上的共享锁或排它锁。意向共享锁和意向独占锁是表级锁不会和行级的共享锁和独占锁发生冲突同时意向锁之间也不会发生冲突。只会和表级别的共享锁和表级独占锁发生冲突。意向锁是 InnoDB 自动加的不需要用户干预。这样一个事务在表中一些记录加独占锁InnoDB 就会自动加表级别的意向锁独占锁这样其他的事务如果对该表加表级别的独占锁就不用遍历表里面的记录通过表级别的意向锁直接就能判断当前事务是够会被阻塞了。简单的讲就是意向锁是为了快速判断表里面是否有记录被加锁。自增锁自增锁(AUTO-INC)是一种表级锁专门针对插入 AUTO_INCREMENT 类型的列。可以该列的值数据库会自动赋值自增的值这主要就是通过自增锁实现的。一般会在主键中设置 AUTO_INCREMENT。自增锁(AUTO-INC)采用的是一种特殊的表锁机制为了提高插入的性能锁不是在一个事务完成后才释放而是在完成是自增长值插入的 SQL 语句后立即释放。不过自增锁(AUTO-INC)在进行大量插入的时候另一个事务中的插入会被阻塞。从 MySQL 5.1.22 版本开始InnoDB 提供了一种轻量级互斥的自增实现机制大大提高了自增插入的性能。通过 innodb_autoinc_lock_mode 来控制锁的类型。innodb_autoinc_lock_mode说明0采用 AUTO-INC 锁执行语句结束后释放锁这种方式锁粒度大比较重1针对批量插入采用 AUTO-INC 锁针对简单插入采用轻量级的互斥锁如果当前有事务进行批量的数据插入后面的简单插入需要等待前面 AUTO-INC 锁的释放才可以插入这种方式可以保证同一 insert 语句插入的自增 ID 都是连续的2所有的插入操作都使用轻量的互斥锁锁的粒度小多条语句插入存在竞争自增长的值可能不连续不过当 innodb_autoinc_lock_mode 2 搭配 binlog 的日志格式是 statement 一起使用的时候在主从复制的场景中会发生数据不一致的问题。下面来分析下首先来看下 binlog 的格式binlog 有三种格式1、Statement(Statement-Based Replication,SBR)每一条会修改数据的 SQL 都会记录在 binlog 中里面记录的是执行的 SQL;Statement 模式只记录执行的 SQL不需要记录每一行数据的变化因此极大的减少了 binlog 的日志量避免了大量的 IO 操作提升了系统的性能。正是由于 Statement 模式只记录 SQL而如果一些 SQL 中包含了函数那么可能会出现执行结果不一致的情况。比如说 uuid() 函数每次执行的时候都会生成一个随机字符串在 master 中记录了 uuid当同步到 slave 之后再次执行就获取到另外一个结果了。所以使用 Statement 格式会出现一些数据一致性问题。2、Row(Row-Based Replication,RBR)不记录 SQL 语句上下文信息仅仅只需要记录某一条记录被修改成什么样子;Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。比如一个修改满足条件的数据有 100 行则会把这 100 行数据详细记录在 binlog 中。当然此时binlog 文件的内容要比第一种多很多。不过 Row 格式也有一个很大的问题那就是日志量太大了特别是批量 update、整表 delete、alter 表等操作由于要记录每一行数据的变化此时会产生大量的日志大量的日志也会带来 IO 性能问题。3、Mixed(Mixed-Based Replication,MBR)Statement 和 Row 的混合体。在 Mixed 模式下系统会自动判断该用 Statement 还是 Row一般的语句修改使用 Statement 格式保存 binlog;对于一些 Statement 无法准确完成主从复制的操作则采用 Row 格式保存 binlog。下面分析下 当 innodb_autoinc_lock_mode 2 搭配 binlog 的日志格式是 statement 一起使用的时候在主从复制的场景中为什么会发生数据不一致。CREATETABLE t (id int(11) NOTNULL AUTO_INCREMENT,c int(11) DEFAULTNULL,d int(11) DEFAULTNULL,PRIMARY KEY (id),UNIQUE KEY c (c)
) ENGINEInnoDB;session Asession Binsert into t values(null,1,1)insert into t values(null,2,2)insert into t values(null,3,3)insert into t values(null,4,4)create table t2 like t;insert into t2 values(null,5,5)insert into t2(c,d) select c,d from t;分析下上面语句的执行首先 session A 先写入了4条数据然后 session B 创建了相同的 t2 表。接下来 session A 和 session B 在相同的时刻写入数据到表 t 中。因为 innodb_autoinc_lock_mode 2 插入语句在申请万自增主键之后就会马上释放自增锁不需要等待插入语句执行完成。那么就可能出现下面的情况1、session B 首先插入语句 (1,1,1),(2,2,2),(3,3,3);2、session A 申请到了自增的 id 4,插入数据 (4,5,5);3、session B 继续执行插入数据 (5,4,4)。这样看下来没什么影响表 t 中的数据也都插入到了 t2 中只是主键 ID 有点不同。当 binlog_formatstatement 的时候在来看下 binlog 是如何同步从库的数据。因为两个 session 是同时插入数据的binlog 对表 t2 的更新日志只会有两种情况先记录 session A 的或者先记录 session B 的同时 binlog 在从库中的数据执行也都是顺序性的生成的id都是连续的不会出现主库中两个 session 并行间隙插入的情况这样就会出现从库和主库数据不一致的情况。如何解决呢可以设置 binlog 的类型为 row这样 binlog 会把插入数据的操作都如实记录进来到备库执行的时候不再依赖于自增主键去生成同时 innodb_autoinc_lock_mode 设置为 2。对于普通的 insert 语句里面包含多个 value 值即使 innodb_autoinc_lock_mode 设置为 1也不会等语句执行完成才释放锁。因为这类语句在申请自增 id 的时候是可以精确计算出需要多少个 id 的然后一次性申请申请完成后锁就可以释放了。对于批量的数据插入类似 insert … select、replace … select和 load data 语句。这种是不能这样操作的因为不知道预先要申请多少个 ID。批量的数据插入如果一个个的申请 id,不仅速度很慢同时也会影响插入的性能这肯定是不合适的。因此对于批量插入数据的语句MySQL有一个批量申请自增id的策略1、语句执行过程中第一次申请自增id会分配1个2、1个用完以后这个语句第二次申请自增id会分配2个3、2个用完以后还是这个语句第三次申请自增id会分配4个4、依此类推同一个语句去申请自增id每次申请到的自增id个数都是上一次的两倍。insertinto t values(null, 1,1);
insertinto t values(null, 2,2);
insertinto t values(null, 3,3);
insertinto t values(null, 4,4);
createtable t2 like t;
insertinto t2(c,d) select c,d from t;
insertinto t2 values(null, 5,5);insert…select实际上往表 t2 中插入了 4 行数据。但是这四行数据是分三次申请的自增 id第一次申请到了 id1第二次被分配了 id2 和 id3 第三次被分配到 id4 到 id7。由于这条语句实际只用上了 4 个 id所以 id5 到 id7 就被浪费掉了。之后再执行 insert into t2 values(null, 5,5)实际上插入的数据就是8,5,5)。所以总结下来数据插入主键 ID 不连续的情况大概有下面几种1、事务回滚事务在执行过程中出错主键冲突或者主动发生回滚会导致已经申请的自增 ID 被弃用2、批量数据插入的插入优化批量数据插入 MySQL 会有一个批量的预申请自增 ID 的策略。行锁MySQL 的行锁是在引擎层由各个引擎自己实现的但并不是所有的引擎都支持行锁的比如 MyISAM 引擎就不支持行锁InnoDB 是支持行锁的这也是 MyISAM 被 InnoDB 替代的重要原因之一。下面主要来介绍下 InnoDB 中的行锁。行锁主要有下面三类1、Record Lock记录锁也就是仅仅把一条记录锁上2、Gap Lock间隙锁锁定一个范围但是不包含记录本身3、Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。Record LockRecord Lock 记录锁这个很好理解比如事务 A 更新了一行而这时候事务 B 也更新了同一行则必须等待事务 A 的操作完成才能更新。在 InnoDB 事务中行锁是在需要的时候才加上的但并不是不需要了就立刻释放而是要等到事务结束时才释放。所以当事务中需要锁多个行要把最可能造成锁冲突。最可能影响并发度的锁尽量往后放。同时记录锁还分成读锁和写锁共享锁S锁也叫读锁满足读读共享读写互斥。独占锁X锁也叫写锁满足写写互斥、读写互斥。如果一个事务 A 对一条数据加了读锁那么另外一个事务 B 如果同样也加了读锁这两个事务不会互斥都能正常读取如果事务 B 加的是写锁那么事务 B 就需要等待事务 A 的读锁释放之后才能操作加锁。如果一个事务 A 对一条数据加了写锁那么其他的事务对这条数据加锁无论是读锁还是写锁都需要等待事务 A 释放才能继续加锁。如何加锁//对读取的记录加共享锁select ... lockin share mode;//对读取的记录加独占锁select ... for update;不过需要注意的是当一条记录被加了记录锁其它事务如果只是简单的查询没有使用当前读那么是不会被阻塞的因为会通过 MVCC 找到当前可读取的版本数据直接返回数据即可。Gap Lock间隙锁Gap Lock是 Innodb 在可重复读提交下为了解决幻读问题时引入的锁机制。幻读的问题存在是因为新增或者更新操作这时如果进行范围查询的时候加锁查询会出现不一致的问题这时使用普通的行锁已经没有办法满足要求需要对一定范围内的数据进行加锁间隙锁就是解决这类问题的。如上图上面加了 id 范围为 (4,6) 的间隙锁 ,那么其他事务进行 id 为 5 的主键数据插入就会被阻塞直到间隙锁被释放。间隙锁之间是兼容的两个事务之间可以共同持有包含相同间隙的间隙锁同时也不存在互斥关系间隙锁的目的只是为了解决幻读问题而提出的。Next-Key LockNext-Key Lock是 Record Lock Gap Lock 的组合锁定一个范围并且锁定记录本身。如果对 id 范围为 (4,6] 加了 Next-Key Lock除了 id 为 5 的主键数据插入就会被阻塞同时 id 为 5 的主键数据修改也会被阻塞。插入意向锁插入意向锁是一种间隙锁形式的意向锁在真正执行 INSERT 操作之前设置。一个事务在数据插入的是时候会判断插入的位置是否加了被其它的事务加了间隙锁或临键锁。如果有的话就会阻塞直到间隙锁或临键锁被释放才能执行后面的插入。因为插入意向锁也是一种意向锁意向锁只是表示一种意向所以插入意向锁之间不会互相冲突多个插入操作同时插入同一个 gap 时无需互相等待。加锁的原则分析完了 MySQL 中几个主要的锁,再来看下这几个锁在 MySQL 中的使用。这里引用下丁奇大佬在极客时间专栏中的总结MySQL 后面的版本可能会改变加锁策略这个原则是在下面版本中总结的5.x系列5.7.248.0系列 8.0.13。加锁原则1、加锁的对象是索引加锁的基本单位是 next-key lock是前开后闭区间2、只有语句查找过程中访问到的对象才会加锁加锁优化1、索引中的等值查询如果加锁的对象是唯一索引这时候锁就从 next-key lock变成行锁了因为只需要锁住对应的一行就行了2、索引中的等值查询索引是唯一索引查询的值没有找到或者索引是普通索引 就会锁住一个范围向右遍历最后一个不满足条件的值并将其锁住这时候next-key lock就会变成 Gap Lock 了这个条件是确定加锁的右边范围同时唯一索引上的范围查询会访问到不满足条件的第一个值为止。下面来几个栗子来具体的分析下CREATETABLE t (
id int(11) NOTNULL,
c int(11) DEFAULTNULL,
d int(11) DEFAULTNULL,
PRIMARY KEY (id),
KEY c (c)
) ENGINEInnoDB;insertinto t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);1、主键等值查询session Asession Bsession Cbeginupdate t set dd1 where id7insert into t values(8,8,8)(blocked)update t set dd1 where id10可以看到 session B 中的插入操作被阻塞了下面来分析下session A1、加锁的对象是索引加锁的基本单位是 next-key lock首先会加 next-key lock (5,10]2、又因为 id 7 这行数据数据库中不存在索引中的查询如果没有找到对应的数据就会锁住满足条件的范围数据向右遍历最后一个不满足条件的值并将其锁住这时候next-key lock就会变成 Gap Lock 了所以 session A 首先会加一把 (5,10) 的间隙锁。session B因为是数据插入操作会加插入意向锁因为 (5,10)已经被 session A 锁住了所以 id 8 的数据就不能插入了会被阻塞。session Csession C 因为 id 10 这行数据是存在的并且 id 为主键索引。根据上面的加锁原则首先加锁的基本单位是 next-key lock索引中的等值查询如果加锁的对象是唯一索引这时候锁就从 next-key lock变成行锁了。2、非唯一索引等值查询session Asession Bsession Csession Dbeginselect id from t where c5 lock in share modeupdate t set dd1 where id5insert into t values(7,7,7)(blocked)insert into t values(2,2,2)(blocked)可以看到 session C 被阻塞了下面来分析下session A首先 session A 加了一个 id 5 的读锁。1、加锁的基本单位是 next-key lock首先会加 next-key lock (0,5]2、索引中的等值查询索引是唯一索引查询的值没有找到或者索引是普通索引 就会锁住一个范围向右遍历最后一个不满足条件的值并将其锁住这时候next-key lock就会变成 Gap Lock了 (5,10) 同样也会被锁住这样加锁的范围就是 (0,10)session B只有访问到的对象才会加锁session A 的查询使用覆盖索引并不需要访问主键索引所以主键索引上上的查询不会被阻塞。session C 和 session BD两个插入操作都会加插入意向锁因为间隙 (0,10) 被 session A 锁住了所以插入操作就会被阻塞了。3、主键索引范围锁例如下面的栗子select*from t where id10forupdate; select*from t where id10and id11forupdate;上面这两个查询看起来是等价的其实他们加锁的方式是不同的下面来分析下session Asession Bsession Cbeginselect * from t where id10 and id 11 for updateinsert into t values(8,8,8)(Query OK)insert into t values(13,13,13)(blocked)update t set dd1 where id15(blocked)可以看到上面插入的 id13 的数据会被阻塞还有 id15 的数据修改也会被阻塞下面来分析下session A首先 id10 这个条件按照加锁的基本对象首先加 next-key lock (5,10]因为 id 是主键等值查询会退化成行锁所以 next-key lock (5,10]就会退化成 id10 的行锁同时因为是范围查询向右查询所以右边界会找到 15会加 next-key lock (10,15];所以加的锁就是 (10,15] 的 next-key lock 还有 id10 的行锁。4、非唯一索引范围查询session Asession Bsession Cbeginselect * from t where c10 and c 11 for updateinsert into t values(8,8,8)(blocked)update t set dd1 where id15(blocked)可以看到上面插入的 id13 的数据会被阻塞还有 id15 的数据修改也会被阻塞下面来分析下session A按照加锁的基本对象首先加 c 的 next-key lock (5,10]因为 c 是普通索引等值查询不会退化成行锁同时因为是范围查询向右查询所以右边界会找到 15会加 c 的 next-key lock (10,15];所以 session A 会加索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。5、非唯一索引等值查询假定目前表中的数据见下文有两条 c10 的数据。来看下下面的查询栗子session Asession Bsession Cbeginselect * from t where c10 for updateinsert into t values(12,12,12)(blocked)update t set dd1 where id15可以看到上面 session B 中 id12 的数据插入被阻塞了来分析下原因session A查询的条件是 c10,上面的图示可以看到 c10 的书有两条首先加锁的基本单位是 next-key lock所以会加一个 (c5,id5) 到 (c10,id10) 的 next-key lock因为这是个等值查询的索引索引中的等值查询索引是唯一索引查询的值没有找到或者索引是普通索引 就会锁住一个范围向右遍历最后一个不满足条件的值并将其锁住这时候next-key lock就会变成 Gap Lock 了所以 (c10,id10) 到 (c15,id15) 也会被加一个间隙锁。所以 session A 中的锁就是 (c5,id5) 到 (c15,id15) 的间隙锁。6、limit 语句加锁session Asession Bbeginselect * from t where c10 limit 2 for updateinsert into t values(12,12,12)session A 中加入了 limit 查询还是栗子5 中的插入语句这时候 session B 的插入就不会被阻塞了。因为有 limit 2 的限制因此在遍历到 (c10, id13) 这一行之后满足条件的语句已经有两条循环就结束了。因此索引c上的加锁范围就变成了从c5,id5) 到 (c10,id13) 这个前开后闭区间。所以业务中如果加入 limit 条件能够减小锁的范围。总结锁大概分成三类 全局锁表级锁和行锁。加锁原则1、加锁的对象是索引加锁的基本单位是 next-key lock是前开后闭区间2、只有语句查找过程中访问到的对象才会加锁加锁优化1、索引中的等值查询如果加锁的对象是唯一索引这时候锁就从 next-key lock变成行锁了因为只需要锁住对应的一行就行了2、索引中的等值查询索引是唯一索引查询的值没有找到或者索引是普通索引 就会锁住一个范围向右遍历最后一个不满足条件的值并将其锁住这时候 next-key lock就会变成 Gap Lock 了这个条件是确定加锁的右边范围同时唯一索引上的范围查询会访问到不满足条件的第一个值为止。
