CODEWITHSUNDEEP
databaseconcurrency
zhihuifan
zhihuifan

Reputation: 1113

Why do we need intent lock?

In a database, we would not like the table to be dropped during we are modifying a row in this table. Per my understanding, a read lock on table + a write lock on row when write a row in table should be enough(based on that a write lock is needed when drop the table), why do we need a intent lock in this case? seems many databases using intent lock which confused me very much. I think pthread_rwlock should be enough.

Upvotes: 10

Views: 3623

Answers (3)

zhihuifan
zhihuifan

Reputation: 1113

One of the conclusions today is "intent lock can lock a parent node AND all its children nodes in a read only mode in a cheaper/safer way".

Take an example for making a table read only case, how to lock it in S-X mode?

  1. We lock the table in S mode, then user still can modify the rows with S(table) + W(row) way. to avoid that, we need to lock every row in a S mode to make sure rows will not be updated. The cost is so huge, and it has a bug that user can insert new rows as well. -- cost too much and not safe.

  2. We lock the table in X mode, How other can read the rows (S on table + S on row), no way, since mode_X on table blocked MODE_S on table. That's not read only.

The right solution with the intent lock is:

Lock the table in MODE_S. that's all!

any intention to modify the rows needs to take a MODE_IX lock on the table, but it is blocked by MODE_S. the solution is cheap/efficient and safe!

Upvotes: 0

Adam
Adam

Reputation: 29069

I read here that they only exists for performance. Imagine you want to drop a table but you would have to check for every row if its locked or not - that would be time consuming, and you would have to lock every row that you checked.

Heres a citation from the blog post:

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?

Upvotes: 13

Tsyvarev
Tsyvarev

Reputation: 65996

read lock on table + a write lock on row

This would break meaning of the read lock on the table.

Assume concurrent SELECT operation, which expects unmodified table during execution. This operation will take read lock on the table ... and it will succeed in your implementation. This is bad, as table is actually modified during row modification.

Instead, follow locks combination is used for modify row in the table:

IX(Intent eXclusive) on table + X(eXclusive, similar to "write lock") on row

This combination is compatible (that is, can be executed concurrently) with modification of another row, but it is incompatible with

S(Share, similar to "read lock") on table

used by SELECT.

Locks compatibility table can be found, e.g., on wiki.

Upvotes: 0

Related Questions