 | Level: Introductory Alexander Kuznetsov, Certified Advanced Technical Expert , IBM
17 Sep 2002 Preventing lost updates may be challenging. This article describes a simple and very efficient solution to this problem using triggers and SQL stored procedures to generate unique numbers in the middle tier.
© 2002 International Business Machines Corporation.
All rights reserved.
Introduction
Let's suppose you want to rent a car for a trip someplace where it is snowing.
Because of the snow,
you specify both traction control and ABS as mandatory conditions in your search criteria.
While you are browsing a list of cars available for rent,
a rental agency clerk
is correcting a data entry error for the same car -- the car you have just chosen does not actually have traction control.
By the time you click Reserve, the error in the database has been corrected, but you
have, unknowingly, chosen a car without traction control.
This article describes just one simple approach to solve this problem, also known as the "problem of lost updates."
This article does not attempt to provide any comparisons of different solutions to this problem; it
describes a simple, efficient and scalable method of generating unique numbers in the middle tier.
Using unique record version numbers to prevent lost updates
A detailed analysis of the problem of lost updates may be found in [1]. Let's go through the scenario described in the introduction and consider another approach to solving it.
The data on rental cars is stored in a table. The table is populated with sample data:
CREATE TABLE CAR(PART_KEY SMALLINT NOT NULL,
ID INT NOT NULL,
MAKE_MODEL VARCHAR(100) NOT NULL,
TAG CHAR(9) NOT NULL,
HAS_ABS CHAR(1) NOT NULL DEFAULT 'Y',
HAS_TRACTION_CONTROL CHAR(1) NOT NULL DEFAULT 'N',
RESERVED_FOR VARCHAR(100),
VERSION_ID INT NOT NULL DEFAULT 1,
PRIMARY KEY(PART_KEY, ID));
INSERT INTO CAR
(PART_KEY, ID, MAKE_MODEL, TAG, HAS_TRACTION_CONTROL, VERSION_ID )
VALUES (1, 1, 'Chevrolet Prizm LSI 1998, White', 'RENT ME NOW', 'Y', 1); |
Having specified a search criteria, WHERE HAS_TRACTION_CONTROL = 'Y' AND HAS_ABS = 'Y' in my Web browser, I sent my request to the server. In response to my request, the server had returned a result set and completely forgot about my request, which means no cursors were left open, and all the locks had been released. Yet, every record sent to my Web browser was provided with a unique record version number, VERSION_ID. Record number 1 satisfied my search criteria and was retrieved along with its version number, 1. (Version number 1 is unique across the whole table, which means no other record will ever have version number 1). The clerk retrieved the same record, did a fast update, and saved the changes. To indicate that the record had been updated, the middle tier generated a new version number for this record, 2.
UPDATE CAR SET
HAS_TRACTION_CONTROL = 'N',
VERSION_ID = 2
WHERE ID = 1
AND VERSION_ID = 1;
|
This update succeeded. Note the condition AND VERSION_ID = 1 in the WHERE clause. Our approach is either to update the same version that was retrieved or to fail. We don't want to overwrite somebody else's changes. For this schema to work correctly, we must provide a different record version number every time a record is updated. Let's create a BEFORE UPDATE trigger to guarantee that every updated record gets a different version number:
CREATE TRIGGER CAR_UPD_CHECK
NO CASCADE
BEFORE UPDATE ON CAR
REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
FOR EACH ROW
MODE DB2SQL
WHEN (NEW_ROW.VERSION_ID = OLD_ROW.VERSION_ID)
SIGNAL SQLSTATE '80000'
('An attempt to update without providing a different version number')
@
|
When I finally made up my mind and clicked Reserve,
the middle tier generated a new record version number, 3:
UPDATE CAR SET
RESERVED_FOR = 'Alexander Kuznetsov',
VERSION_ID = 3
WHERE ID = 1
AND VERSION_ID = 1;
SELECT * FROM CAR;
|
The update failed, as it should, because the version number of the record did not match
(DB2 returned SQLSTATE '02000' No row was found for FETCH, UPDATE or DELETE).
The most important advantage of this approach is its simplicity. Also the performance impact of adding a very simple trigger is minimal. An existing index on ID is used for the updates (it is created for the primary key constraint), so there is no need to create additional indexes.
As we have seen, it is possible to prevent lost updates using very simple means. In a perfect world we would stop right at this place. However, in a real world there is another nasty possibility: the record you are going to update may be already deleted. Another one with exactly the same primary key value may be inserted in its place by the time you finally decide to save the record. Since neither sequences nor idenities are available in EEE,
this possibility is quite real. So let's make sure every version of every record has a version number unique across the whole table. That done, the newly inserted record will have a different version number. The same trigger that we have discussed before will detect this situation
just as well. Now let's discuss how to generate unique numbers in the middle tier.
Generating unique version numbers
If you are planning to have an informal party at home, and you suddenly realize you don't have any plastic cups, you might drive to the nearest grocery store and pick up a reasonable amount. Perhaps 50, 100 or 250; they don't take much room in the trunk anyway.
Plastic cups aren't perishable.
If any are left, they will certainly be used later.
If you have miscalculated and need to replenish the supply, you will probably make another trip to get another hundred or so. You wouldn't be making a round trip to the store to buy only one cup each time a guest arrived, would you? Well, me neither, even if the car runs great, the road is smooth and there isn't much traffic.
For our scheme to work, we need to provide unique version numbers for every version of every record.
If we are using DB2 UDB EEE V7.2, we do not have an option of using a sequence.
Several approaches to generating unique numbers in this situation are described in [2].
Let's consider another one:
a JavaTM class retrieves the unique numbers that are generated by the stored procedure
and provides them to other middle tier modules. Sample Java code is provided in Resources.
To get a unique number from a sequence implemented in DB2,
a middle tier process sends a request via network.
The request is then processed by a server running DB2.
Then the result is returned via network.
So, retrieving a unique number from a sequence comes at a price of a round trip over
the network and the overhead of a database call as shown in Figure 1.
Figure 1. Retrieving single unique numbers
Our implementation is somewhat different (Figure 2).
Figure 2. Retrieve intervals of unique numbers
A middle tier process requests a wholesale amount of unique numbers, for example 100. A stored procedure reserves a contiguous interval of unique numbers, say from 201 to 300, and returns the ends of the interval, a pair of numbers (201, 300). Now this middle tier process may consume these unique numbers by itself or provide them to other processes or both ... It's no longer any burden for the server or the network. It's 100 unique numbers at a price of just 1. (Well, not exactly. A custom implementation based on a stored procedure cannot possibly outperform a sequence that comes with DB2). Another middle tier process requests and receives the next wholesale amount of unique numbers, say 1000, from 301 to 1301. Generating 1000 unique numbers is as easy for both the server and the network as generating just 100. As unique numbers are generated, a record in a database is being updated and the updates are committed every time.
As we have seen, it's more efficient to retrieve intervals of unique values (Figure 2)
rather than get every value from the servers (Figure 1), because:
- Some workload is moved from the servers to the middle tier.
- Network traffic is significantly reduced.
Implementing this approach is quite simple. Let's create a table. In this example PART_KEY is a partitioning key by default:
CREATE TABLE AVAILABLE_NUMBER
(PART_KEY SMALLINT NOT NULL,
AVAILABLE_NUMBER INT NOT NULL);
|
Now let's populate the table:
INSERT INTO AVAILABLE_NUMBER(PART_KEY, AVAILABLE_NUMBER)VALUES(0, 0);
|
Here's the stored procedure that generates intervals of unique numbers:
CREATE PROCEDURE DB2INST2.GET_UNIQUE_VALUE_EXTENT
( IN NUM_VALUES INT,
OUT VALUES_START INT,
OUT VALUES_END INT,
OUT SQLSTATE_OUT CHAR(5),
OUT SQLCODE_OUT INT)
LANGUAGE SQL
P1: BEGIN ATOMIC
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE UNDO HANDLER FOR SQLEXCEPTION
BEGIN
-- You must extract both SQLSTATE and SQLCODE in one statement
SELECT SQLSTATE, SQLCODE
INTO SQLSTATE_OUT,
SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;
END;
-- Setting default values to output parameters
SET VALUES_START=0;
SET VALUES_END=0;
SET SQLSTATE_OUT=SQLSTATE;
SET SQLCODE_OUT=SQLCODE;
-- First let's update the record.
-- Because the UPDATE statement is inside an ATOMIC block, an update lock
-- on the record is held until the transaction is committed.
UPDATE AVAILABLE_NUMBER SET AVAILABLE_NUMBER = AVAILABLE_NUMBER + NUM_VALUES;
-- It's assumed there is only one record.
-- The statement will fail if there are multiple records.
SELECT AVAILABLE_NUMBER INTO VALUES_END FROM AVAILABLE_NUMBER;
SET VALUES_START = VALUES_END - NUM_VALUES;
-- The client will COMMIT the transaction and release the update lock
END P1
|
Users are not granted any rights on the AVAILABLE_NUMBER table, they only have EXECUTE privileges
on the stored procedure.
The SQL UPDATE statement in the stored procedure must acquire an update lock -- only then an interval of values is retrieved and returned to the middle tier.
Another call of this stored procedure must wait to acquire its update lock.
This guarantees the uniqueness of generated values, no matter how many connections retrieve them.
Note that when AVAILABLE_NUMBER exceeds its maximum positive value, an error is raised and no more values are generated.
Conclusion
As we have seen, it's possible to implement a simple algorithm to avoid "lost updates."
The price of doing this, of course, is adding a column and an update trigger on every table being protected.
Also we have seen the generation of unique numbers in the middle tier and discussed the advantages of this approach.
Acknowledgements
The author would like to thank Serge Rielau for a very useful discussion. The author would also like to thank Anna Krylova, his wife, and Elden Bastian, his friend, for their help and encouragement.
IBM, DB2, DB2 Universal Database are trademarks or registered
trademarks of IBM Corporation in the United States, other countries,
or both.
Java and all Java-based trademarks and logos
are trademarks or registered trademarks of Sun Microsystems, Inc.
in the United States, other countries, or both.
IBM
copyright and trademark information
Downloads | Description | Name | Size | Download method |
|---|
| Sequence sample | sequencesample.zip | 3KB | HTTP |
|---|
| Sequence sample | sequencesample.tar | 10KB | HTTP |
|---|
Resources -
[1] Baklarz, George, and Bill Wong. DB2 Universal Database Version 7.1 for UNIX, Linux, Windows and 0S/2 Database Administration Certification Guide, 4th edition.
-
[2] Yevich, Richard, Warwick Ford, and Susan Lawson. DB2 High Performance Design and Tuning.
- Sequence.java retrieves the unique numbers generated in the stored procedure and provides them to other modules.
About the author | | | Alexander Kuznetsov has 14 years of experience in software design, development and database administration. Currently he designs a multi-terabyte clustered database in DB2 UDB EEE . Alexander is an IBM Certified Advanced Technical Expert (DB2 Cluster) and an IBM Certified Solutions Expert (Database Administration and Application Development). He can be reached at alkuzo at mindspring.com and the comp.databases.ibm-db2 newsgroup |
Rate this page
| |
