网站备案需要资料,flash属于网页制作平台吗,制作网站报价单,外贸通过哪些渠道找客户一、锁的基本概念
1、锁的定义
锁是协调多个进程或线程并发访问数据库资源的一种机制。
MySQL中的锁是在服务器层或者存储引擎层实现的#xff0c;保证了数据访问的一致性与有效性。但加锁是消耗资源的#xff0c;锁的各种操作#xff0c;包括获得锁、检测锁是否已解除、…一、锁的基本概念
1、锁的定义
锁是协调多个进程或线程并发访问数据库资源的一种机制。
MySQL中的锁是在服务器层或者存储引擎层实现的保证了数据访问的一致性与有效性。但加锁是消耗资源的锁的各种操作包括获得锁、检测锁是否已解除、释放锁等 都会增加系统的开销。
2、锁的分类
MySQL锁可以按模式分类为乐观锁与悲观锁。按粒度分可以分为全局锁、表级锁、页级锁、行级锁。按属性可以分为共享锁、排它锁。按状态分为意向共享锁、意向排它锁。按算法分为间隙锁、临键锁、记录锁。 二、全局锁
1、定义
全局锁就是对整个数据库实例加锁。
2、使用全局锁
要使用全局锁则要执行这条命令
flush tables with read lock 执行后整个数据库就处于只读状态了这时其他线程执行以下操作都会被阻塞
对数据的增删改操作比如 insert、delete、update等语句对表结构的更改操作比如 alter table、drop table 等语句。
3、释放全局锁
如果要释放全局锁则要执行这条命令
unlock tables
当会话断开了全局锁也会被自动释放。
4、应用场景
全局锁主要应用于做全库逻辑备份这样在备份数据库期间不会因为数据或表结构的更新而出现备份文件的数据与预期的不一样。 假设如果在全库逻辑备份期间有用户购买了一件商品一般购买商品的业务逻辑是会涉及到多张数据库表的更新比如在用户表更新该用户的余额然后在商品表更新被购买的商品的库存。如果在备份用户表和商品表之间有用户购买了商品。 这种情况下备份的结果是用户表中该用户的余额并没有扣除但是商品表中该商品的库存被减少了如果后面用这个备份文件恢复数据库数据的话用户钱没少而库存少了等于用户白嫖了一件商品。 所以在全库逻辑备份期间加上全局锁就不会出现上面这种情况了。 【缺点】
如果在主库上备份那么在备份期间都不能执行更新业务基本上就能停止。
如果在从库上备份那么备份期间从库不能执行主库同步过来的binlog会导致主从延迟。
【全局锁影响业务的解决方案】
如果数据库的引擎支持的事务支持可重复读的隔离级别那么在备份数据库之前先开启事务会先创建 Read View然后整个事务执行期间都在用这个 Read View而且由于 MVCC 的支持备份期间业务依然可以对数据进行更新操作。
因为在可重复读的隔离级别下即使其他事务更新了表的数据也不会影响备份数据库时的 Read View这就是事务四大特性中的隔离性这样备份期间备份的数据一直是在开启事务时的数据。
备份数据库的工具是 mysqldump在使用 mysqldump 时加上 –single-transaction 参数的时候就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。
InnoDB 存储引擎默认的事务隔离级别正是可重复读因此可以采用这种方式来备份数据库。
但是对于 MyISAM 这种不支持事务的引擎在备份数据库时就要使用全局锁的方法。 三、表级锁
MySQL 里面表级别的锁有这几种
表锁元数据锁MDL;意向锁AUTO-INC 锁
1、表锁
表锁是指对一整张表加锁。MyISAM 默认锁与 InnoDB 都支持表级锁定。 读锁read lock也叫共享锁shared lock 针对同一份数据多个读操作select可以同时进行而不会互相影响 写锁write lock也叫排他锁exclusive lock 当前操作没完成之前会阻塞其它读select和写操作update、insert、delete 【表锁的创建】
//表级别的共享锁也就是读锁
lock table t_student read;//表级别的独占锁也就是写锁
lock table t_stuent write;
【表锁的释放】
unlock tables 此命令会释放当前会话的所有表锁。
【优缺点】
优点开销小、加锁快、无死锁
缺点表锁的粒度大发生锁冲突概率大会影响并发性能低。
【建议】
MyISAM的读写锁调度是写优先这也是MyISAM不适合做写为主表的引擎因为写锁以后其它线程不能做任何操作大量的更新使查询很难得到锁从而造成永远阻塞。
【示例演示】
(1) 表锁的读锁示例
session01 session02lock table t_student read;// 上读锁select * from t_student; // 可以正常读取select * from t_student;// 可以正常读取 update t_student set name 3 where id 2; // 报错,因被上读锁不能写操作 update t_student set name 3 where id 2; // 被阻塞 unlock tables;// 解锁update teacher set name 3 where id 2;// 更新操作成功
(2) 表锁的写锁示例
session01 session02lock table t_student write;// 上写锁select * from t_student ; // 可以正常读取select * from t_student ;// 被阻塞 update t_student set name 3 where id 2; // 可以正常更新操作 update t_student set name 4 where id 2; // 被阻塞 unlock tables;// 解锁select * from t_student ;// 读取成功 update t_student set name 4 where id 2; // 更新操作成功 2、元数据锁MDL,metadata lock
MDL主要作用是维护表元数据的数据一致性在表上有活动事务显式或隐式的时候不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁来保护表的元数据信息用于解决或者保证DDL操作与DML操作之间的一致性。
我们不需要显式的使用 MDL因为当我们对数据库表进行操作时会自动给这个表加上 MDL
对一张表进行 DML操作时加的是 MDL 读锁对一张表进行 DDL操作表结构变更操作时加的是 MDL 写锁 总结读读共享读写互斥写写互斥 当有线程在执行 select 语句 加 MDL 读锁的期间如果有其他线程要更改该表的结构 申请 MDL 写锁那么将会被阻塞直到执行完 select 语句 释放 MDL 读锁。
反之当有线程对表结构进行变更 加 MDL 写锁的期间如果有其他线程执行了 CRUD 操作 申请 MDL 读锁那么就会被阻塞直到表结构变更完成 释放 MDL 写锁。 MDL 不需要显式调用那它是在什么时候释放的? MDL 是在事务提交后才会释放这意味着事务执行期间MDL 是一直持有的。
那如果数据库有一个长事务所谓的长事务就是开启了事务但是一直还没提交那在对表结构做变更操作的时候可能会发生意想不到的事情比如下面这个顺序的场景
首先线程 A 先启用了事务但是一直不提交然后执行一条 select 语句此时就先对该表加上 MDL 读锁然后线程 B 也执行了同样的 select 语句此时并不会阻塞因为「读读」并不冲突接着线程 C 修改了表字段此时由于线程 A 的事务并没有提交也就是 MDL 读锁还在占用着这时线程 C 就无法申请到 MDL 写锁就会被阻塞
那么在线程 C 阻塞后后续有对该表的 select 语句就都会被阻塞如果此时有大量该表的 select 语句的请求到来就会有大量的线程被阻塞住这时数据库的线程很快就会爆满了。 为什么线程 C 因为申请不到 MDL 写锁而导致后续的申请读锁的查询操作也会被阻塞 这是因为申请 MDL 锁的操作会形成一个队列队列中写锁获取优先级高于读锁一旦出现 MDL 写锁等待会阻塞后续该表的所有 DML操作。
所以为了能安全的对表结构进行变更在对表结构变更前先要看看数据库中的长事务是否有事务已经对表加上了 MDL 读锁如果可以考虑 kill 掉这个长事务然后再做表结构的变更。 3、意向锁
为了支持在不同粒度上进行加锁操作InnoDB存储引擎支持一种额外的锁方式称之为意向锁。意向锁是将锁定的对象分为多个层次意向锁意味着事务希望在更细粒度上进行加锁。
InnoDB存储引擎支持意向锁设计比较简练其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁 意向共享锁IS Lock事务想要获得一张表中某几行的共享锁。 意向排他锁IX Lock事务想要获得一张表中某几行的排他锁。
如果没有「意向锁」那么加「排他表锁」时就需要遍历表里所有记录查看是否有记录存在行排他锁这样效率会很慢。
那么有了「意向锁」由于在对记录加行排他锁前先会加上表级别的意向排他锁那么在加「独占表锁」时直接查该表是否有意向排他锁如果有就意味着表里已经有记录被加了行排他锁这样就不用去遍历表里的记录。
所以意向锁的目的是为了快速判断表里是否有记录被加锁。
由于InnoDB存储引擎默认支持的是行级别的锁因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下图所示。
表级IS表级IX表级S表级X表级IS兼容兼容兼容不兼容表级IX兼容兼容不兼容不兼容行级S兼容不兼容兼容不兼容行级X不兼容不兼容不兼容不兼容 快速记忆意向锁之间不会发生冲突表锁和行锁满足读读共享、读写互斥、写写互斥。 4、自增锁AUTO-INC
表里的主键通常都会设置成自增的这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。
之后可以在插入数据时可以不指定主键的值数据库会自动给主键赋值递增的值这主要是通过 AUTO-INC 锁实现的。
AUTO-INC 锁是特殊的表锁机制锁不是再一个事务提交后才释放而是再执行完插入语句后就会立即释放。
在插入数据时会加一个表级别的 AUTO-INC 锁然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值等插入语句执行完成后才会把 AUTO-INC 锁释放掉。
那么一个事务在持有 AUTO-INC 锁的过程中其他事务的如果要向该表插入语句都会被阻塞从而保证插入数据时被 AUTO_INCREMENT 修饰的字段的值是连续递增的。
但是 AUTO-INC 锁在对大量数据进行插入的时候会影响插入性能因为另一个事务中的插入会被阻塞。
因此 在 MySQL 5.1.22 版本开始InnoDB 存储引擎提供了一种轻量级的锁来实现自增。
一样也是在插入数据的时候会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁然后给该字段赋值一个自增的值就把这个轻量级锁释放了而不需要等待整个插入语句执行完后才释放锁。
InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量是用来控制选择用 AUTO-INC 锁还是轻量级的锁。
当 innodb_autoinc_lock_mode 0就采用 AUTO-INC 锁语句执行结束后才释放锁当 innodb_autoinc_lock_mode 2就采用轻量级锁申请自增主键后就释放锁并不需要等语句执行后才释放。当 innodb_autoinc_lock_mode 1 普通 insert 语句自增锁在申请之后就马上释放类似 insert … select 这样的批量插入数据的语句自增锁还是要等语句结束后才被释放
当 innodb_autoinc_lock_mode 2 是性能最高的方式但是当搭配 binlog 的日志格式是 statement 一起使用的时候在「主从复制的场景」中会发生数据不一致的问题。
举个例子考虑下面场景 session A 往表 t 中插入了 4 行数据然后创建了一个相同结构的表 t2然后两个 session 同时执行向表 t2 中插入数据。
如果 innodb_autoinc_lock_mode 2意味着「申请自增主键后就释放锁不必等插入语句执行完」。那么就可能出现这样的情况
session B 先插入了两个记录(1,1,1)、(2,2,2)然后session A 来申请自增 id 得到 id3插入了3,5,5)之后session B 继续执行插入两条记录 (4,3,3)、 (5,4,4)。
可以看到session B 的 insert 语句生成的 id 不连续。
当「主库」发生了这种情况binlog 面对 t2 表的更新只会记录这两个 session 的 insert 语句如果 binlog_formatstatement记录的语句就是原始语句。记录的顺序要么先记 session A 的 insert 语句要么先记 session B 的 insert 语句。
但不论是哪一种这个 binlog 拿去「从库」执行这时从库是按「顺序」执行语句的只有当执行完一条 SQL 语句后才会执行下一条 SQL。因此在从库上「不会」发生像主库那样两个 session 「同时」执行向表 t2 中插入数据的场景。所以在备库上执行了 session B 的 insert 语句生成的结果里面id 都是连续的。这时主从库就发生了数据不一致。
要解决这问题binlog 日志格式要设置为 row这样在 binlog 里面记录的是主库分配的自增值到备库执行的时候主库的自增值是什么从库的自增值就是什么。
所以当 innodb_autoinc_lock_mode 2 时并且 binlog_format row既能提升并发性又不会出现数据一致性问题。 四、行级锁
1、行锁
行锁就是锁住表里面的一行数据。
行级锁是粒度最低的锁发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大容易发生死锁现象。
MySQL中只有InnoDB支持行级锁默认锁而 MyISAM 引擎并不支持行级锁。 行级锁分为共享锁S,读锁和排他锁X写锁。 读锁read lock也叫共享锁shared lock 允许一个事务去读一行阻止其他事务获得相同数据集的排他锁 写锁write lock也叫排他锁exclusive lock 允许获得排他锁的事务更新数据阻止其他事务取得相同数据集的共享锁和排他锁 //对读取的记录加共享锁
select ... lock in share mode;//对读取的记录加独占锁
select ... for update;
【示例演示】
1行级读锁示例
session01 session02begin; select * from t_student where id 2 lock in share mode;// 上读锁 select * from t_student where id 2; // 可以正常读取 update t_student set name 3 where id 2; // 可以更新操作 updatet_student set name 5 where id 2; // 被阻塞 commit;update t_student set name 5 where id 2;// 更新操作成功
2行级写锁示例
session01 session02begin; select * from t_student where id 2 for update; // 上写锁 select * from t_student where id 2; // 可以正常读取 update t_student set name 3 where id 2; // 可以更新操作 update t_student set name 5 where id 2; // 被阻塞 rollback; update t_student set name 5 where id 2; // 更新操作成功
【行级锁算法】
行级锁的类型主要有三类或者说是三种行级锁的算法
Record Lock记录锁也就是仅仅把一条记录锁上Gap Lock间隙锁锁定一个范围但是不包含记录本身Next-Key LockRecord Lock Gap Lock 的组合锁定一个范围并且锁定记录本身 2、 Record Lock记录锁
单个行记录上的锁。Record Lock总是会去锁住索引记录如果InnoDB存储引擎表建立的时候没有设置任何一个索引这时InnoDB存储引擎会使用隐式的主键来进行锁定 select * from t_test where id 1 for update;
上述就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁这样其他事务就无法对这条记录进行修改了。
【注意】
id 列必须为唯一索引列或主键列否则上述语句加的锁就会变成临键锁。
同时查询语句必须为精准匹配不能为 、、like等否则也会退化成临键锁 3、Gap Lock间隙锁
当我们用范围条件而不是等值条件检索数据并请求共享或排他锁时InnoDB对于键值在条件范围内但并不存在的记录叫做“间隙GAP)”加锁这种锁机制就是所谓的间隙锁。
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
上述所有 id 在110区间内的记录行都会被锁住所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞但是 1 和 10 两条记录行并不会被锁住。
间隙锁用于解决并发访问的幻读问题。 4、Next-Key Lock临键锁
临键锁是记录锁与间隙锁的组合它的封锁范围既包含索引记录又包含索引区间。
每个数据行上的非唯一索引列上都会存在一把临键锁当某个事务持有该数据行的临键锁时会锁住一段左开右闭区间的数据。需要强调的一点是InnoDB 中行级锁是基于索引实现的临键锁只与非唯一索引列有关在唯一索引列包括主键列上不存在临键锁。
InnoDB 在Repeatable Read隔离级别下Next-key Lock 算法是默认的行记录锁定算法。 5、行级锁加锁规则难点
MySQL 行级锁的加锁规则。
【唯一索引等值查询】
当查询的记录是「存在」的在索引树上定位到这一条记录后将该记录的索引中的 next-key lock 会退化成「记录锁」。当查询的记录是「不存在」的在索引树找到第一条大于该查询记录的记录后将该记录的索引中的 next-key lock 会退化成「间隙锁」。
【非唯一索引等值查询】
当查询的记录「存在」时由于不是唯一索引所以肯定存在索引值相同的记录于是非唯一索引等值查询的过程是一个扫描的过程直到扫描到第一个不符合条件的二级索引记录就停止扫描然后在扫描的过程中对扫描到的二级索引记录加的是 next-key 锁而对于第一个不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。同时在符合查询条件的记录的主键索引上加记录锁。当查询的记录「不存在」时扫描到第一条不符合条件的二级索引记录该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录所以不会对主键索引加锁。
非唯一索引和主键索引的范围查询的加锁规则不同之处在于
唯一索引在满足一些条件的时候索引的 next-key lock 退化为间隙锁或者记录锁。非唯一索引范围查询索引的 next-key lock 不会退化为间隙锁和记录锁。 五、死锁
死锁指两个或者多个事务在同一资源上相互占用并请求锁定对方占用的资源从而导致恶性循环的现象。 死锁产生的条件 1. 互斥条件一个资源每次只能被一个进程使用 2. 请求与保持条件一个进程因请求资源而阻塞时对已获得的资源保持不放 3. 不剥夺条件进程已获得的资源在没有使用完之前不能强行剥夺 4. 循环等待条件多个进程之间形成的一种互相循环等待的资源的关系 解决方法
1. 查看死锁show engine innodb status \G 2. 自动检测机制超时自动回滚代价较小的事务innodb_lock_wait_timeout 默认50s 3. 人为解决kill阻塞进程show processlist 4. wait for graph 等待图主动检测
六、总结 参考链接
一张图彻底搞懂 MySQL 的锁机制 | MySQL 技术论坛
MySQL 有哪些锁 | 小林coding
把MySQL中的各种锁及其原理都画出来 - 知乎
