MySQL锁兼容性 [英] MySQL lock compatibility

问题描述

根据此处的官方文档:

锁兼容性矩阵:

    X              IX          S         IS
X   Conflict    Conflict    Conflict    Conflict
IX  Conflict    Compatible  Conflict    Compatible
S   Conflict    Conflict    Compatible  Compatible
IS  Conflict    Compatible  Compatible  Compatible

文档还说:

因此，除了全表请求之外，意图锁不会阻止任何其他操作 (例如，LOCK TABLES ... WRITE). IX和IS的主要目的 锁定是为了表明某人正在锁定一行或将要锁定一行 在表中.

Thus, intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

如果意图锁仅阻止完整表请求，那么如何在上述锁兼容性矩阵中解释与S锁的IX冲突?据我了解，锁兼容性矩阵中的S和X都是记录锁，对吗?

If the intention locks only block full table requests, then how to explain the IX conflicts with S lock in the above lock compatibility matrix? To my understanding, the S and X in the lock compatibility matrix are both record locks, it's that right?

推荐答案

据我了解，锁兼容性矩阵中的S和X都是记录锁，对吗?

To my understanding, the S and X in the lock compatibility matrix are both record locks, it's that right?

那是正确的.假设您可以直接比较表锁和记录锁，这是不正确的，文档可能无法完全弄清这一点，并且可以通过以下锁类型兼容性矩阵方便地总结这些规则"这一部分可能会引起误解，因为它不能涵盖所有内容(即与S/X-表锁和 record 锁有关的任何冲突信息).

That is correct. Incorrect is the assumption that you can directly compare table and record locks, which the documentation probably does not make completely clear, and the part "These rules can be conveniently summarized by means of the following lock type compatibility matrix" might be a bit misleading, as it does not cover everything (namely any conflict information about S/X-table locks with record locks).

从技术上讲，该矩阵定义了检查某个对象(例如，对象)上的锁时的结果.每当MySQL尝试向某物添加锁时.如果尝试在表上获取S锁，则该锁将与该表上的IX锁冲突.

Technically, that matrix defines the result when checking locks on some object, e.g. whenever MySQL tries to add a lock to something. If you try to get an S lock on a table, it would conflict with an IX lock on that table.

如果一条记录可能具有意图锁定，那么那里也会发生冲突.仅仅因为一个可锁定的对象不使用意图锁就不会改变(通用)兼容性矩阵.

If a record could have an intention lock, it would conflict there too. Just because a lockable object doesn't use intention locks doesn't change the (general) compatibility matrix.

从技术上讲，锁的内部数据类型与记录和表相同，只是从未为记录设置意图锁.实际上，记录锁永远不会与表锁进行比较(因为这是两个不同的对象)，并且记录锁干扰表锁的唯一原因是锁定协议(该协议要求同时对表和记录进行锁定)锁定记录).

Technically, the internal datatype for locks is the same for records and tables, the intention locks are just never set for records. A record lock will actually never be compared to a table lock (as these are two different objects), and the only reason a record lock interferes with a table lock is the locking protocol (which requires a lock on both the table and the record to lock a record).

因此，要锁定记录，通常需要不同表锁定.兼容性矩阵相同，但是表的值通常可以与记录的值不同.

So to lock a record, you will typically require a different table lock. The compatibity matrix is the same, but the value for the table can and usually will differ from the value of the record.

所以

因此，意图锁除了完整表请求(例如LOCK TABLES ... WRITE)外不会阻塞任何东西.

Thus, intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE).

是正确的，因为只有全表请求才需要与现有意图锁冲突的锁，并且记录不使用意图锁.但是要重复一遍:您仍然必须比较两个不同的锁.记录上的S锁不能与表上的锁冲突，因为这两个对象永远不会进行比较.

is correct because only full table requests require a lock that conflicts with an existing intention lock, and records do not use intention locks. But to repeat it: you still have to compare tow different locks. An S-lock on a record cannot conflict with the lock on a table, as these two object will never be compared.

该手册正在对表和记录进行锁定.它实际上将IS和IX定义为:

The manual is mixing table and record locks a bit. It actually defines IS and IX as:

• 意图共享(IS):事务T打算在表t中的各个行上设置S锁.
• 意向排他(IX):事务T打算在那些行上设置X锁.

• Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.
• Intention exclusive (IX): Transaction T intends to set X locks on those rows.

因此，如果需要，矩阵中的IS和IX可以某种程度上解释为行的属性(从技术上讲，它们不是)，而您将它们读取为表上的锁(因为它只能是为表设置，但这是一个不同的锁).但是该矩阵仍然仅描述了比较记录 的情况(该手册可能不够清楚)，并且它不包含任何带有S的兼容性信息.或X 表锁定.

so if you want, IS and IX in the matrix can somewhat be interpreted as properties of rows (which they technically are not), while you read them as the lock on the table (as it can only be set for a table, but which is a different lock). But the matrix still only describes the situation to compare records (which the manual probably does not make clear enough), and it does not include any compatibility information with an S or X table lock.

因此，请总结一下:您不需要在上述锁兼容性矩阵中解释与S锁的IX冲突"，因为它根本无法解决这种情况.

So to summarize: you do not need "to explain the IX conflicts with S lock in the above lock compatibility matrix", as it simply does not cover that situation.

这篇关于MySQL锁兼容性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！