Outline

• Why is concurrency control needed?
• Quiz
• Isolation Levels
• Locking

Introduction

• Last week, we finished up talking about different programming interfaces to DB2.
• Then we began talking about DB2 security.
• We talked about how primary and secondary IDs are established during the connection process.
• We talked about how authorization special registers are associated with the defaults that are assumed about the naming of database objects and with the ownership of those objects.
• We discussed the various administrative authorities.
• Then went into how granting and revoking of privileges work in different settings, in particular, we talked about the dynamic and static SQL settings.
• Finally, we discussed security in a distributed setting, and how trusted contexts can be used in this arena.
• Today, we are going to look at concurrency control in DB2.

Concurrency Control

• Many users can be communicating with DB2 at the same time.
• The techniques used to make sure this happens seamlessly are known as concurrency control.
• Before we discuss, concurrency control in detail, let's look at a few bad situations that can happen when multiple users interact with the same database at the same time...

Phantom and Non-Repeatable Read Anomalies

• Phantom Row Anomaly
  • Suppose that you ask your ticket agency to list all the performance of the Metropolitan Opera for May, and you get a list of four performances.
  • You ask for tickets to all of them, but when you receive the tickets, you discover that another performance has been added and you have to pay for five tickets.
  • After your read of the list, but before the purchase, somebody had added a row to the list of Operas.
  • This is a phantom row anomaly.
• Non-repeatable Read
  • Suppose that you ask your ticket agency for the price of tickets to a concert and you are told that the price is $35.
  • You decide to buy some tickets, but only after you made your purchase you discovered that the price has gone up to $50.
  • Somebody had changed the value of the price between your read and the purchase.
  • This is called the non-repeatable read anomaly, because you have accessed the same piece of data twice and found different values.

Dirty Read and Lost Update Anomalies

• Dirty Read Anomaly
  • Suppose that you scan a list of planned concert and see that Willie Nelson will be performing in your town next summer.
  • When you try to buy a ticket, you find out that the list was only tentative and that the concert was never really scheduled.
  • The change to the list was then rolled back.
  • This is called the dirty read anomaly, because you were allowed to read information before it was committed.
• Lost Update Anomaly
  • Suppose that you ask your ticket agency if any tickets are available for a Bruce Springsteen concert, and the agency replies that one ticket is left.
  • A short time later I asked my agency for the same ticket and get the same reply.
  • Your agency prints a ticket and updated the available tickets from one to zero; then my agency does exactly the same thing (sets available tickets to zero) without seeing your agency's updates.
  • This is called the lost update anomaly, because two users have updated the same pieces of information and one of the updates was lost.

Quiz

Which of the following statements is true?

1. Type 3 JDBC drivers are written in pure Java.
2. Authorization in DB2 is the process of establishing which primary and secondary IDs a user has.
3. Roles and secondary IDs serve the same purpose in DB2.

Isolation Levels

• Ideally, you would like to avoid all these anomalies.
• The cost for doing this is to limit concurrency.
• DB2 allows application designers to control the trade-off between isolation and concurrency, by specifying an isolation level for each transaction.
• Following types of isolation levels are supported:
  • Repeatable Read (RR) -- this is the highest level of isolation, it prevents all of the anomalies we've mentioned, but limits concurrency.
  • Read Stability (RS) -- this prevents non-repeatable read, but not phantom row anomalies. It has less of an impact on concurrency than RR.
  • Cursor Stability (CS) -- this prevents dirty read and lost updates, but not phantom read and non-repeatable read
  • Uncommitted Read (UR) -- this is the lowest level of isolation. It does not prevent anomalies, but on the plus side does not impact concurrency. It might be used when perfect accuracy is not needed.

Anomalies Seen at Various Isolation Levels

The PREP and BIND commands for application programs have a parameter that controls the isolation level for all transactions executed by the program that is being bound. The parameter consists of the word ISOLATION followed by the abbreviation of the desired level: RR, RS, CS, or UR. The default isolation level, both for application programs and for interactive sessions, is Cursor Stability (CS).

DB2 Locking Basics

• Locks are one mechanism that DB2 uses to control concurrent access to Data
• DB2 uses the IRLM (Internal Resource Lock Manager) to keep track of locks
• IRLM runs in its own address space. It provides the locking mechanism for DB2. IRLM is not a DB2 component.
• Both DB2 and IMS use IRLM to keep track of its locks.

Characteristics of Transaction Locking

• Size (or scope) -- this says how much data is locked (row, table, etc)
• Mode (or state) -- this is the type of lock such as shared (read) or exclusive (write) locks.
• Duration -- this refers to how long after the lock is obtained is the lock held.

Possible Lock Sizes

When a table space is created or altered, you can specify the LOCKSIZE option. The choices are:

• TABLESPACE -- When TABLESPACE is specified for a partitioned TS or Universal TS creation, the partition is actually the lock size.
• TABLE -- TABLE size can only be specified for segmented TS.
• PAGE
• ROW
• ANY -- When ANY is specified, DB2 decides at prepare/bind time and always chooses page size except in rare cases when it chooses table.

Thoughts about Different Lock Sizes

• When the lock size is TABLESPACE or TABLE, concurrency is severely limited.
• We'll mainly focus on PAGE or ROW lock sizes.
• The resources required to acquire, maintain, and release a row lock are just about the same as the resources required for a page lock.
• Therefore, the number of rows per page must be factored into the row-versus-page locking decision. The more rows per page, the more resources row locking will consume.
• As a general rule of thumb, LOCKSIZE PAGE is the most practical locking strategy for most applications.
• If you're experiencing severe contention problems on a table space that is currently using LOCKSIZE PAGE, consider changing to LOCKSIZE ROW and gauging the impact on performance, resource consumption, and concurrent data access.

Lock Mode

• Share (S) -- This is used to read the data without an intent to update it.
• eXclusive (X) -- This lock is used to read and write data. Only someone in the uncommitted read isolation level could read if this lock is held.
• Update (U) -- This lock is used when you initially want to read, but at some later point might need to write. Only someone in the uncommitted read isolation level or with a share lock could read if this lock is held. In a "cursor select / update where current of (WCO)" statements, the control is returned to application between the "cursor select" and the "update WCO" statement. A long- held "S" lock in the read phase has a less fair chance to be promoted to the "X" lock. An "Update (U)" lock is introduced to disallow concurrent updaters at the read phase.

Lock Compatibility Table

Intent Locks

• Consider a table with a million rows.
• Acquiring and releasing 1 million row locks is costly in terms of CPU used.
• For strong semantics, where the million row locks are held until commit, there is much space consumed to track the locks. So for these transactions that are heavy hitters, it makes more sense to lock the whole table and not be burdened with the cost of taking small (row or page) locks.
• It makes sense therefore to have different kind of locks at table versus row granularity.
• For instance, one might be only reading rows. For each row, we would have to get a read lock.
• At the table level though, it would be useful to register own intent to get only share locks for this table. As a step So we might get an IS (intent share lock) on the whole table.
• Intent locks allow us a mechanism to easily do locking at a gross table and refined row level in a more concurrent manner.

Types of Intent Locks.

• Intent share (IS) -- The lock owner declares the intent to only get share level row or pages locks. He/she will only read the data. The lock owner acquires s-locks on pages or rows accessed. Concurrent processes may both read and change data.
• Intent exclusive (IX) -- The lock owner and concurrent processes can read and change data. The lock owner acquires x locks on pages or rows changed. The lock owner can also read pages or rows with update intent and acquires u locks on these pages or rows. When a page or row is changed then the u lock is promoted to an x lock.
• Share with intent exclusive (SIX) SIX is actually a hybrid state. From a read perspective it is a gross lock and from a write perspective it is an intent lock. The lock owner can read and change data. Concurrent processes can read data, but not change it. Only when the lock owner changes data, is the x lock acquired on a page or row.

The DB2 Locking Hierarchy

• Depending on the tablespace type we have different levels of granularity for locks.
• For segmented tablespaces, we have tablespace, table, page, and row levels.
• For partitioned or universal tablespaces, we have partition, page, and row levels.
• With the new notion of intent, now in a segmented TS, when we acquire a row/page level lock we must first acquire an intent lock on the table space and table.
• For a partitioned or universal tablespace, we must first acquire a lock on the partition.

Compatibility of Lock Mode for Table

Duration of Page and Row Locks

• Lock duration refers to the length of time that a lock is held.
• The BIND parameters ISOLATION level (CS, RR, RS, UR) affect Page and Row Lock duration
• ISOLATION(CS), or Cursor Stability, acquires and releases page locks as pages are read and processed. CS provides the greatest level of concurrency at the expense of potentially different data being returned by the same cursor if it is processed twice during the same unit of work
• ISOLATION(RR), or Repeatable Read, holds page and row locks until a COMMIT point; no other program can modify the data. If data is accessed twice during the unit of work, the same exact data will be returned.
• ISOLATION(RS), or Read Stability, holds page and row locks until a COMMIT point, but other programs can INSERT new data. If data is accessed twice during the unit of work, new rows may be returned, but old rows will not have changed.
• ISOLATION(UR), or Uncommitted Read, is also known as dirty read processing. UR avoids locking altogether, so data can be read that never actually exists in the database.

Repeatable Read (RR)

• Repeatable read is the highest level of isolation. It can prevent all the anomalies mentioned today.
• A RR transaction with an index access acquires a lock on all data that it reads and holds the lock until commit point.
• This prevents other transactions from updating/deleting/inserting any of this data.
• Thus, if a program reads the same piece of data twice in the same transaction with an isolation level of RR, it is guaranteed to see the same value (or absence of a value!).
• If an RR-level transaction reads a lot of data, the concurrency of the database can be severely limited.
• An index access lock prevents others to insert a row, thus protecting from the phantom anomaly. A RR transaction not using index access requires a gross lock on the table level.

Read Stability (RS)

• Read Stability holds page and row locks until a COMMIT point, but other programs can INSERT new data.
• A row or page lock is held only for qualifying rows or pages that are returned to an application at least until the next commit point.
• This isolation level guarantees that if a transaction read the same row twice, it will have the same value, but it does not prevent new rows from appearing during the course of a transaction.
• An RS-level transaction has a smaller impact on concurrency than an RR-level transaction, and it is protected from non-repeatable read anomalies but not from phantom row anomalies.

Cursor Stability (CS)

• Cursor Stability acquires and releases page "s" locks as pages are read and processed.
• CS provides the greatest level of concurrency at the expense of potentially different data being returned by the same cursor if it is processed twice during the same unit of work.
• If the cursor had specified FOR UPDATE OF then DB2 would have taken "u" locks instead of "s" locks. "u" locks are also released as soon as the subsequent "fetch" cause DB2 to move from this page/row into another one.
• CS-level transactions are protected against dirty reads and lost updates, but not against phantoms and non-repeatable reads.
• The CS level of isolation provides less protection and has less impact on concurrency.
• This isolation level is a very common setting for DB2 applications.

Uncommitted Read (UR)

• UR avoids locking for isolation and provides the least amount of protection against the isolation anomalies.
• UR- level transactions have virtually no effect on concurrency.
• Since a UR-level transaction can read data that is in an inconsistent state (for example, it may see the debit to your savings account but not the credit to your checking account), this isolation level is usually used only in statistical surveys or other applications where perfect accuracy is not required.
• The UR user has the least overhead. Any updaters in the system doe not cause a lock wait for the UR user. The UR user does not cause a lock wait for any updaters.
• Please note that this isolation level only applies to the read process. An update, delete, insertprocess will be promoted by DB2 to CS, if it is bound with UR.

Lock escalation

• Lock escalation is the process of releasing a large number of page or row locks held by an application process on a single table or table space, and acquiring a table or table space lock, or a set of partition locks instead.
• While an application is accessing relatively few pages or rows it allows more concurrency by using use page/row locks.
• When it starts to access many pages or rows, it would take lots of CPU to manipulate the locks and would take up storage to hold the locks.
• When the number of page/row locks exceeds the LOCKMAX in the configuration file, DB2 releases the many page/row locks and acquires the gross table space/partition/ table locks instead. This is called lock escalation.
• When escalation occurs for a partitioned table space, only partitions that are locked by the current user are escalated.

Lock Suspensions and Timeouts

• The longer a lock is held, the greater the potential impact to other applications.
• When an application requests a lock that is already held by another process, and the lock cannot be shared, that application is suspended.
• A suspended process temporarily stops running until the lock can be acquired. Lock suspensions can be a significant barrier to acceptable performance and application availability.
• When an application has been suspended for a pre-determined period of time, it will be terminated. When a process is terminated because it exceeds this period of time, it is said to timeout. In other words, a timeout is caused by the unavailability of a given resource.
• The suspended process resumes running when: All processes that hold the conflicting lock release it or or the requesting process times out after a preset interval and the process resumes to deal with an error condition. DB2 issues messages to the console and returns SQLCODE -911 or -913 to the process.

Deadlocks

• A deadlock occurs when: Two (or more) agents hold a lock on different resources and each agent needs the lock on the other's resource.
• When deadlock occurs: One agent is chosen as the victim and rolls back. The victim's lock is released. The other agent can continue.
• Different applications accessing the same data in different orders increases chances of deadlocking. So it makes sense to write similar transaction so that the access data in a similar order.
• CURRENTDATA parameter only applies with the CS isolation level. CURRENTDATA(YES) means that the data upon which the cursor is positioned cannot change while the cursor is positioned on it. CURRENTDATA(NO) does not make the same guarantee. With CURRENTDATA(NO), you have much greater opportunity for avoiding locks.
• You can also reduce the risk of deadlocks by using a lower isolation level and smaller lock sizes.
• Use "FOR UPDATE OF..." on Cursor SELECT acquires a "u", not an "s" lock. When performing "UPDATE WHERE CURRENT OF" later, a "u" lock has a better chance to be promoted to an "x" lock without encountering deadlocks.
• Use "USE AND KEEP EXCLUSIVE LOCK" on cursor select directly acquire an "x" lock, no lock promotion is required when data is to be updated. This also reduces the possibility of deadlock.