我要投搞

标签云

收藏小站

爱尚经典语录、名言、句子、散文、日志、唯美图片

当前位置:小鱼玄机解释报 > 启动事务 >

年薪为500000的DBA必须了解MySQL锁和事务

归档日期:07-14       文本归类:启动事务      文章编辑:爱尚语录

  锁是数据库系统的一个关键特性,与文件系统不同。数据库系统使用锁来支持对共享资源的并发访问,从而提供数据完整性和一致性。例如:操纵缓冲池中的LRU列表以删除,添加和移动LUR列表中的元素。

  对于任何类型的数据库,您都需要具有相应的锁定机制,因此MySQL也不例外。

  MySQL数据库由于其自己的架构而具有各种数据存储引擎。每个存储引擎都设计有不同的应用场景。针对特定应用场景的要求,每个存储引擎的锁定机制针对其面临的特定场景进行了优化,因此每个存储引擎的锁定机制也大不相同。

  MySQL公共存储引擎(MyISAM,InnoDB)使用两种类型(级别)的锁定机制:表级锁定,行级锁定。

  表级锁定是MySQL存储引擎中最大的粒度锁定机制。这种锁定机制的最大特点是实现逻辑非常简单,系统的负面影响最小。因此获得锁定和释放锁定的速度非常快。由于表级锁定将一次锁定整个表,因此避免困扰我们的死锁问题是非常好的。

  当然,锁定粒度的最大负面影响是锁定资源争用的概率也是最高的,从而导致显着的折扣。

  表级锁定的主要用途是一些非事务性存储引擎,如MyISAM,MEMORY,CSV。

  行级锁定的最大特点是粒度锁定对象的数量非常小,目前是主要数据库管理软件实现的最小数据库粒度。由于锁定粒度很小,锁定资源争用的可能性也很小,从而为应用程序提供了最大可能的并发处理能力,并提高了一些需要高并发应用程序的应用程序的整体性能。

  虽然它在并发处理能力方面具有很大的优势,但行级锁定带来了许多弊端。由于锁定资源的粒度很小,因此每次获取锁定并释放锁定时,还有更多工作要做,并且消耗自然更大。此外,行级锁定也最容易出现死锁。

  其他插入操作需要等待最后一个update语句完成,然后执行insert操作,这将生成一个表锁。

  如果事务t1最近获得了行r的共享锁,那么另一个事务t2可以得到行r的共享锁,因为读取不会改变行r的数据,请将此案例称为Lock Compatible。但是,如果有其他事务想要获取行r的独占锁,那么必须等待事务t1,t2释放行r的共享锁 - 这称为锁不兼容(confilict)。

  此外,InnoDB存储引擎支持多粒度锁定,which允许行级和表级锁的事务共存。为了支持不同粒度的锁定,InnoDB存储引擎支持一种名为Intention Lock的附加锁定方法。

  意图锁将锁定的对象划分为多个级别,而意图锁意味着事务想要以更精细的粒度锁定。

  所以底层对象(行记录)被锁定,即最细粒度的对象被锁定。首先,您需要锁定粗粒度对象。

  如果需要对页面上的记录r执行X锁定,则需要锁定数据库A,表格,页面上的意图锁定,最后是记录r上的X锁定。如果这些部分中的任何一个导致等待,则操作需要等待粗粒度锁的完成。

  例如,在将X锁添加到记录r之前,near表在表1上有一个S表锁,然后表上已存在S锁1.之后,事务需要将IX添加到记录r。由于不兼容,事务需要等待表锁操作的完成。

  InnoDB存储引擎支持意图锁定设计更简洁,其意图锁定是表级锁定,旨在揭示事务中的下一行将是该类型请求锁定。它支持两种类型的意图锁:

  意图锁定由数据引擎本身。用户无法手动操作意图锁。在向数据行添加共享/独占锁之前,InooDB将首先获取数据表中数据行的相应意图锁。

  因为InnoDB存储引擎支持行级锁,所以意图锁实际上不会阻止除全表扫描之外的任何请求。

  用户有时需要显式锁定数据库读取操作以确保数据逻辑的一致性。这要求数据库支持锁定语句,即使是对select的只读操作也是如此。 InnoDB存储引擎支持select语句的两个一致锁定读取:

  select ... for update将X锁定添加到读取行记录,而其他事务无法向锁定行添加任何锁定。

  选择...锁定共享模式将S锁定添加到读取行记录,其他事务可以锁定到锁定的S锁定,但是如果添加X锁定,将是一场小组赛。

  另外,选择...进行更新,选择...在共享模式下锁定必须在事务中,当提交事务时,锁定被释放因此,在使用上诉的双选select语句时,请务必添加BEGIN,START TRANSACTION或SET AUTOCOMMIT = 0。

  在默认隔离级别下,一致性读取是指多版本控制中InnoDB的事务。当第一次读取发生时,生成图像。在第一个读取时间点之前,可以读取其他事务提交的修改,并且在第一个读取时间点之后,无法读取其他事务提交的修改或未提交的修改。

  唯一的例外是可以读取在第一个阅读时间点之前未由此事务提交的修改数据。 p class =ql-align-justify>

  在读取提交数据隔离级别下,一致读取的每个读取操作都将拥有自己的映像。一致读取不会强加任何锁定,因此不会阻止修改其他事务。

  例如,最经典的mysqldump --single-transaction备份是将当前事务隔离级别更改为可重复读取并打开一致事务的快照。它是一致的非锁定读取。

  自我增长在数据库中很常见属性也是许多DBA或开发人员首选的主键方法。在InnoDB存储引擎的内存结构中,每个表都有一个自增长计数器,具有自增长值。

  插入操作将被赋予自我-growth列基于此自增长计数器加一。此实现称为AUTO-INC锁定。这种自锁是一种特殊的表锁机制。为了提高插入的性能,在事务完成后锁定不会被释放,但是已完成。从增长值插入sql语句后立即释放。

  AUTO-INC锁定在一定程度上提高了并发插入的效率,但仍有一些性能问题。

  InnoDB提供了一个自我-growth实现轻量级互斥,大大提高了自我增长值的表现。提供参数innodb_autoinc_lock_mode来控制自增长锁使用的算法。默认值为1.他允许您在可预测的自增长值和最大并发插入之间进行权衡。

  MySQL 5.1.22之前的自增长实现,AUTO-INC通过表锁定方式

  对于简单插入,此值将使用互斥量作为内存中间的计数器执行累积操作。传统的AUTO-INC锁定方法用于批量插入。在此配置中,如果不考虑回滚,则自增长列的增长是连续的。应该注意的是,如果使用AUTO-INC锁定方法生成自增值,并且需要简单插入操作,则还需要等待AUTO-INC锁定的释放。

  对于所有类似插件的自我增长是由相互排斥产生的,而不是AUTO-INC锁定方法这是性能最高的方法。但它会带来一些问题:由于并发插入的存在,每次插入时,基于语句库复制的自增长值不是连续的。

  因此,在这种情况下,在任何情况下都需要行基复制,以确保最大的并发性能和主从数据的一致性。

  记录锁定将始终锁定索引记录。如果InnoDB存储引擎表在创建时未设置为任何索引,那么这就是InnoDB存储引擎使用隐式主键锁定的位置。

  行级锁是应用于索引行数据的锁,例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE语句是t处的索引。 c1 = 10在该行上添加一个锁,以防止其他事务在相应的索引行上插入/更新/删除操作。

  行锁始终锁定索引记录。即使表没有设置任何索引,InnoDB也会创建一个隐藏的聚簇索引,然后在此索引中。添加行锁。例如:

  当InnoDB表没有任何索引时,行 - 级别锁定强加于隐式创建。在聚簇索引上,因此当SQL不接受任何索引时,将在每个聚簇索引之后添加X锁。这类似于表锁,但原则上它应该与表锁完全不同。示例:

  当我们检索具有范围条件而不是相等的数据并请求共享或排他锁时,InnoDB锁定符合条件的现有数据记录的索引条目;对于条件范围内的键值,但不存在的记录,称为gap(GAP),也会锁定这个间隙。

  区间锁是应用于索引记录之间的间隔的锁,锁定一系列记录,但不包括记录本身,例如SELECT c1 FROM t WHERE c1 BETWEEN 10和20 FOR UPDATE语句,虽然当前表可能没有c1字段的值= 15,但它仍然阻止插入= 15数据,因为间隔锁定已经计算了索引中的间隔数据查询范围。同样被锁定,间隔锁的使用仅在部分事务隔离级别(可重复读取级别)有效。

  区间锁仅阻止来自其他事务的插入,即只有插入操作生成GAP锁,而更新操作不参数GAP锁。示例:

  #打开三个会话窗口,两个窗口模拟两个事务,另一个窗口查看两个事务的一些间隔锁定信息

  Gap Lock is In为了防止多个事务将记录插入同一范围,这可能导致幻像读取问题。

  所谓的Next-Key Locks是记录锁和空格锁的组合,也就是说,除了锁定记录本身外,还有必要锁定索引之间的差距。

  InnoDB在扫描表的索引时以这种形式实现行级锁定:遇到匹配的索引记录,其上有相应的S锁或X锁。

  因此,行级锁实际上是一个索引记录锁。如果事务在索引上的记录r上具有S或X锁,则另一个事务不能立即在r记录索引顺序之前在间隙上插入新记录。

  假设有一个包含值的索引:10,11,13和20.可以添加一个Next-Key锁(左开和右关)以下间隔。

  在最后一个间隔中,Next-Key锁将索引中的最大值锁定为正无穷大。 p>

  默认情况下,InnoDB启用RR事务隔离级别。在这种情况下,InnoDB在找到并扫描索引时使用Next-Key锁定,该索引旨在解决幻像读取出现。

  当查询索引包含唯一(主键索引和唯一索引)属性时,InnoDB存储引擎优化Next-Key锁定,将其降级为Record Lock,它只锁定索引本身,而不是范围。

  插入意图锁定是插入数据行之前设置的间隙锁定。当插入多个事务时,这种锁用于将数据插入到相同的索引间隙中。 ctions不会将数据插入到间隙的相同位置,然后它们不必相互等待。

  客户端A创建一个包含10,11,13,20个四个索引记录的keme2表,然后在所有索引记录上设置一个互斥锁这个互斥锁在20记录之前包含间隙锁定。

  锁定机制可用于实现事务隔离要求,以便事务可以同时工作。锁增加了并发性,但存在潜在的问题。幸运的是,由于需要事务隔离,锁只会带来三个问题。如果可以防止这三种情况发生,则不会出现并发异常。

  脏页:指的是一个已在缓冲池中修改但尚未刷新到磁盘的页面,即数据库实例内存中的页面和磁盘页面中的页面数据不一致,当然,在刷新到磁盘之前,日志已写入重做日志文件。

  读取脏页是很正常的。脏页是由异步数据库实例内存和磁盘引起的,这不会影响数据的一致性(或者两者最终会达到一致性,即将脏页刷新到磁盘时)。并且因为脏页刷新是异步的,所以它不会影响数据库的可用性,因此它可以带来性能改进。

  脏数据:指未提交的数据。如果你读脏了数据,即一个事务可以在另一个事务中读取未提交的数据,这显然是违规的。隔离数据库。

  脏读:指的是在不同的事务中,当前事务可以读取未被另一个事务提交的数据。简而言之,它可以读取脏数据。

  无法识别是在事务中读取相同的数据集。当事务尚未结束时,另一个事务访问相同的数据集并执行一些DML操作。因此,在第一事务中的两个读取数据之间,由于第二事务的修改,第一事务读取的数据可能不同。当在事务中读取两次的数据不同时,会发生这种情况。这种情况称为不可重复读取。

  非可重复读取和脏读取之间的区别在于脏读取读取未提交的数据,并且不重复读取确实提交的数据。

  虽然数据库可以防止更新问题,但生产应用程序中缺少更新还有另一个逻辑含义。导致问题的问题不是因为数据库本身的问题。实际上,在所有多用户计算机系统环境中都可能发生此问题。例如,以下情况:

  例如,如果用户帐户中有10,000元人民币,他会使用两个网上银行客户端分别转账,第一个转账9000元。由于网络和数据之间的关系,您需要等待。

  但此时,用户还经营另一家网上银行客户并转账1元。如果最后两次操作成功,则用户帐户余额为9999元。首次转账9000元尚未更新,但转账的其他账户将收到9000元,这导致资金更多而账户没有。

  但银行也非常聪明,个人银行业务必然会出现usb密钥,这种情况不会发生。是的,通过usb密钥登录可以解决这个问题。但是在数据库级别解决此问题更为重要,并避免可能发生丢失更新的任何情况。

  由于不同锁之间的兼容性,在某个时刻,一个事务中的锁需要等待另一个事务锁。它释放它占用的资源,这是阻塞的。事实上,阻止并不是一件坏事,以确保事务可以同时运行。

  默认情况下,InnoDB存储引擎不会回滚由超时引起的错误异常。事实上,在大多数情况下,InnoDB存储引擎不会回滚异常。

  1解决死锁问题的最简单方法是不要等待,任何等待都将转换为回滚,并重新启动事务。

  这不是死锁问题。在在线环境中,它可能导致并发性能下降,甚至不能执行单个事务。这种锁造成的问题比死锁问题严重得多,而且这种锁造成的问题比死锁问题严重得多,因为很难找到并浪费资源。

  2解决死锁问题的最简单方法是超时,即当两个事务等待时,等待超过某个阈值时是,其中一个事务被回滚,另一个等待事务可以继续。使用innodb_lock_wait_timeout设置超时。

  超时机制很简单,它只在超时后通过超时处理事务,或者它选择根据FIFO的顺序回滚对象。但是,如果超时公司具有相对较大的权重,例如更新大量行的事务操作(例如使用无限循环执行某些事务的程序),则会占用大量的撤销日志,这是FIFO,不适合。因为回滚此事务的时间可能超过另一个事务所花费的时间。

  如果程序是串行的,则不会发生死锁。仅在并发的情况下存在死锁,并且数据库本身是同时运行的程序,因此可能发生死锁。

  在事务数据库中,死锁是一个经典问题,但只要发生频率不高,死锁问题就不必太担心了。死锁应该很少发生,如果频繁发生,则系统不可用。

  如果要访问多个表数据或访问同一个表的不同行集,请确保每个访问的顺序相同。例如,可以将多个语句封装在存储过程中,并且调用相同存储过程的方法可以减少死锁的发生。

  尽可能少地使用锁。例如,如果您可以假设幻像读取,请使用select语句而不是select ... for update语句。

  事务的主要目的是:事务将数据库从一致状态转换为另一个一致状态。在数据库中提交的工作是确保已保存所有更改或未保存所有修改。

  事务可以包含一个非常简单的SQL语句或一组复杂的SQL。事务是一个程序执行单元,用于访问和更新数据库中的各种数据项。事务中的操作是否已修改。这是交易的目的。

  表示整个数据库事务是一个不可分割的工作单元。仅当事务中的所有数据库操作都成功执行时,才认为整个事务是成功的。事务中的任何SQL语句都无法执行,并且还必须撤消已成功执行的SQL语句。应该将数据库状态返回到执行事务之前的状态。

  一致性定义基本上可以理解为事务对合规性具有数据完整性约束。这些约束可以包括主键约束,外键约束或一些用户定义的约束。在执行事务之前和之后是合法数据状态,并且不会违反任何数据完整性,这意味着一致。交易是一致性的单位。如果某个事务中的操作失败,系统可以自动撤消事务 - 返回事务初始化的状态。

  隔离有其他名称,例如并发控制,可序列化事务的隔离要求每个读写事务的对象可以与其他事务的操作分开,也就是说,事务在提交之前对其他事务是不可见的。

  提交事务后,结果是永久性的(写入磁盘),即使出现停机等故障,数据库也可以恢复数据。应该指出的是,结果的有效性只能从交易本身的角度来保证。例如,在提交事务之后,所有更改都是永久性的,即使由于崩溃需要恢复数据库,也可以保证恢复后提交的数据不会丢失。

  但是如果数据库本身出现故障,但是一些外部原因,如RAID卡损坏,自然灾害等,导致数据库出现问题,那么所有提交的数据可能会丢失。

  因此,耐用性保证了交易系统的高可靠性,而不是高可用性。对于高可用性实现,事务本身不能保证并且需要一些系统一起工作。

  事务的隔离由锁实现。通过数据库的重做日志和撤消日志完成原子性,一致性和持久性,重做日志成为重做日志,以确保事务的原子性和持久性。撤消日志用于确保事务的一致性。

  重做和撤消的作用都可以看作是恢复操作,重做恢复提交事务修改的页面操作,撤消回滚r记录到特定版本。因此,两个记录的内容是不同的。重做通常是物理日志,记录页面的物理修改操作,undo是逻辑日志,根据每行记录进行记录。

  InnoDB是事务的存储引擎,它通过Commit机制中的Force Log实现事务持久性。也就是说,当事务提交时,必须首先写入事务的所有日志。输入重做日志文件以获得持久性,并完成事务的提交操作。

  此处的日志指的是重做日志。在InnoDB存储引擎中,它由两部分组成:重做日志和撤消日志。

  重做日志用于保证事务的持久性,undo log用于帮助事务回滚和多版本控制(mvcc)功能,重做日志是基本上按顺序写入,不需要读取数据库中的重做日志文件。撤消日志需要随机读写。

  为了确保将每个日志写入重做日志文件,在每个重做日志缓冲区写入重做日志文件后加载InnoDB存储引擎。需要调用一次fsync操作。

  首先将重做日志缓冲区写入文件系统缓冲区,因为重做日志文件在没有O_DIRECT选项的情况下打开。要确保将重做日志写入磁盘,必须执行fsync操作。由于fsync的效率取决于磁盘的性能,因此磁盘的性能决定了事务提交的性能,这是数据库的性能。

  InnoDB存储引擎允许用户手动非持久性情况来提高数据库性能。也就是说,当事务提交时,日志不会写入重做日志文件,而是在执行fsync操作之前等待一段时间。

  0 :表示未提交事务写入重做日志操作仅在主线程中执行,并且重做日志的fsync操作在主线:表示每个事务在提交时都写入重做日志。

  2:表示在事务提交时将重做日志写入重做日志文件,但仅在没有fsync操作的情况下写入文件系统的高速缓存。在此设置中,当MySQL数据库关闭时(即数据库服务意外停止)并且操作系统不会崩溃时,它不会导致丢失事务。当操作系统关闭时,重新启动数据库后,某些未从文件系统缓存刷新到重做日志文件的事务将丢失。

  重做日志记录事务的行为,并通过pages重做操作,但事务有时需要回滚,然后你需要撤消。因此,在修改数据库时,InnoDB存储引擎不仅会生成重做,还会产生一定量的撤销。这样,如果用户 - 由于原因,执行的事务或语句失败,或者如果用户请求使用回滚语句回滚,则可以使用撤消信息将数据回滚到修改之前的数据。

  undo是一个逻辑日志,所以只需将数据库逻辑恢复到原始状态。所有修改在逻辑上都可以celed,但回滚后数据结构和页面本身可能会大不相同。这是因为在多用户并发系统中,可能存在数十,数百甚至数千个并发事务。数据库的主要任务是协调并发访问数据记录。例如,事务正在修改当前页面中的一些记录,还有其他事务可以修改同一页面中的其他几个记录。因此,您无法将页面回滚到事务的开头,因为它会影响其他事务正在执行的工作。

  初始化数据库后,无法再更改innodb_undo_tablespaces;默认值为0,表示undo表空间不是独立设置的,默认记录是ibdata;否则,在undo目录下创建这么多的undo文件。例如,如果将该值设置为4,则将创建名为undo001~undund004的undo表空间文件。每个文件的默认大小为10M。修改此值将导致Innodb无法完成初始化,数据库将无法启动,但可以修改其他两个参数。

  purge用于完成delete和updAte操作。由于InnoDB存储引擎支持MVCC,因此只要事务提交,就无法再处理记录。此时,其他事务可能正在引用此行,因此InnoDB存储引擎需要在记录之前保留版本。是否可以删除记录是通过清除来判断的。如果任何其他事务未引用行记录,则可以执行真正的删除操作。可以看出,清除操作是清理先前的删除和更新操作,并且上述操作是最终完成的。实际操作是删除操作,它清除先前记录的版本。

  如果事务是非只读事务,则每次事务提交时都需要fsync操作,确保重做日志几乎写入磁盘。当数据库关闭时,可以通过重做日志恢复。虽然固态硬盘的出现提高了磁盘的性能,但磁盘的rsync性能有限。为了提高磁盘fsync的效率,数据库提供了组提交功能,也就是说,可以刷新一个fsync以确保将多个事务日志写入文件。

  修改内存中事务对应的信息并将日志写入重做日志buffer。封顶fsync将确保日志从磁盘写入磁盘重做日志缓冲区。步骤1相对于步骤2是一个较慢的过程,因为存储引擎需要处理磁盘。但是,当存在执行此过程的事务时,其他事务可以执行步骤1的操作。正在提交的事务完成提交操作。当再次执行步骤2时,可以通过fsync将多个事务的重做日志刷新到磁盘一次,从而大大降低磁盘上的压力,从而提高数据库的整体性能。对于更频繁编写或更新的操作,组提交的效果尤其明显。

  MySQL数据库中有两个附加参数:handler_commit和handler_rollback,用于事务的统计操作。它可用于计算InnoDB存储引擎的显式和隐式事务提交操作。

  脏读:也??称为读出无效数据,在数据库访问中,事务T1将修改一个值,然后事务T2读取该值,之后,T1由于某种原因撤销对值的修改,这导致T2读取的数据无效。

  不可重复的读取:表示在数据库访问中,事务范围内的两个相同查询返回不同的数据。

  幻像读取:指交易未独立执行时发生的现象。例如,第一个事务修改表中的数据。例如,此修改涉及表中的所有数据行。同时,第二个事务也修改了该表中的数据。此修改是在表中插入新的数据行。

  许多人容易混淆阅读和幻像读。确实,两者有些相似。但是,不可重复读取的重点是更新和删除,而幻像读取的重点是插入。

  注意:mysql默认为自动提交,而load1如果没有执行一次,则会自动提交load2。

  显然,load3方法要快得多,因为每次提交都必须写一个重做日志,而存储过程load1和load2实际上写了20,000次。创建了日志文件,但对于存储过程load3,它实际上只写了一次。

  提供24小时不间断的最新资讯,及搜索、邮件等网络服务。内容包括全球热点事件、突发新闻、时事评论、热播影视剧、体育赛事、行业动态、生活服务信息

  本站所有文章由会员即时发表,本站对所有文章的真实性、完整性及立场等,不负任何法律责任。所有文章内容只代表发文者个人意见,并非本网站之立场,用户不应信赖内容,并应自行判断内容之真实性。如有不适当或对于文章出处有疑虑,请联络我们告知,我们将在最短时间内进行撤除。本站有权删除任何留言及拒绝任何人士发文,同时亦有不删除文章的权利。

本文链接:http://singtamil.com/qidongshiwu/516.html