CSE 515: Database Transaction Processing Systems,  Fall 2008

[Announcements] [Experiments]   [Hints]   [Home]

Experiment 3: Lost Updates, Deadlocks and Isolation Levels

Description:

In this experiment you are to implement two stored procedures, both of which increase a particular faculty member's salary in the Faculty table by $1. IncreaseSalary1 uses a single UPDATE statement to do the job. IncreaseSalary2 uses a SELECT statement to fetch the salary, increases it by $1, and then uses an UPDATE statement to store the result back in the table.  You are to run four experiments using these two procedures.  In each experiment you will be running 10 terminals concurrently, each of which executes the same script.  The script executes a loop, calling a particular procedure repeatedly until the time allotted for the experiment elapses.

 

1.      In the first experiment all scripts call IncreaseSalary1 and execute at SERIALIZABLE (isolation level 8).

2.      In the second experiment all scripts call IncreaseSalary1 and execute at READ COMMITED (isolation level 2).

3.      In the third experiment all scripts call IncreaseSalary2 and execute at SERIALIZABLE.

4.      In the fourth experiment all scripts call IncreaseSalary2 and execute at READ COMMITTED.

The purpose of the experiment is to investigate the relationship between deadlocks, isolation levels and lost updates.  Deadlocks can occur when concurrent transactions request the same sequence of locks - a situation that arises in IncreaseSalary2 since it accesses a particular row with two SQL statements.  Lost updates can occur when locking is not two-phase - a situation that arises when transactions are run at READ COMMITTED.  Since IncreaseSalary gives a $1 raise, we can calculate the number of lost updates by comparing the number of times IncreaseSalary has been executed with the actual raise given to the faculty member at the end of the experiment.

Steps:

Here are the needed files: ClientJava.zip

 

Repeat the following steps for each of the four experiments, substituting IncreaseSalary1 or IncreaseSalary2 for IncreaseSalary.

1.      Create the stored procedure IncreaseSalary, which raises the salary of a particular faculty member by $1.  The Id of the lucky person can be stored as a constant in the procedure.

2.      Create a schema file containing the table Faculty and the stored procedure IncreaseSalary.

3.      Leave the init_db.txt file empty.

4.      The file prototype.txt, containing only one entry "int IncreaseSalary(Connection con);"  is provided

5.      Script files, terminal0.scp to terminal9.scp, is provided:

>> 

      IncreaseSalary

6.      Create the java program IncreaseSalaryClass.java, which invokes the stored procedure IncreaseSalary1 (or IncreaseSalary2, .) .

7.      Run the experiment with the 10 terminals for 10 seconds.

8.      Modify IncreaseSalary2 by inserting a one second waitfor statement between the SELECT and the UPDATE and repeat the experiments at the two isolation levels.

 

 

Report:

Prepare a table presenting the number of lost updates and deadlocks for each of the experiments. Discuss the relationship between isolation levels, lost updates and deadlocks.  Why is there a difference between performing the update in a single statement and as two statements?  Why does the waitfor statement affect the result? 

 

 

Hints:

1.      When you run your program in TPPT, it will manage the connection to the database, so you don't need the connection code. However, when you debug your program in JDK or JBuilder, your code must establish the connection to the database.

2.      Before calling a stored procedure or executing an SQL statement in your program, make sure it's correct using Jisql.

3.      Because only one connection is allowed on SingleUser server at a time, if you cannot connect to the server, please wait for some time and try again.

4.      The TPPT manual can be found on the Hints page.

 

 

Hand In:

1.        The Java source code and schema file. (Basically, what you have modified on the basis of the ClientJava folder I provided.)

2.        Timing results produced by the tool. (In original .wri format.)

3.        The final state of the Faculty table. (can be included in your report)

4.        Your report.

 

 

Last updated on 10/30/08 by  Tingbo Hou