Isolation Levels, Lost Updates, and Deadlocks
Lost updates can occur when locking is short term, as with the
isolation level of READ COMMITTED. Deadlocks can occur when
concurrent transactions request a same set of locks, each holding some
of the locks and waiting for the other locks. The purpose of the
experiment is to study the effect of different transaction code and
isolation levels on lost updates and deadlocks.
The task is to repeatedly increase a specific stock by $10 until
the allotted time runs out, using the same Invest table and data as in
Assignment 2. You are to implement the task in two ways, by calling
two different stored procedures, and then do two sets of experiments
with each implementation using TPPT. Both stored procedures do a
single $10 increase:
Each experiment runs 10 terminals concurrently, using a same script,
which calls a stored procedure repeatedly for 10 seconds:
- IncreaseStock1 uses a single UPDATE statement to do the $10
- IncreaseStock2 uses a SELECT statement to fetch the stock amount,
increases it by $10, and then uses an UPDATE statement to store the
result back in the table.
Since IncreaseStock1 and IncreaseStock2 each does a $10 increase, we
can calculate the number of lost updates based on the number of times
IncreaseStock has been executed with the actual increase given to the
stock at the end of the experiment. The number of deadlocks can be
read off the results from TPPT.
- All scripts call IncreaseStock1 and execute at the default
isolation level (SERIALIZABLE).
- All scripts call IncreaseStock1 and execute at READ UNCOMMITTED
(isolation level 1).
- All scripts call IncreaseStock2 and execute at READ COMMITTED
(isolation level 2).
- All scripts call IncreaseStock2 and execute at SERIALIZABLE
(isolation level 8).
What to do
- Create the stored procedures IncreaseStock1 and IncreaseStock2.
- Prepare to run TPPT; you may start with the example files from
Assignment 3, but the following is all you need to set up:
- Create a schema file containing the Invest table and the Insert
statements as in Assignment 2, and the stored procedures you created
for this assignment.
- Let the initdata file be empty.
- Let the file prototype.txt contain only "int
- Let the script files, terminal0.scp to terminal9.scp, each contain
- Create a Java program IncreaseStockClass.java that defines "int
IncreaseStock(Connection con)" and invokes the stored procedure
IncreaseStock1 or IncreaseStock2 depending on the experiment.
- Run each of the four experiments with 10 terminals for 10 seconds.
You only need to do experiments on the UnderMultiUser server.
- Modify IncreaseStock2 by inserting a one second WAITFOR statement
between the SELECT and the UPDATE and repeat the two experiments with
- Prepare an analysis of the results: create a table presenting the
number of lost updates and deadlocks for each of the experiments;
discuss the relationships among different transaction code, isolation
levels, lost updates, and deadlocks; why is there a difference between
performing the update in a single statement and in two statements, and
why does the WAITFOR statement affect the result?
Hand in your description and code electronically, using Blackboard,
by midnight on the due date. Your handin should include
your Java/JDBC source code and the schema file,
timing results produced by the tool and the final state of the Invest table, and
your analysis of the results.
This homework will be graded based on 100 points, allocated in
proportion to the required amount of work for each part. You may do
this assignment in a team of two people; the two people will receive
the same points.