Avoiding Lost Updates #2: Updateable Views
This is a followup to my earlier post “Avoiding Lost Updates: Protecting Data in a Multi-user environment”.
In this post I’ll talk about how this problem can be solved when you’re creating updateable views. Now, if you read the previous post, you’ll know the five options that I thought of (plus a sixth pointed out by Gary). For updateable views, if you want to protect against lost updates (in other words, “Last Update Wins” is not an acceptable solution), you can still use “Column Compare” and “Hash Compare” without any changes.
For the “Timestamp Compare”, “Version Compare” and Gary’s ORA_ROWSCN options, however, you’ll need to bring some data from the base tables up to the view’s SELECT clause.
For “Timestamp Compare” and ORA_ROWSCN, you need to get the latest of the timestamps/SCNs. You could use the GREATEST function for this (as long as ORA_ROWSCN is always greater for later transactions?).
It’s just as simple when you’re using “Version Compare”. It was pointed out by a colleague of mine that all you have to do is present the SUM of all the version columns from each base table that can be updateable via the view. This way, if any or all of the base tables experience changes, the “version” column in the view will increment by 1 or more. Most of the time, that is!
Let’s consider an example. We have the following imaginatively named base tables with a view on them:
CREATE TABLE table_a ( id NUMBER(12) PRIMARY KEY, t VARCHAR2(10), version NUMBER(38) NOT NULL); CREATE TABLE table_b ( id NUMBER(12) PRIMARY KEY, u VARCHAR2(30) NOT NULL, version NUMBER(38) NOT NULL ); CREATE TABLE table_c ( id NUMBER(12) PRIMARY KEY, v VARCHAR2(10) NOT NULL, version NUMBER(38) NOT NULL ); CREATE TRIGGER table_a_ins_upd_row BEFORE INSERT OR UPDATE ON table_a FOR EACH ROW BEGIN IF inserting THEN :NEW.version := 1; ELSE :NEW.version := :OLD.version + 1; END IF; END; / CREATE TRIGGER table_b_ins_upd_row BEFORE INSERT OR UPDATE ON table_b FOR EACH ROW BEGIN IF inserting THEN :NEW.version := 1; ELSE :NEW.version := :OLD.version + 1; END IF; END; / CREATE TRIGGER table_c_ins_upd_row BEFORE INSERT OR UPDATE ON table_c FOR EACH ROW BEGIN IF inserting THEN :NEW.version := 1; ELSE :NEW.version := :OLD.version + 1; END IF; END; / CREATE VIEW view_ab AS SELECT table_a.id, table_a.t, table_b.u, table_c.v, table_a.version + table_b.version + table_c.version version FROM table_a LEFT OUTER JOIN table_b ON table_a.id = table_b.id LEFT OUTER JOIN table_c ON table_a.id = table_c.id;
The view therefore shows all rows in table_a, as well as the corresponding rows in table_b and table_c if they exist.
Assume that the view_ab has instead-of-insert and update triggers that do the following:
Insert/update id and t on table_a.
If “u” is inserted or updated with a value, do the corresponding insert or update on table_b.
If “u” is cleared (set to NULL), delete it from table_b.
And the same for “v” in relation to table_c.
If I do this:
INSERT INTO view_ab (id, t, u, v) VALUES (1, 'A', 'B', 'C');
The view should have (1, ‘A’, ‘B’, ‘C’, 3). Version = 3 because we have one row in each of the three tables each with version = 1.
Now if I instead did:
INSERT INTO view_ab (id, t, u) VALUES (1, 'A', 'B');
The view should have the row (1, ‘A’, ‘B’, null, 2). Version = 2 because we have rows in table_a and table_b with version = 1, but no row in table_c.
The only purpose of the version column is to change if any insert, update or delete occurs on any of the base tables.
What if I do the following:
UPDATE view_ab SET u = NULL, v = 'C' where id = 1;
What will the view have? It will have (1, ‘A’, null, ‘C’, 2). Hang on there – I have done a change to the row, but the version has stayed the same! Why? Because the update trigger had to delete from table_b (reducing the total version to 1) and insert into table_c (increasing the total version back to 2).
How to solve this? I shall ponder this for a little while.