S-lock (share) - the lock owner can read but not change the page or row.There are three row and page lock modes we will use: If the locks are not compatible, B cannot proceed and must wait until A releases its lock. The locks are said to be compatible if the mode of a lock for process A permits the lock acquired by the process B. The important property of each lock is its mode, which DB2 uses to determine if one lock is compatible with another. We will only deal with row and page locks in this article. DB2 allows these sizes - Table space, table, partition, page, row, LOB, and XML. The lock size is the amount of data that is controlled by that lock. Let me do a brief recapitulation of the basic terms so that the content of the article is more understandable.īasically, DB2 uses locks to ensure that no process accesses data that has been changed, but not yet committed, by another process, and to ensure that an application cannot access dirty data (partial or incomplete data). Throughout the article we will use different locks that DB2 acquires for SQL statements and different isolation levels you can use for concurrency control. A brief recapitulation of locks and isolation levels As I got inspired by that Redbook, I will use a very similar use case here, so the credit goes to the authors of the Redbook. (Moreover, you can even find a prior version of that Redbook, which was written for DB2 4 and which I found interesting as well.)Īnyway, what I would like to discuss in this article is a simple scenario, that shows that a singleton SELECT can result in surprising application behavior if not used properly. By the way, even though the Redbook is for DB2 9, most of the information in there is still relevant and I encourage you to read it if you are about to study serialization and concurrency control in more details. ![]() Later, I came across a great Redbook DB2 9 for z/OS: Resource Serialization and Concurrency Control, where I found a simple use case where a singleton SELECT statement could cause interesting issues and surprising results. However, that discussion lit a fire in me and I still kept thinking about possible scenarios where a singleton SELECT could not necessarily be the best choice. Even though I did not know any further details about their environment, it seemed obvious to me that this would always be the best solution for readability and performance, because by using a singleton SELECT you avoid all the overhead of OPEN, FETCH, CLOSE cursor. Singleton SELECT is a select statement that returns a single row at most. Some time ago I had a discussion with a DBA who guided the developers to use a singleton SELECT instead of fetching from a cursor in cases where just a single row would be selected.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |