Optimize locking is the generic name given to two mechanisms that reduce the number of locks acquired by the database engine when performing DML operations. These are
- Locking based on Transaction ID
- Locking after qualification
What type of operation benefits the most from this feature?
The DML operation that will benefit the most from this feature is the UPDATE statement. This is because previously when performing DML you needed to wait until the old transaction completed before you can start a new one. A quick reminder is listed below of how locking work During the transaction
Without Read Committed Snapshot
- Reader doesn’t block reader
- Writer blocks reader
- Reader blocks write
- Writer Blocks Writer
With Read Committed Snapshot
- Reader Doesn’t block reader
- Reader Doesn’t Block Writer
- Writer Doesn’t block reader
- Writer Block Writer
With Optimized Locking
- Reader Doesn’t block reader
- Reader Doesn’t Block Writer
- Writer Doesn’t block reader
- Writer doesn’t block writer **
The ** is mainly related to the mechanisms I mentioned earlier.
So, let’s dive in a bit.
Databases need to respect ACID properties. This means that transactions need to be isolated from each other so that changes made by one do not affect others while in transit (being still uncommitted). The way this was handled is like being a parent of 3 kids. When all kids shout at once, you acquire a lock on one, do what it asks, then move on to the next. This way everybody gets attention. Unfortunately, this isn’t scalable when there are 100 kids. It takes too long.
Enter Read Committed Snapshot isolation level. Now you group the kids into Reader (those that only have a question they want answered) and Writers (those that want you to actually get up and do something for them). With this done; all readers are redirected to one parent and the writers to another parent, unfortunately the parent dealing with the writers are still limited by the fact that they can only acquire lock X and it remains acquired for the duration while they are performing the task.
Enter Optimized Locking, now both parents come up with a mechanism to handle the requests from the kids. For the most part nothing has changed for the readers but the writer now doesn’t acquire a lock for the duration of transaction per child. For example, the parent is tying the shoelace, combing the hair, packing the bag, making sure they have breakfast and also checking if they finished homework before boarding the school bus. Initially they would acquire a lock on the child (table) and one key lock per task(row) and until all the tasks were completed the kid was not allowed to move. This meant the kid wasn’t allowed to move until it finished eating breakfast even if the shoelaces are tied and the feet have nothing to do with the mouth. Technically the kid isn’t released until all tasks are done. With optimized locking we can allow locks to be released as soon as the task is done. So, the kid can start moving around as soon as the shoe lace is tied since it’s possible to move and eat at the same time. This is the transaction ID based locking in an over simplified way.
The next mechanism we have is Lock after Qualification. In this case the kid reaches the writer and the write parent will quickly check with the Reader parent if this is something they actually have to do. Basically, checking or qualifying with the Read Snapshot before actually acquiring a lock. A good example will be the kids saying they need to be dropped over at a friend’s place for a sleepover. Before actually performing the task the writer quickly checks with the reader if this has been already discussed and what was the last thing agreed on, and once the request has been qualified the writer performs the update.
This might be a bit difficult to understand so let’s dive in a bit more. Consider the case where 2 users want to update different rows within the same table. Normally it would be addressed on a first come first server basis. The first session acquires UPD lock on the table and until it finishes scanning the table to find rows that qualify for its update the second session cannot do anything to that table.
With Lock after Qualification, we no longer need a UPD on the table to identify the rows, we can simply get the latest version of the row from version store (because we have enabled Optimistic concurrency). This way both sessions can identify rows that qualify for their updates and can be executed in parallel provided the same row doesn’t qualify for both sessions. This is great to avoid deadlocks.
But wait a minute. What if the same row qualifies for both sessions?
In this case the first session to complete will have committed its transaction and the state of the row will have changed. As a result, the second session will return a different result since its not waiting for the latest state of the row anymore.
If the order in which changes are being made is important then you need to specifically mentioned the same in the ISOLATION LEVEL for the transaction , use SERIALIZABLE or REPEATABLE READ.
What to watch out for?
It is enabled by default in Azure SQL databases and therefore you can use it in your production environment too. Remember that enabling this feature bypasses ROW and PAGE level locks that are the usual mechanism for acquiring locks in SQL Server.
How to use it?
In order to use this feature, you need to fist have ADR (accelerated database recovery) enabled. You can read more about it here. To get the full benefit you should also enabled Read committed Snapshot isolation level. For more info on this feature click here.
OPTIMIZED_LOCKING DISABLED
OPTIMIZED_LOCKING ENABLED
These features greatly reduce the amount of memory consumed as part of storing lock information for highly transaction OLTP systems and reduce the likelihood of deadlocks as well. There is a slight change in the behavior however since previously we could rely on the default to respect the order of update while now its controlled by the user.
Please Consider Subscribing
