This article was inspired by a situation that was recently observed in a vendor supplied system I deal with. In a previous article we looked at concurrency handling where we were inserting or updating a row on a table with a simple primary key. These days people want to maintain the history of changes that have been made to their data and one approach to this requirement is to maintain a start and end date for each row. Commonly the entry that is currently in effect wlll not have an end date.
Given a table that implements a versioning strategy via this date stamping approach, our requirement is to create a mechanism to perform a versioning update. Here we will need to update the end date of the existing effective row and insert a new row, which will become the current effective entry. For simplicity, we will assume that we will only ever be inserting a new entry effective from the current system date.
The table used for our example is:
CREATE TABLE test_table (pk_id NUMBER(10) NOT NULL ,id NUMBER(10) NOT NULL ,start_date DATE NOT NULL ,end_date DATE ,padding VARCHAR2(100) NOT NULL ) / -- create the primary key constraint ALTER TABLE test_table ADD CONSTRAINT test_table_pk PRIMARY KEY (pk_id) / CREATE SEQUENCE test_table_pk_id_seq /
You’ll note that the only integrity constraint declared is a surrogate primary key, which is completely useless for imposing any business rule integrity to our data. More on this later. We’ll use the sequence created to populate this column.
Lets turn our attention to performing the required update. As noted before we need to perform two operations for our chosen entity ID; set the end date on the current entry if it exists (i.e. the row that currently has no end date) and create a new entry with a start date from the current system date. Since we’re using DATE data types for the start and end date range, we’ll ensure that the end date used is 1 second less than the start date.
Our script is as follows. We have a simple procedure to handle the update and insert operations that we require. We make sure to use a variable for the effective date so that there won’t be any problem of a delay between the update of the existing entry and the creation of the new one. We commit after each update and introduce a short delay to ensure we aren’t updating the table multiple times within the one second.
SET ECHO ON
SET SERVEROUTPUT ON
DECLARE
PROCEDURE new_version (p_id IN test_table.id%TYPE)
AS
c_one_second CONSTANT NUMBER := 1/24/60/60;
l_pk_id test_table.pk_id%TYPE;
l_eff_date DATE;
BEGIN
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Start of new_version');
l_eff_date := SYSDATE;
-- end date the current entry to be one second prior
-- to the start date of the new entry we will be
-- creating
UPDATE test_table
SET padding = padding || ', updated by SID ' || SYS_CONTEXT('userenv','sid')
, end_date = l_eff_date - c_one_second
WHERE id = p_id
AND end_date IS NULL;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - After UPDATE');
INSERT INTO test_table
(pk_id
,id
,start_date
,end_date
,padding)
VALUES
(test_table_pk_id_seq.nextval
,p_id
,l_eff_date
,NULL
,'Inserted by SID ' || SYS_CONTEXT('userenv','sid'))
RETURNING pk_id INTO l_pk_id;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - After INSERT - created row pk_id ' || TO_CHAR(l_pk_id));
dbms_lock.sleep(10);
COMMIT;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - End of new_version');
END new_version;
BEGIN
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Start of test');
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Create version 1');
new_version (p_id => 1);
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Create version 2');
new_version (p_id => 1);
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - End of test');
END;
/
The following query lists the content of the table, for clarity only showing the time component for the date columns:
SET LINESIZE 132
COLUMN PADDING FORMAT A60
SELECT t.pk_id
, t.id
, TO_CHAR(t.start_date,'hh24:mi:ss') AS start_date
, TO_CHAR(t.end_date,'hh24:mi:ss') AS end_date
, t.padding
FROM test_table t
ORDER BY
t.start_date
/
If we run this query after executing our script we get:
PK_ID ID START_DA END_DATE PADDING
---------- ---------- -------- -------- ---------------------------------------
1 1 06:49:46 06:49:55 Inserted by SID 141, updated by SID 141
2 1 06:49:56 Inserted by SID 141
All looks as we would want.
Now let’s see how well concurrency is handled. You’ll have noted that the short delay in the test script is done before the COMMIT. This is deliberate so as to keep the transaction active and allow us to illustrate the concurrency problems with the script. If we run the script from 2 sessions simultaneously and query our table we get:
PK_ID ID START_DA END_DATE PADDING
---------- ---------- -------- -------- ---------------------------------------
1 1 07:02:59 07:03:08 Inserted by SID 149, updated by SID 149
2 1 07:03:00 07:03:09 Inserted by SID 141, updated by SID 141
3 1 07:03:09 07:03:09 Inserted by SID 149, updated by SID 141
4 1 07:03:10 Inserted by SID 141
Hmmm… not exactly what we would want…
We can see that both sessions created an initial entry; one at 7:22:30 and one at 7:32:32. The session with SID 149 then updated the row with PK_ID of 1 and then created the row with PK_ID of 3. The session with SID 141 then updated the end date on the rows with PK_ID of 2 and 3 before creating the last row.
If we run the scripts a second time, after the table has been populated do we continue to have trouble? Let’s see:
PK_ID ID START_DA END_DATE PADDING
---------- ---------- -------- -------- ---------------------------------------
1 1 07:02:59 07:03:08 Inserted by SID 149, updated by SID 149
2 1 07:03:00 07:03:09 Inserted by SID 141, updated by SID 141
3 1 07:03:09 07:03:09 Inserted by SID 149, updated by SID 141
4 1 07:03:10 07:04:37 Inserted by SID 141, updated by SID 149
5 1 07:04:38 07:04:47 Inserted by SID 149, updated by SID 149
7 1 07:04:40 07:05:07 Inserted by SID 141, updated by SID 141
6 1 07:04:48 07:04:39 Inserted by SID 149, updated by SID 141
8 1 07:05:08 Inserted by SID 141
The problem is still there. We have overlapping date ranges, which will definitely cause trouble when trying to work out which entry was effective at any point in time.
So, what can we do to overcome this. As mentioned at the start of this post, there are no constraints declared on the table to enforce the integrity we’re looking for. Let’s see if we can create one.
Ideally we want a constraint that says “for a given entry, don’t permit the start date to overlap with the start and end dates in any other row and don’t let the end date overlap with the start and end dates in any other row”. Unfortunately it’s not possible to declare such a constraint.
One constraint I have seen people use is to declare uniqueness on the ID and START_DATE. Obviously the thinking is that we don’t want two entries effective from the same point in time for the same ID. However, if you look at the data in our table you will see that it meets this constraint and the data is still wrong. Clearly this constraint is inadequate.
Another possible constraint is to ensure that only a single entry has a NULL end date for a give ID value. This won’t actually prevent all types off errors but it may prevent the problems we are observing above. We can’t declare this as a constraint in Oracle but we can create a unique function-based index to enforce this:
CREATE UNIQUE INDEX test_table_i1 ON test_table (CASE WHEN end_date IS NULL THEN id ELSE NULL END) /
(Purists will point out that the “ELSE NULL” part is not required in the above CASE expression but I prefer to include it for clarity.)
So, rerunning our scripts in 2 sessions, how does this index impact us? One session completed successfully while the other session gave us the following error:
DECLARE * ERROR at line 1: ORA-00001: unique constraint (DEVELOPER.TEST_TABLE_I1) violated ORA-06512: at line 25 ORA-06512: at line 51
Line 24 is the INSERT statement. What’s happening here is that the first session has performed its actions and created a new entry with a NULL end date. The second session has performed its UPDATE followed by an INSERT. However, when the first session commits its changes then Oracle flags an error in the second session as the row it has tried to insert violates the unique index we have created.
So, on one hand we managed to prevent data corruption but at the expense of failing one of the transactions. Can we do better?
Given that we need to maintain data integrity over a number of rows and not just within a single row we need to look at different strategies for managing the data. Preventing multiple sessions from manipulating the rows for a given ID will allow a single session to perform its changes safe from interference. In general, serialising access like this should not be taken lightly but at times it may be necessary.
How can we prevent multiple sessions from manipulating the same set of data at at the same time? An obvious solution is to perform a SELECT FOR UPDATE on all the rows for a given ID before we perform our changes. The session that manages to lock the rows will be able to make its changes and any other session is blocked until it is able to obtain the lock. Lets give this a try by running the following script in 2 sessions:
SET ECHO ON
SET SERVEROUTPUT ON
DECLARE
PROCEDURE new_version (p_id IN test_table.id%TYPE)
AS
CURSOR test_cur (p_cur_id test_table.id%TYPE)
IS
SELECT *
FROM test_table
WHERE id = p_cur_id
FOR UPDATE;
c_one_second CONSTANT NUMBER := 1/24/60/60;
l_pk_id test_table.pk_id%TYPE;
l_eff_date DATE;
BEGIN
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Start of new_version');
-- obtain a lock on all the rows for the given ID by opening
-- a cursor that returns all the rows for the ID
OPEN test_cur (p_id);
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - After SELECT FOR UPDATE');
-- we calculate the effective date after we've obtained the locks, not before!
l_eff_date := SYSDATE;
-- end date the current entry to be one second prior
-- to the start date of the new entry we will be
-- creating
UPDATE test_table
SET padding = padding || ', updated by SID ' || SYS_CONTEXT('userenv','sid')
, end_date = l_eff_date - c_one_second
WHERE id = p_id
AND end_date IS NULL;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - After UPDATE');
INSERT INTO test_table
(pk_id
,id
,start_date
,end_date
,padding)
VALUES
(test_table_pk_id_seq.nextval
,p_id
,l_eff_date
,NULL
,'Inserted by SID ' || SYS_CONTEXT('userenv','sid'))
RETURNING pk_id INTO l_pk_id;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - After INSERT - created row pk_id ' || TO_CHAR(l_pk_id));
dbms_lock.sleep(10);
COMMIT;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - End of new_version');
END new_version;
BEGIN
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Start of test');
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Create version 1');
new_version (p_id => 2);
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Create version 2');
new_version (p_id => 2);
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - End of test');
END;
/
Both scripts executed successfully and the table shows:
PK_ID ID START_DA END_DATE PADDING
---------- ---------- -------- -------- ---------------------------------------
1 2 07:12:33 07:12:42 Inserted by SID 149, updated by SID 149
2 2 07:12:34 07:12:52 Inserted by SID 141, updated by SID 141
3 2 07:12:43 07:12:52 Inserted by SID 149, updated by SID 141
4 2 07:12:53 Inserted by SID 141
Oh, that’s not right though. The cause of the problem is probably clear; both sessions initially created new rows as the SELECT FOR UPDATE had no rows to lock so we didn’t get the serialisation that we wanted. Can we solve this? If we had a table that held the domain of IDs, i.e. one row per ID, then we could potentially lock that row. Whichever session held the lock would be able to make changes to the date versioned entries. Lacking this though we can create our own lock using DBMS_LOCK. With DBMS_LOCK we can “lock” a string value. If the value is constructed to reflect the ID that we’re updating then whichever session is able to lock that string is able to make the changes to the table. The script below illustrates this:
SET ECHO ON
SET SERVEROUTPUT ON
DECLARE
PROCEDURE new_version (p_id IN test_table.id%TYPE)
AS
c_one_second CONSTANT NUMBER := 1/24/60/60;
l_pk_id test_table.pk_id%TYPE;
l_eff_date DATE;
l_lockhandle VARCHAR2(100);
l_lock NUMBER;
BEGIN
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Start of new_version');
-- obtain a lock on a string that reflects the ID
dbms_lock.allocate_unique (lockname => 'TEST_TABLE_ID_' || TO_CHAR(p_id)
,lockhandle => l_lockhandle);
l_lock := dbms_lock.request (lockhandle => l_lockhandle
-- ,lockmode => dbms_lock.x_mode
-- ,timeout => dbms_lock.maxwait
,release_on_commit => TRUE);
-- we calculate the ffective date after we've obtained the lock, not before!
l_eff_date := SYSDATE;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - After DMBS_LOCK');
-- end date the current entry to be one second prior
-- to the start date of the new entry we will be
-- creating
UPDATE test_table
SET padding = padding || ', updated by SID ' || SYS_CONTEXT('userenv','sid')
, end_date = l_eff_date - c_one_second
WHERE id = p_id
AND end_date IS NULL;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - After UPDATE');
INSERT INTO test_table
(pk_id
,id
,start_date
,end_date
,padding)
VALUES
(test_table_pk_id_seq.nextval
,p_id
,l_eff_date
,NULL
,'Inserted by SID ' || SYS_CONTEXT('userenv','sid'))
RETURNING pk_id INTO l_pk_id;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - After INSERT - created row pk_id ' || TO_CHAR(l_pk_id));
dbms_lock.sleep(10);
COMMIT;
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - End of new_version');
END new_version;
BEGIN
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Start of test');
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Create version 1');
new_version (p_id => 2);
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - Create version 2');
new_version (p_id => 2);
dbms_output.put_line (TO_CHAR(SYSTIMESTAMP,'hh:mi:ss.ff3') || ' - End of test');
END;
/
Looking at the table data after we run the script in 2 sessions simultaneously, we see:
PK_ID ID START_DA END_DATE PADDING
---------- ---------- -------- -------- ---------------------------------------
1 2 07:23:18 07:23:27 Inserted by SID 149, updated by SID 141
2 2 07:23:28 07:23:37 Inserted by SID 141, updated by SID 149
3 2 07:23:38 07:23:47 Inserted by SID 149, updated by SID 141
4 2 07:23:48 Inserted by SID 141
Success! We can see that the two sessions took it in turns to create a new entry, end dating the previous entry created by the other session. The lock worked as expected. The serialisation of access isn’t without its downfalls. Most obvious, if the data is subject to a high frequency of update then permitting only one session at a time to make changes may result in unacceptable delays. However, if this were the case then I would probably argue that the data structure is inappropriate.
In this post we’ve seen that the lack of declarative contraints makes it easy for concurrent changes to corrupt the structure that we were expecting within our data. For this example we had to resort to a programmatic locking strategy to serialise modification of the data to avoid the concurrency problems. Not necessarily the best of strategies but one that may need to be applied when no other options are available.
Mark Hoxey 