MySQL怎么隐藏密码

转载

mob64ca14038b36 2024-09-14 11:37:07

文章标签 MySQL怎么隐藏密码隔离级别锁算法 Gap Lock间隙锁 Next-Key Lock 文章分类 MySQL 数据库

读《MySQL技术内幕 InnoDB存储引擎》锁笔记
数据库管理的是磁盘上的文件，文件系统也是对磁盘文件的管理，那么数据库和文件系统有什么区别呢？
首先假设这样一个场景，用户开启两个窗口，在这2个窗口中，打开磁盘上同一个文件，起初两个窗口中看到的文件内容是相同的，现在用户在窗口1中修改了文件的内容，这个时候用户在窗口2中刷新文件内容，应该看到最新的内容吗？这得看用户自己的选择了。

选择1：窗口2看到最新的实时的内容。
选择2：窗口1中修改的文件内容保存到磁盘之后，窗口2才能看到保存之后的内容。
选择3：窗口2内容一直保持不变，从打开文件之后，自始至终看到的都是相同的内容。
选择4：窗口2不能打开已经在其他窗口打开的文件

要实现用户的这种可选择的要求，在数据库中有一个专业的名词——事物隔离级别，

用户选择	事物隔离级别
选择1	读未提交（read-uncommitted）
选择2	不可重复读（read-committed）
选择3	可重复读（repeatable-read）
选择4	串行化（serializable）

数据库的事物的隔离级别是怎么实现的呢？主要是通过锁来实现。
这样数据库就给用户提供了不同的策略，可供选择，来管理文件。显然文件系统是不具备这样的可供选择的策略，因此锁的机制成为了数据库系统区别于文件系统的一个关键特性。

锁的类型

InnoDB实现了标准的行锁：

共享锁(S Lock)：允许事物持有锁，以便读取行数据
排它锁(X Lock)：允许事物持有锁，以便更新或删除行数据

如果事物T1在表中的第r行持有一个共享锁，那么另一个事物T2请求第r行的锁，将有如下场景：
如果事物T2请求的是第r行的共享锁，那么将立即获得第r行的共享锁
如果事物T2请求的是第r行的排它锁，那么T2不能理解获得锁。
如果事物T1在第r行加了排它锁，那么事物T2在第r行无论请求加共享锁还是排它锁，都不能立即加上锁。事物T2必须等到T1在第r行的锁释放才能有机会加锁成功。

	X	S
X	不兼容	不兼容
S	不兼容	兼容

从InnoDB1.0开始，在information_schema架构下添加了表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通过这三张表用户可以简单地监控当前事物并分析可能存在的锁问题。
–查看事务

select * from information_schema.INNODB_TRX;

–查看锁

select * from information_schema.INNODB_LOCKS;

–查看锁等待

select * from information_schema.INNODB_LOCK_WAITS;

一致性非锁定读

一致性非锁定读:是指innoDB 存储引擎通过多版本控制的方式来读取当前执行时间数据库中的行的数据。如果读取的行正在执行delete，update操作，这是读取操作不会因此而进行等待行上的锁的释放，相反innodb存储引擎会执行读取行的一个快照数据

MySQL怎么隐藏密码_隔离级别

快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。

在事物隔离级别read-committed和repeatable-read，InnoDB存储引擎使用非锁定的一致性读，在read-committed事务隔离级别下，总是读取行的最新版本，如果行被锁定，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在repeatable-read事物隔离级别下，对于快照数据，非一致性读总是读取事物开始时的行数据版本。

一致性锁定读

在某些情况下，影虎需要显示第对数据库读取操作进行加锁以保证数据逻辑的一致性，而这个要求数据库支持加锁语句，即使是对于select的只读操作。InnoDB支持两种一致性的锁定读操作：

select … for update，对于读取的行加一个X锁
select … lock in share mode，对于读取的行加一个S锁

锁的算法

InnoDB存储引擎有3中行锁的算法：

Record Lock：单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
Next-Key Lock：Gap Lock与Record Lock的结合，锁定一个范围，并且锁定记录本身

InnoDB对于行的查询都是采用这种Next-Key Lock锁定算法，这样可以有效防止幻读的发生。在默认的隔离级别下，即REPEATABLE READ下，InnoDB采用Next-key Locking机制。而在READ COMMITTED下，其仅采用Record Lock。
当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。
当查询的索引含有唯一属性时，InnoDB会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围，这样提高应用的并发性。
若是辅助索引，则情况会完全不同。

create table z(
    a int,
    b int,
    primary key(a),
    key(b)
);
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

表z的列b是辅助索引，若在会话A中执行下面的SQL语句：

时间	会话A	会话B
1	begin;
2	select * from z where b=3 for update;
3	commit;

由于有两个索引，其需要分别进行加锁：

对于聚集索引，其仅在列a等于5的索引上加上Record Lock
对于辅助索引，其加上的是Next-Key Lock，锁定的范围是(1,3]。除此之外，还会对其下一个键值加上Gap Lock，即还有一个范围为(3,6)的锁。

因此，若在新会话B中运行下面的SQL语句，都会被阻塞：

第1个SQL语句：因为在会话A中执行的SQL语句已经对聚集索引中列a=5加上了X锁，因此执行会被阻塞
第2个SQL语句：主键插入4，没有问题，但是插入的辅助索引值2在锁定的范围(1,3]内，因此执行同样会阻塞
第3个SQL语句：插入的主键6没有被锁定，5也不在范围(1,3]之间。但插入的值5在另一个锁定的范围(3,6]中，故也会阻塞

select * from z where a=5 lock in share mode;
insert itno z select 4,2;
insert itno z select 6,5;

时间	会话A	会话B
1	begin;
2	select * from z where b=3 for update;
3		begin;
4		select * from z where a=5 lock in share mode; insert itno z select 4,2; insert itno z select 6,5;
5		commit;
6	commit;

而下面SQL语句不会阻塞，可以执行执行：
因为下面的辅助索引的值都不在Next-Ket Lock的范围内

insert itno z select 8,6;
insert itno z select 2,0;
insert itno z select 6,7;

锁选择

create table test(
	id int,
	v1 int,
	v2 int,
	primary key(id),
	key `idx_v1`(`v1`)
)Engine=InnoDB;

如果更新条件没有走索引，例如执行如下语句，此时会进行全表扫描，扫表的时候，要阻止其他任何的更新操作，所以上升为表锁。

update from t1 set v2=0 where v2=5;

如果更新条件为索引字段，但是并非唯一索引（包括主键索引），例如执行如下语句，那么此时更新会使用Next-Key Lock。

update from t1 set v2=0 where v1=9;

使用Next-Key Lock的原因：

首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值
还要保证锁定的区间不能插入新的数据。
如果更新条件为唯一索引，则使用Record Lock（记录锁）

解决Phantom Problem问题

在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免Phantom Problem (幻像问题）。
Phantom Problem是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。违反了事务的隔离性，即当前事务能够看到其他事务的结果。
InnoDB存储引擎采用Next-Key Locking的算法避免Phantom Problem。对于SQL语句：

SELECT* FROM t WHERE a>2 FOR UPDATE;

其锁住的不是5这单个值，而是对（2, +∞)这个范围加了 X锁。因此任何对于这个范围的插入都是不被允许的，从而避免 Phantom Problem。
InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ,在该隔离级别下,其采用Next-Key Locking的方式来加锁。而在事务隔离级别READ COMMITTED下,其仅采用Record Lock。

事物隔离性	锁算法	锁范围
读未提交（read-uncommitted）	无	–
不可重复读（read-committed）	记录锁 Record Lock	行锁
可重复读（repeatable-read）	Next-Key Lock	锁范围
串行化（serializable）	gap lock	锁范围

对于serializable这种隔离级别，完全串行化的操作，是在每个select读的数据行上，加了共享锁，相当于select *** lock in share mode,在每个变更的数据行上加上了排它锁。

repeatable-read和serializable通过主键操作数据的时候，next-key lock降级为 record lock

脏数据和脏页的区别

脏数据和脏页是完全不同的两种概念：

脏数据是指事务对缓冲池中行记录的修改，并且还没有提交。是在不同事务下，当前事务可以读取到另外事务未提交的数据，简单来说就是可以读到脏数据。
脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据不一致。脏页的读取是非常正常的，脏页是数据库实例内存和磁盘异步造成的，这并不影响数据的一致性，脏页最终会被刷新到磁盘中。

不可重复读和脏读的区别

脏读读取到的是未提交的数据
不可重复读读到的是已提交的数据，但违反了数据库事务的一致性

死锁

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阂值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在 InnoDB 存储引擎中，参数 innodb_lock_wait_timeout 用来设置超时的时间。
除了超时机制，当前数据库还都普遍采用 wait_for_graph（等待图）的方式来进行死锁检测，它是一种主动的死锁检测方式。
wait_for_graph要求数据库保存以下两种信息：

锁的信息链表
事物等待链表

通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。在 wait_for_graph中，事务为图中的节点。而在图中，事务 Tl 指向 T2 边的定义为：

事务 Tl 等待事务 T2 所占用的资源
事务 Tl 最终等待 T2 所占用的资源，也就是事务之间在等待相同的资源，而事务 Tl 发生在事务 T2 的后面

下面来看一个例子，当前事务和锁的状态如下图所示。

MySQL怎么隐藏密码_隔离级别_02

在 Transaction Wait Lists 中可以看到共有 4 个事务 t1、 t2 、 t3 、 t4 ，故在 wait for graph 中应有 4 个节点。而事务 t2 对 row1 占用 X 锁，事务 t1 对 row2 占用 S 锁。事务 t1 需要等待事务 t2 中 row1 的资源，因此在 wait_for_graph 中有条边从节点 t1 指向节点 t2 。事务 t2 需要等待事务 t1 、 t4 所占用的 row2 对象，故而存在节点 t2 到节点 t1 、 t4 的边。同样，存在节点 t3 到节点 t1、t2、t4 的边，因此最终的 wait_for_graph 如下图所示：

MySQL怎么隐藏密码_锁算法_03