database - 为什么我们需要意向锁？

Question

在数据库中，我们不希望在修改该表中的行时删除该表。根据我的理解，表上的读锁+在表中写入行时的行上的写锁应该足够了(基于删除表时需要写锁)，为什么在这种情况下我们需要意向锁？似乎很多数据库都使用意向锁，这让我很困惑。我认为 pthread_rwlock 应该足够了。

Answer 1

我读了here它们只为性能而存在。想象一下，您想要删除一个表，但您必须检查每一行是否已锁定 - 这将非常耗时，并且您必须锁定您检查的每一行。

引用博客文章:

From a technical perspective the Intent Locks are not really needed by SQL Server. They have to do with performance optimization. Let’s have a look on that in more detail. With an Intent Lock SQL Server just indicates at a higher level within the Lock Hierarchy that you have acquired a Lock somewhere else. A Intent Shared Lock tells SQL Server that there is a Shared Lock somewhere else. A Intent Update or Intent Exclusive Lock does the same, but this time SQL Server knows that there is an Update Lock or an Exclusive Lock somewhere. It is just an indication, nothing more.

But how does that indication help SQL Server with performance optimization? Imagine you want to acquire an Exclusive Lock at the table level. In that case, SQL Server has to know if there is an incompatible lock (like a Shared or Update Lock) somewhere else on a record. Without Intent Locks SQL Server would have to check every record to see if an incompatible lock has been granted.

But with an Intent Shared Lock on the table level, SQL Server knows immediately that a Shared Lock has been granted somewhere else, and therefore an Exclusive Lock can’t be granted at the table level. That’s the whole reason why Intent Locks exist in SQL Server: to allow efficient checking if an incompatible lock exists somewhere within the Lock Hierarchy. Quite easy, isn’t it?