Section 11.2. Concurrency and Locking Scenarios

11.2. Concurrency and Locking Scenarios

Like many other database systems, DB2 provides support for concurrent data access. While the database is being accessed and manipulated by multiple users, it is important to keep data integrity by using database locking. Before getting into detailed discussions of DB2 locking, you should first understand various concurrent data access scenarios you may encounter and how each DB2 isolation level can prevent these scenarios from occurring or allow these scenarios to occur.

11.2.1. Lost Updates

Assume there is an airline reservation system with multiple agents answering calls for seat reservations. A table called reservations is defined to store flight numbers, seat assignments, and passenger names. Each seat on every flight is represented by a single row of data. Figure 11.2 shows the reservations table.

Figure 11.2. Sample content of the reservations table

Suppose customer Harry wants to reserve a seat on Flight 512 and calls the reservation center. An agent, Sam, receives the call and finds the only seat available, 4A, for Harry. While Harry is confirming the itinerary with his wife, Sam maintains the searched result on screen. At this time, agent Mary helps another customer, Billy, to fulfill his request. Mary also finds 4A, the last seat on Flight 512.

Eventually, Harry decides to confirm the seat and Sam assigns seat 4A to Harry. However, Mary does not see Harry's update and she assigns the same seat to Billy. Both seat assignments are successful, but guess who gets the seat? If the list of seats is retrieved again, you will see that the second update overwrites the first one (see Figure 11.3). Hence, Sam loses the seat assignment and Harry will not be able to get on the plane as he expects.

Figure 11.3. Sample content of the updated reservations table

This example demonstrates that if there is no mechanism in place to maintain the accuracy of data, it is possible to lose updates without knowing it or until the customers find out for themselves.

By default, DB2 acquires a lock on every record that the agent is updating. This default behavior cannot be changed. With this type of lock, no other agent can update the same row of data. If this reservation system is implemented in DB2, this scenario of lost update will never occur. When Sam is updating the record, all other read operations (except uncommitted read) and write operations to the same row of data will wait until Sam's transaction is completed. Once Sam has committed the change, Mary will see the new seat assignment in her next data retrieval, so Billy will not be assigned to the seat.

Two terms are introduced here that warrant some discussion. A transaction (also known as a unit of work) is a sequence of SQL statements that the database manager treats as a whole. Any reading from or writing to the database is performed in a transaction. At the end of a transaction, the application can COMMIT or ROLLBACK the changes. Once you issue a COMMIT operation, changes are written to the database. A ROLLBACK operation causes the changes within the transaction to be rolled back. Transactions are discussed in more detail in Chapter 13, Developing Backup and Recovery Solutions.

11.2.2. Uncommitted Reads

Using the same flight reservation example, assume Sam is updating a row to assign a seat. Since DB2 locks the row by default, no other agent can read or update the same record. Meanwhile, the manager wants to run a report to determine how many passengers are scheduled to fly on Flight 512. Because of the default locking behavior, the manager's request has to wait until Sam's update is completed. However, if the manager's application is implemented to read uncommitted data, the manager can run the report without waiting for Sam to complete his transaction. This type of read is called an uncommitted read or a dirty read. However, changes Sam makes are not guaranteed to be written to the database. Therefore, if he decides to roll back the changes, the manager will get a different result when running the report again.

Whether an uncommitted read is allowed or avoided is based on the application design. As you can imagine, performance of applications with the ability to read uncommitted data is better because there is no need to acquire and wait for locks. However, you must understand that the data retrieved is not committed data, which means that the data may not be the same the next time you query it.

11.2.3. Nonrepeatable Reads

Suppose Harry asks Sam to find an aisle seat on Flight 512. Sam issues a query and retrieves a list of available seats on the flight. Figure 11.4 shows such a list where (the NULL value) in the Passenger Name column means the seat is not assigned.

Figure 11.4. Available seats on Flight 512

In this aircraft model, only C and D seats are aisle seats. There is only one aisle seat available on this flight, seat 8C. Before Sam reserves seat 8C for Harry, no lock is acquired on this row highlighted in Figure 11.4. At this time, Mary has assigned and committed the same aisle seat to another customer, Billy. When Sam is ready and tries to assign Seat 8C to Harry, the update fails because the seat is no longer available. If the same query is issued, Figure 11.5 shows that seat 8C is no longer available.

Figure 11.5. Updated available seats on Flight 512

This is an example of a nonrepeatable read scenario for which a different result set is returned with the same query within the same transaction. To avoid this situation, all the rows returned from the result set, shown in Figure 11.4, should be locked. This way, no other user can update the rows currently being read until the transaction is completed. However, concurrency will be decreased because of the extra locks being held.

11.2.4. Phantom Reads

A phantom read is very similar to a nonrepeatable read: while rows currently read are not updatable or removable by another user, new rows can be inserted into the tables that fall under the query criteria.

The flight reservation application is designed in a way that all rows in a result set are locked. Due to the demand of this particular flight, the airline decides to upgrade the aircraft to a larger one so that more passengers can be served. Since more seats are added to the flight, the same query used before to obtain available seat will now return extra rows. If the aircraft upgrade is made in the middle of another query transaction, the next execution of the same query will result in extra phantom rows. Depending on the situation, reading phantom rows may work with the application. To avoid this behavior, extra locking is required.