Objectives

In this chapter, we will discuss the control of data modification using transactions and examine some of the possible concurrency problems and the strategies that can be used to avoid them. After reading this chapter you should gain an understanding of the following:

• Concurrency consideration

• Isolation levels

• Locking

For the remainder of this chapter, we will use the sample database as an example to examine some concurrency issues that need to be addresses by any application accessing the database. The following scenario illustrates the associated concurrency and locking issues:

One of the duties of project managers is to assign projects to employees. Two project managers, PM1 and PM2, realize that CHRISTINE, with employee number 000010, must be working on only one project, the ADMIN SERVICES project (project code AD3100). Therefore, both managers undertake the task of finding another employee to assign to a second project, MA2100. All the record updates are made using an in-house application.

For reference, consider the sample EMPLOYEE table in Figure 6.1, and the PROJECT table Figure 6.2, as they exist in the SAMPLE database.

Figure 6.1. Partial contents of the EMPLOYEE Table in the Sample database.

						EMPNO  FIRSTNME
------ ------------
000010 CHRISTINE
000020 MICHAEL
000340 JASON

3 record(s) selected.

Figure 6.2. Contents of the PROJECT table that exists in the Sample database.

						PROJNO PROJNAME                RESPEMP
------ ------------------------ -------
AD3100 ADMIN SERVICES           000010
MA2100 WELD LINE AUTOMATION     000010to be re-assigned
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
OP2013 DB/DC SUPPORT            000340

20 record(s) selected.