一文搞懂数据库中的“锁”（图文详解） |
您所在的位置：网站首页 › DBL数据库锁定 › 一文搞懂数据库中的“锁”（图文详解） |
一文搞懂数据库中的“锁”（图文详解）
|
[toc] 1.锁 1.1.锁的概述锁是什么 锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。锁的分类 全局锁：锁定数据库中的所有表。 表级锁：每次操作锁住整张表。 行级锁：每次操作锁住对应的行数据。 1.2.全局锁全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML、DDL语句，已经更新操作的事务提交语句都将被阻塞 应用场景： 做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。 如果不加全局锁，先后执行数据备份和业务的数据更新操作，会导致数据不一致使用全局锁进行数据库逻辑备份的过程： 加全局锁 flush tables with read lock; mysqldump是数据库用于数据备份的工具，执行数据备份 注意：mysqldump是MySQL提供的一个工具，不是sql语句，需要在windows命令行中执行 mysqldump -uroot -p123456 user>user.sql 在加锁后，DML和DDL被阻塞，其他客户端不能写入数据，但是DQL可以执行，其他客户端可以查找数据 备份结束，得到备份后的文件，释放锁 unlock tables;
案例演示（模拟三个客户端）： 客户端A：对数据库加上全局锁 flush tables with read lock;
执行update语句--->失败 update student set name = 'A' where id = 2;
注意：mysqldump是MySQL提供的一个工具，不是sql语句，需要在windows命令行中执行
在D盘中可以看到数据已经备份完成
数据备份成功后，在客户端A中释放锁 unlock tables;此时在客户端B中update就可以正常执行。 全局锁的好处： 保证数据的完整性。全局锁的弊端： 粒度很大，如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。 如果业务数据库不是单机版而是主从结构，且做了读写分离，那么在从库上备份不会影响主库的读写操作，但是在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。其他实现一致性数据备份的方式： 在InnoDB引擎中可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。其底层是通过快照读实现。 mysqldump --single-transaction -uroot –p123456 database > database.sql 1.3.表级锁 1.3.1.概念表级锁，顾名思义，在每次操作时锁住整张表。应用在MyISAM、InnoDB、BDB等存储引擎中 1.3.2.表级锁分类 表锁 元数据锁 意向锁 1.3.3.表锁表锁分类： 表共享读锁（简称：读锁）
加锁的语法： lock tables tb1 , tb2... read / write释放锁的语法： unlock tables 或者关闭客户端连接写锁案例演示： 假设有一张表score，有两个客户端A和B，模拟读锁，以下操作依次执行： 在客户端A中给表score加读锁 lock tables score read ; 在客户端A中执行查询语句--->查询成功 select * from score; 在客户端A中执行更新语句--->更新失败，且报错 update score set math = 100 where id = 2;在客户端B中执行查询语句--->查询成功 select * from score;在客户端B中执行更新语句--->更新处于阻塞状态 update score set math = 100 where id = 2;
读锁案例演示： 假设有一张表score，有两个客户端A和B，模拟写锁，以下操作依次执行： 在客户端A中给表score加写锁 lock tables score write ; 在客户端A中执行查询语句--->查询成功 select * from score; 在客户端A中执行更新语句--->更新成功 update score set chinese = 100 where id = 2; 在客户端B中执行查询语句--->读取处于阻塞状态 select * from score; 在客户端B中执行更新语句--->更新处于阻塞状态 update score set chinese = 100 where id = 2;
元数据锁的案例演示： 以下操作按顺序依次演示。 客户端A开启事务，并对表score执行select查询操作--->成功执行 begin select * from score; 客户端B也开启事务，并对表score执行select查询、update更新操作--->成功执行 begin select * from score; update score set math = 88 where id = 1; 可以发现上述操作都能成功执行，原因是共享锁之间相互兼容，可并发进行读写操作。
意向锁的分类： 意向共享锁（IS）：与表锁共享锁（shared_read）兼容，与表锁排他锁（write）互斥。 由语句select ... lock in share mode添加 。 意向排他锁（IX）：与表锁共享锁（shared_read）及排他锁(write)都互斥，意向锁之间不会互斥。 由insert、update、delete、select...for update添加 。我们可以通过以下语句查看意向锁是否添加成功： select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;案例演示： 客户端A开启事务，然后执行select语句，会自动为id=1这行加上行锁，随后手动通过lock in share mode加上意向共享锁IS： begin; select * from score where id = 1 lock in share mode; 客户端B加上表锁共享读锁（shared_read），此时表锁添加成功： lock tables score read; 客户端B加上表锁写锁（write），此时发现被阻塞： lock tables score write;
锁之间的兼容和排斥情况：
SQL增删改查语句对应加的行锁：
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。 InnoDB的行锁是针对于索引加的锁，如果某字段没有创建索引，即不通过索引条件检索该字段的数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。我们可以通过以下语句查看行锁是否添加成功： select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks; 1.4.3.间隙锁和临键锁 默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。案例演示： 根据索引进行等值查询，且索引是唯一索引（例如主键索引)，给一个不存在的记录加行锁时, 行锁会优化为间隙锁 。 间隙锁的作用：防止其他并发事务在间隙中插入数据
如果根据索引进行等值查询，且该索引是普通索引（字段上的记录值有可能重复），那么在叶子节点中向右遍历的最后一个值不满足查询需求时，临键锁退化为间隙锁。 行锁的作用：防止单行记录被并发修改 临键锁/间隙锁的作用：防止单行前后间隙插入记录，引起幻读
如果根据索引进行范围查询，且该索引是唯一索引（如主键索引），那么会加上临键锁，会访问到不满足条件的第一个值为止。 行锁的作用：防止该行被并发修改 两个临键锁的作用：防止范围内的数据记录被并发修改
|
【本文地址】