2021年1月28日星期四

InnoDB中不同SQL语句设置的锁

锁定读(locking read)、更新(UPDATE)或删除(DELETE)通常会在SQL语句处理过程中扫描的每个索引记录上设置记录锁。语句中是否存在排除行的WHERE条件并不重要。InnoDB不记得确切的WHERE条件,而只知道哪个索引范围被扫描。这些锁通常是next-key锁,它还会阻止在记录之前插入"间隙"。然而,间隙锁(gap lock)可以被显式禁用,这会导致不使用next-key锁。

如果在检索中使用了二级索引,并且要设置的索引记录锁是排它的,则InnoDB也会检索相应的聚集索引记录并在它们上设置锁。

执行SQL语句时,如果没有找到可用的索引,MySQL必须扫描整个表来处理该语句,这样的话表的每一行都会被锁定,从而阻塞其他用户对表的所有插入。因此,创建良好的索引非常重要,这样可以避免扫描许多不必要的行。

InnoDB设置特定类型的锁,如下所示:

  • SELECT ... FROM 是一致读,读取数据库快照,除非将事务隔离级别设置为SERIALIZABLE,否则不设置锁。对于SERIALIZABLE级别,检索会在遇到的索引记录上设置共享的next-key锁。但是,对于使用唯一索引来搜索唯一行的语句,只需要一个索引记录锁。

  • 对于 SELECT ... FOR UPDATE 或者 SELECT ... LOCK IN SHARE MODE ,对扫描的行加锁,并对不符合结果集中包含条件的行(例如,如果它们不满足WHERE子句中给出的条件)释放锁。

  • SELECT ... LOCK IN SHARE MODE 在所有遇到的索引记录上设置共享的next-key锁。但是,对于使用唯一索引来搜索唯一行的语句,只需要一个索引记录锁。

  • SELECT ... FOR UPDATE 在搜索遇到的每个记录上设置排它的next-key锁。但是,对于使用唯一索引来搜索唯一行的语句,仅需要索引记录锁定。

  • UPDATE ... WHERE ... 在搜索遇到的每个记录上设置排它的next-key锁。但是,对于使用唯一索引来搜索唯一行的语句,仅需要索引记录锁定。

  • DELETE FROM ... WHERE ... 在搜索遇到的每个记录上设置排它的next-key锁。但是,对于使用唯一索引来搜索唯一行的语句,仅需要索引记录锁定。

  • INSERT 在插入的行上设置排他锁。该锁是索引记录锁,不是next-key锁(即没有间隙锁),并且不会阻止其他会话插入到插入行之前的间隙中。

举个例子,假设有一张表t1,结构如下:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

再假设,有三个会话操作顺序如下:

Session 1:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

首先Session1获得i=1这一行的排它锁,接下来Session2和Session3由于主键重复只能请求获取该行的共享锁,由于行上已经有排它锁,因此Session2和Session3请求的共享锁不能被立即授予。再接着,Session1回滚,行上的排它锁被释放,于是Session2和Session3在该行上都持有共享锁,此时,死锁发生了,由于对方持有的共享锁,任何一方都不能获得该行的排它锁。

下面这组操作也是类似:

Session 1:

START TRANSACTION;DELETE FROM t1 WHERE i = 1;

Session 2:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 1:

COMMIT;

和前面的情况类似,Session1提交以后,Session2和Session3请求该行上的共享锁被立即授予,此时它们再请求获取排它锁时就出现死锁了,因为共享锁被另一个事务持有。

补充: 聚集索引与辅助索引

clustered index (译:聚集索引、聚簇索引)

secondary index (译:二级索引、辅助索引)

每个InnoDB表都有一个特殊的索引,称为聚集索引,用于存储行数据。通常,聚集索引与主键是同义词。为了从查询、插入和其他数据库操作中获得最佳性能,必须了解InnoDB如何使用聚集索引来优化每个表的最常见的查找和DML操作。

Typically, the clustered index is synonymous with the primary key.

通常,"clustered index" 和 "primary key" 是一个意思。

  • 当你在表上定义一个PRIMARY KEY时,InnoDB将它用作聚集索引。为创建的每个表定义一个主键。如果没有逻辑唯一的非空列或列集,请添加一个新的自动递增(auto-increment)列,其值将自动填充。

  • 如果你没有为你的表定义一个PRIMARY KEY,则MySQL会在所有键列都不为NULL的情况下找到第一个唯一索引,并且InnoDB使用它作为聚集索引。

  • 如果表没有主键或合适的唯一索引,InnoDB会在包含行ID值的合成列上内部生成一个名为GEN_CLUST_INDEX的隐藏聚集索引

通过聚集索引访问行非常快,因为索引搜索直接指向包含所有行数据的页。如果表很大,聚集索引体系结构通常节省磁盘I/O操作。

除了聚集索引之外的所有索引都称为二级索引。在InnoDB中,二级索引中的每条记录都包含该行的主键列,以及为二级索引指定的列。InnoDB使用此主键值在聚集索引中搜索行。

如果主键很长,则辅助索引将使用更多空间,因此具有主键较短是比较有利的。

With the exception of spatial indexes, InnoDB indexes are B-tree data structures. Index records are stored in the leaf pages of their B-tree or R-tree data structure. The default size of an index page is 16KB. Supported sizes are 64KB, 32KB, 16KB (default), 8KB, and 4KB.

 

 









原文转载:http://www.shaoqun.com/a/520054.html

跨境电商:https://www.ikjzd.com/

好卖家:https://www.ikjzd.com/w/776

eori:https://www.ikjzd.com/w/499


锁定读(lockingread)、更新(UPDATE)或删除(DELETE)通常会在SQL语句处理过程中扫描的每个索引记录上设置记录锁。语句中是否存在排除行的WHERE条件并不重要。InnoDB不记得确切的WHERE条件,而只知道哪个索引范围被扫描。这些锁通常是next-key锁,它还会阻止在记录之前插入"间隙"。然而,间隙锁(gaplock)可以被显式禁用,这会导致不使用next-key锁。如果
贝贝网:贝贝网
黄劲:黄劲
Prime Day给旺季带来的一点思考:谁赢了?:Prime Day给旺季带来的一点思考:谁赢了?
Azimo:Azimo
Lazada什么产品好做?选品推荐:Lazada什么产品好做?选品推荐

没有评论:

发表评论