数据库同步工具
sqlserver,Mysql数据同步软件

Mysql回顾一–锁和事务

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

事务隔离级别是为了解决由并发,脏读取,不可重复读取(用于更新/删除操作)和幻像读取(用于插入操作)引起的问题。为了确保事务中的读取一致性,需要对其进行控制。

我们都知道事务的隔离级别可以解决脏读,不可重复读和幻像读的问题。但是Mysql使用什么机制来实现读取一致性?

这涉及MVCC(多版本并发控制)。 MVCC:多版本并发控制,控制事物的隔离级别。仅RC,RR隔离级别将使用此技术。

MVCC(Mutil-Version并发控制)是多版本并发控制。 MVCC是一种并发控制方法,通常在数据库管理系统中,以实现对数据库的并发访问。

在Mysql的InnoDB引擎中,它是指在READ COMMITTD和REPEATABLE READ这两个隔离级别下的事务访问版本链中的记录以进行SELECT操作的过程。

这允许其他事务修改此记录。无论如何,每次修改都会记录在版本链中。 SELECT可以转到版本链以获取记录,从而实现读写和读写的并发执行,从而提高了系统性能。 ?

让我们仔细看看它是如何完成的。

版本链

首先让我们了解版本链的概念。在InnoDB引擎表中,其聚集索引记录中有两个必要的隐藏列:

每次修改聚集索引记录时,都使用trx_id的ID来存储事务ID。

每当roll_pointer修改哪个聚集索引记录时,它都会将旧版本写入撤消日志。 roll_pointer是一个指针,它指向此聚集索引记录的先前版本的位置,通过该指针可以获取先前版本的记录信息。 (请注意,插入操作的撤消日志没有此属性,因为它没有旧版本)

例如,现在有一条记录的修改后的语句,其事务ID为60

这时,撤消日志中有一个版本链

ReadView

说到版本链,让我们再次看一下ReadView。 提交的阅读和可重复的阅读之间的区别在于生成ReadView的不同策略

ReadView的主要部分是一个列表,用于存储系统中当前活动的读写事务,即尚未提交的事务。使用此列表确定记录的特定版本对于当前事务是否可见。假设当前列表中的交易ID为[80,100]。

如果要访问的记录版本的事务ID为50,小于当前列表中的最小id80,则表示该事务之前已提交,因此当前活动的事务可以访问该事务。如果您要访问的记录版本的事务ID为70,并且发现该事务在列表ID的最大值和最小值之间,则判断它是否在列表中。无法访问。如果不存在,则事务已提交,因此可以访问版本。如果要访问的记录版本的事务ID为110,大于事务列表的最大ID 100,则表示此版本是在生成ReadView之后发生的,因此无法访问。这些记录都可以在版本链中找到。首先查找最新记录。如果最新记录的事务ID不满足条件且不可见,则转到先前版本,将当前事务ID与该版本的事务ID比较。不可访问,依此类推,直到返回或结束可见版本。

例如,在提交的隔离级别下:

例如,此时有一个事务ID为100的事务,其名称已修改,以使其名称等于Xiao Ming 2,但该事务尚未提交。那么此时的版本链是

这时,另一个事务启动了一条select语句以查询ID为1的记录,此时生成的ReadView列表仅为[100]。然后转到版本链以找到它。首先,必须找到最接近的一个,并且发现trx_id的记录为100,即在列表中找到名称为Xiaoming 2的记录,因此无法对其进行访问。

这时,继续查找名称为Xiaoming 1的下一条记录,并发现trx_id为60,它小于列表中的最小id,因此可以对其进行访问。直接访问的结果是小明1。

此时,我们提交了交易ID为100的交易,并创建了交易ID为110的新记录,并将ID修改为1,但未提交交易

版本链现在为

这时,select事务再次执行查询,并查询ID为1的记录。

关键点在这里

如果您已提交读隔离级别,则此时您将重新读取ReadView,那么活动事务列表中的值将更改为[110]。

根据上述内容,您转到版本链,并通过trx_id比较找到合适的结果是小明2。

如果您是可重复的读取隔离级别,则您的ReadView仍然是在第一次选择期间生成的ReadView,也就是说,列表的值仍为[100]。 因此,选择的结果为小明1。因此,第二个选择的结果与第一个选择的结果相同,因此称为可重复读取!

也就是说,在提交的读取隔离级别下的事务将在每个查询的开头生成一个独立的ReadView,而可重复的读取隔离级别将在第一次读取期间生成一个ReadView,并且随后的所有读取都将被重复。使用以前的ReadView。

另一种确保一致读取的技术是锁定。解决资源竞争。

1)就性能而言,它分为乐观锁定(使用版本比较)和悲观锁定

2)从对数据库的操作类型来看,它分为读取锁和写入锁(均为悲观锁)

读取锁定(共享锁定):对于相同的数据,可以同时执行多个读取操作,而不会互相影响。

手动添加共享锁:从t_xx中选择*?锁定共享模式;

写锁(排他锁):在当前写操作尚未完成之前,它将阻止其他写锁和读锁。

自动添加排他锁:插入/更新/删除

手动添加排他锁:从t_xx中选择*以进行更新;

3)从数据库操作的粒度来看,它分为表锁,行锁和页锁。

表锁定:低开销,快速锁定,无死锁,粒度大,锁定冲突的可能性最高,并发性最低。

行级锁:较大的开销,缓慢的锁,发生死锁,锁的粒度最小,锁发生冲突的可能性最低,并发性也最高。

?页面锁定:开销和锁定时间介于表锁定和行锁定之间,并且将发生死锁。

4)为了使表锁和行锁可以共存并实现多粒度锁控制,InnoDB还具有两个用户无法使用的内部意图锁。这两个意图锁都是表锁。

?故意共享锁:(IS)事务必须在将共享锁添加到数据行之前获取表的IS锁。

?意向排他锁:(IX)事务必须在向数据行添加排他锁之前获取表的IX锁。

功能:可以将其理解为标志,以更快地确定表是否具有锁,以便其他事务无需进行全表扫描就可以知道表是否具有锁。可以提高锁紧效率。

死锁:多个进程等待对方的锁被释放。

锁冲突:一个进程等待另一进程释放所需的锁。

InnoDB行锁定是通过将索引项锁定在索引上来实现的。如果没有索引,InnoDB将通过隐藏的聚集索引(行)来锁定记录。 InnoDB分为三种情况:

记录锁定:束缚索引项

间隙锁定:对于索引项之间的”间隙轭”,第一个记录的”间隙”或最后一条记录之后的”间隙”

下一键锁定:前两种类型的组合锁定记录和前一个间隙。

InnoDB的锁定机制意味着,如果未通过索引条件检索数据,则表中的所有记录都将被锁定,这实际上与表锁定相同。

1)在没有索引条件的情况下进行查询时,InnoDB将锁定所有记录

2)由于Mysql的行锁是添加到索引而不是记录的锁,因此它用于访问不同行中的记录,但是如果使用相同的索引键(即,索引字段是可重复的),会有锁冲突。需要特别注意!

3)当表中有多个索引时,不管主键如何,不同的事务可以使用不同的索引来锁定不同的行(也可以使用不同的索引来锁定同一行)索引,唯一索引或普通索引,InnoDB将使用行锁来锁定数据。

4)即使条件中使用了索引字段,Mysql也会通过判断不同执行计划的成本来确定是否使用索引来检索数据。如果Mysql认为全表扫描更高效,例如某些非常小的表,它将不会使用索引,在这种情况下,表将被锁定。

因此,在分析锁冲突时,请检查执行计划以确认是否实际使用了索引。

尽管未显示上述情况,但可以通过两个窗口的自检得出结论。

当我们使用范围条件而不是相等条件来检索数据,并请求共享或互斥锁时,InnoDB将锁定满足条件的现有数据记录的索引项。对于条件范围内的键值但没有记录,称为“间隙”(GAP),InnoDB也会锁定此“间隙”,这种锁定机制就是所谓的Next-key锁定。

就距离而言,如果emp表中只有101条记录,则它们的empid是1、2、3…………101,以下sql:

是范围条件搜索。 InnoDB不仅锁定满足empid = 101的条件的记录,而且还锁定empid大于101的”间隙”(尽管该记录不存在)。

如果其他会话插入的数据大于101,则将发生锁冲突。

InnoDB使用Next键锁定。一方面,这是为了解决魔术读取(特定参考:魔术读取和间隙锁)以满足隔离级别要求的问题。对于上面的示例,如果不使用间隙锁,则如果其他事务插入的Empid大于101的数据,则该事务将再次执行上述语句,并且将发生魔术读取;另一个方面是满足恢复和复制的需求,这将在后面讨论。

?显然,此范围内的锁定将导致严重的锁定等待。在实际生产中,尤其是在具有更多并发插入的应用程序中,我们应尽力优化业务逻辑,并尝试使用等效条件访问更新后的数据。避免使用范围条件。

还应注意,InnoDB除了通过范围条件进行锁定外,还使用Next-key锁定。如果使用相同的条件来锁定不存在的记录,则InnoDB也将使用Next-key锁定。

请参阅” Mysql深入”

未经允许不得转载:数据库同步软件|Mysql数据同步软件|sqlserver数据库同步工具|异构同步 » Mysql回顾一–锁和事务

分享到:更多 ()

syncnavigator 8.6.2 企业版

联系我们联系我们