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

Figure 11.3. Sample content of the updated reservations table

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

Figure 11.5. Updated available seats on Flight 512

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.