identifiier (identity column)
key1 (serves as key)
key2 (serves as key)
I now wish to expand on the key of this table. For a combination of key1 and key2 columns now want to preserve a sequence number called seq
This sequence number is just to be running count of all key1 and key2 combination rows starting at 1. For a combination of key1 and key2 all rows that are to be inserted, must be inserted with the seq column incremented from the previous maximum for that key1 and key2 combination.
Each row is to be inserted one at a time. The seq number for a new insert must be determined from the max of seq of rows already in the table for that key1 and key2 combination.
If such a combination does not exist then set seq number to 1
So I now want to be able to store rows in the table as follows
(identity) A, B, 1, X, Y, Z (seq number set at 1)
(identity) A, B, 2, R, S, T (seq number incremented)
(identity) A, B, 3, X, Y, Z (seq number incremented)
(identity) B, C, 1, P, Q, R (seq number re-set at 1 for new key1 and key2)
You get the picture.
What would be the best way to perform this auto-increment of the seq number keeping in mind concurrency issues?
Any other viable alternatives to achieve the same?
It is too expensive to normalize further at this point.
The semantics are rather straightforward. I am only interested in preserving a count of the rows for the same key1 and key2.
This count is intended to be used in two ways... for ordering all rows for a given key1 and key2 and to preserve a history of all rows for the same key1 and key2. This really is a sequence number. It depicts the number corresponding to a multiple occurence of key1 and key2 and the table must be expanded to handle such multiple occurences.
Previously there would only be one row for key1/key2 combination. Now the usage has been expanded and I must now handle multiple rows for which data1, data2 and data3 may be different.
I can easily select max(seq) from X where key1 = 'AAA' and key2 = 'BBB'
and then use max+1 to insert a new row.
I guess my primary question here is how can I perform the above guaranteeing atomicity?
Ok, when you insert a new tuple with values of key1 and key2 that have been used before, do you consider the old tuple(s) out of date, i.e. is the reason for the sequence to keep a versioning of the data?
If this is the case, the temporality (or versioning) can be build in by using the timestamp data type.
I did consider using timestamps, but have decided against it, for the following reasons...
1) My preference is to use standard data types for columns and not ASE extensions.
2) It is possible that these sequence numbers will be used in various ways within the application code and I find using straight forward numbers more elegant and easy to use in the application code rather than timestamps.
Is there some way to achieve select max(seq) + 1 and then insert as an atomic operation?
hmm i still think that timestamps are the best solution for your problem. The use of timestamps is the ordinary way to build temporal databases, and most dbms support them, oracle, db2, mysql, so its now a Sybase extension. I even think they are included in the full SQL92 standard.
Otherwise, Im not sure how to solve this problem in the best way. You can of course use an insert trigger and update the field, either from selecting max() or simply to have a copy of the identity field.
I still dont understand the problem in full. Since you use an identity column, you already have a natural ordering and the sequence number should not be needed. Instead (for nice presentation) you can rewrite your selections from this table to map the identity numbers to 1, 2,3 ...