With this month’s topic being “locking, concurrency and waits”, it may not be a bad time to review some of the locking basics. It might help your junior colleagues or your developers to get a better understanding. Hopefully after this article you won’t have to explain any more that “yes a reader needs to commit frequently as well”.
Locking within any database is about finding the best middle ground between “concurrency” (letting as many users work together as possible) and “data integrity” (guaranteeing that what we read is correct within our setting). Db2 will therefore try to take
- the weakest lock allowed
- the shortest lock allowed
- on the smallest resource allowed
This requires a little explanation. The key part of those three bullet points is the word allowed. Different rules apply for different occasions. The easiest to start with is the difference between reading data and writing/changing data.
When my application is reading data, Db2 doesn’t mind that another application is reading the same data. Db2 knows that a reader will not change data, so multiple readers reading the same data will not cause a problem. Db2 takes a Shared-Lock ( also known as an “ S” lock) on the data. As the word says it this lock can be shared with other applications, that also just read data. This “S” lock is taken to protect the data from other processes, to show those processes that someone is currently working with/reading the data. So when we run a SELECT or a FETCH from a read cursor, Db2 protects that data in the background with an “S” lock.
When my application is changing/writing data, Db2 has to ensure that no other application touches that data until my application commits or does a rollback. Db2 has to be more restrictive here because as long as the change has not been committed that data is in transition. Allowing someone else access could allow access to incorrect data. Db2 needs to ensure no one else works with the data, so Db2 takes an Exclusive-Lock (also known as an “X” lock) on the data. So when we run an INSERT, UPDATE or DELETE, Db2 protects that data in the background with an “X” lock.
Since the world is not only made up of black and white, Db2 also has an “Update-Lock” (also known as a “U” lock). Db2 uses this type of lock when your application has indicated that although it is currently reading data, it has the intention to update the data later. This is done through the “for update of” clause. Rather than reading the data with and “S” lock and then once the update occurs promote the lock to an “X” lock, Db2 recognizes that this is a special situation. During the read part of your application, Db2 will place a “U” lock. This is to ensure that no other application comes in that also has an intention to update the same data and both applications would be waiting on each other. During the read phase, other pure readers (“S” locks) are allowed to continue, but anything else will be stopped and will have to wait until the “U” lock is released by Db2. So when we run a FETCH from a ‘For Update’ cursor, Db2 protects that data in the background with an “U” lock.
All locks are equally important to Db2, so the winning lock will be the lock that locked the data first. If that is an “S” lock, the application wanting to do an update (“X” lock) will have to wait until the reader releases the data. Vice versa if the updater application holds the lock (“X” lock) first, then the reader application (“S” lock) will have to wait until the update applications releases the locks. The below images gives an overview of which locks (“S”, “U” or “X”) are compatible with each other or not, depending on who holds the lock and who requests the lock.
Besides the strength of a lock (“S”, “U” or “X”) the concurrency is also heavily impacted by the size of the lock. In Db2 we have different lock sizes row, page, table, table space and any. The default lock size is any, this means that Db2 can decide itself what the best size is. Typically this resorts to page level locking.
It’s clear that if Db2 locks one row or an entire table for one application, that there will most likely be a bigger impact for other applications when the table lock is taken. Indeed the smaller the lock size, the better concurrency is attained. So why does Db2 have bigger lock size ? Cost. One lock costs about the same regardless of its size. So taking a 1000 row locks will be significantly more expensive that one table lock.
In my opinion row level locking is underutilized in Db2, as many customers chose page level locking as their company default. The reason it’s “cheaper”, but all that depends on how the data is used. If the application is reading data randomly, the chance that it needs two rows on the same one page, might be small. So reading 10 records randomly, with page level locking could require 10 page locks which would cost about the same as 10 row level locks. The page level locks however would keep much more data from being accessible to other applications. In a data sharing environment, row level locking can cause an overhead as more row locks will cause more P-locks (physical locks in a data sharing environment. This article won’t get into that). But those extra row locks would only be true if one page lock would eliminate the need for multiple row locks. If data is handled 100% random than you would have just as many row locks as page locks. Does that mean switch to ROW level locking everywhere ? Not at all, but when faced with concurrency issues, you shouldn’t be afraid to test row level locking. As always, the only way to be sure is test, test, test.
So far we’ve talked quite a bit on when a lock is taken and what type that lock will be. However locks also get released. Db2 takes a lock when it needs to access data and will release a lock as soon as it can. The lock release behavior is controlled by the isolation level. In general we can say that all locks get release by commit or rollback. In Db2 this behavior is always true for exclusive locks. The moment an application changes data, Db2 will hold that “X-“lock until commit.
In case of a select or fetch the same logic applies, a commit and a rollback will release the “S” or “U” locks. However depending on the isolation level this behavior can change. Most common isolation level is Cursor Stability (CS), this is the Db2 implementation of the read committed isolation level SQL standard. As it offers the certainty of only reading committed data but at the same time releasing the data as soon as Db2 no longer needs it.
In case of a “S” or “U” lock that means that Db2, with isolation level CS, will release the row or page
- As soon as it moves to the next row in case of a cursor
- End of work which means
- For a cursor: close cursor
- For a singleton select : the next statement in the program
There is much more to be said about locking, how system parameters come into play, how lock escalation works, how to work with dirty reads and what that means, how lock avoidance works, common locking error and much more. Should you feel like brushing up that knowledge and understand how it all comes together. I welcome you to have a look at Steve Thomas’s presentation he delivered at the IDUG conference in 2017. You can find that at the IDUG YouTube channel : https://www.youtube.com/watch?v=6ldQ7NK9kXo
Kurt Struyf
IBM Gold consultant IDUG content committee chair