Concurrency Considerations
Database resource control requires the use of data modification rules. There are concurrency anomalies that we will consider when referring to the previous scenario. They include:
Lost update
Uncommitted read
Nonrepeatable read
Phantom read
Lost Update Problem
A lost update problem occurs when the same data is changed multiple times when it shouldn't be. The last successful change to the data is kept, and the first change is overwritten. In the previous scenario, PM1 has Jason in his office, discussing project MA2100. He brings up the screen with the project assignments ready to make the update. Meanwhile, PM2 is also discussing the same project with Michael and assigns the project to Michael. PM1's discussion took longer, but finally PM1 submits the update and assigns project MA2100 to Jason without knowing that the same project was assigned to Michael. Michael and Jason are now working on the same project, but the records show that the project is assigned to Jason—the last update committed—as shown in Figure 6.3.
Figure 6.3. Project MA2100 is assigned to Jason.
PROJNO PROJNAME RESPEMP ------ ------------------------ ------- AD3100 ADMIN SERVICES 000010 PL2100 WELD LINE PLANNING 000020 IF1000 QUERY SERVICES 000030 IF2000 USER EDUCATION 000030 OP1000 OPERATION SUPPORT 000050 OP2000 GEN SYSTEMS SERVICES 000050 MA2110 W L PROGRAMMING 000060 AD3110 GENERAL ADMIN SYSTEMS 000070 OP1010 OPERATION 000090 OP2010 SYSTEMS SUPPORT 000100 MA2112 W L ROBOT DESIGN 000150 MA2113 W L PROD CONT PROGS 000160 MA2111 W L PROGRAM DESIGN 000220 AD3111 PAYROLL PROGRAMMING 000230 AD3112 PERSONNEL PROGRAMMING 000250 AD3113 ACCOUNT PROGRAMMING 000270 OP2011 SCP SYSTEMS SUPPORT 000320 OP2012 APPLICATIONS SUPPORT 000330 MA2100 WELD LINE AUTOMATION 000340<-new assignment OP2013 DB/DC SUPPORT 000340 20 record(s) selected. |
Lost Update Solution
To avoid lost updates, control of each data record must be maintained by DB2. The control mechanism involves obtaining update control of all the matching records or possible matching records for the query. This is known as repeatable read. The only explicit SQL control mechanism provided by DB2 is the LOCK TABLE statement. If the application explicitly locked the table for update (known as EXCLUSIVE mode), PM2 would not be allowed to update or even read any of the committed data in the table. If the application explicitly locked the table not for update (SHARE mode), PM2 would be allowed to read but not update the data as long as the share lock remained. Consider each option:
If an exclusive lock was obtained, then PM2 would have never been allowed read access for the record with project number MA2100, because the first project manager (PM1) would have locked it. The result of this may be a phantom read phenomenon, discussed later in this section. The drawback to this solution is that although the project was not assigned, it was only visible to PM1.
If a share lock is obtained by both project managers, who both want to assign the project to their respective employees, then neither project manager would have been able to update the row until the share lock was released because an update requires an exclusive lock on the row. Unfortunately, if the project manager releases the share lock, the other project manager might use that moment to assign the project!
Uncommitted Read
In the scenario under discussion, PM1 locks the row containing project MA2100 and makes a temporary update to assign the project to Jason. Until PM1's application has committed or rolled back this update, no other project manager can assign this project to another employee. However, PM2 has permission to read uncommitted changes, so he can see project MA2100 assigned to Jason, and therefore can tell Michael that the project has been assigned, as shown in Figure 6.3.
However, if PM1 changes his mind about assigning the project to Jason and rolls back the temporary update, then project MA2100 will remain unassigned.
To avoid this kind of problem, only committed data should be read by an application.
Nonrepeatable Read
A nonrepeatable read scenario can obtain a different result set within the same unit of work or transaction. Uncommitted read applications do not guarantee a repeatable read. Consider the previous example. Suppose that PM1 decides not to assign the project to Jason and commits the unit of work. If PM2 resubmits the query from the same unit of work to view the project assignments, PM2 will now see that the project has not been assigned to anybody. Thus, PM2 gets different results sets from the same query that was submitted in the same transaction more than once.
To avoid this type of nonrepeatable read scenario, all of the retrieved data needs to be locked. If you want to guarantee that none of the selected data is modified, locking these rows is sufficient. This is known as read stability in DB2. If you want to guarantee that the rows you have selected in this result will never change within the transaction, use repeatable read. This might require additional locking.
Phantom Read Problem
The phantom read phenomenon occurs if an application executes the same query twice; the second time the query is issued, additional rows are returned. Let's say that PM1 looks for all the unassigned projects using his application. He gets a result set of one project which is not assigned to an employee. PM2 adds a new project to the PROJECTS table. PM1 needs to resubmit the query to get the list of unassigned projects. PM1 now gets a different result set, consisting of two projects instead of one. For many applications, this is an acceptable scenario.
Phantom Read Solution
Depending on the situation, a phantom read may be desirable. If you want to avoid this behavior, the application has to lock all of the possible qualifying rows. If the qualifying rows are all the rows in the table, then a table-level lock may be obtained instead of multiple row-level lock. This ensures that no other application can update, delete, or insert a row that would affect the result table.