Menu
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.
286
Intent locks (I) – this lock is a means used by a transaction to inform another transaction about its intention to acquire a lock. The purpose of such lock is to ensure data modification to be executed properly by preventing another transaction to acquire a lock on the next in hierarchy object.
An intention shared lock ( IS ) indicates that a transaction intends to set a shared lock on individual rows in a table. An intention exclusive lock ( IX ) indicates that a transaction intends to set an exclusive lock on individual rows in a table.
At the table level, there are 5 different types of locks. i.e, Exclusive (X), Shared (S), Intent exclusive (IX), Intent shared (IS), and Shared with intent exclusive (SIX) and these locks have already been discussed above.
An intent lock is the lock the database server (lock manager) places on a higher granularity object when a lower granularity object needs to be locked.
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?
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
