As you extend your application horizontally, there are some items that you have to consider for your application. One of the items is how to handle unique key collision. Unique key are usually generated by an Oracle SEQUENCE. Since the application is now split over multiple servers, a single SEQUENCE with the same value cannot be used since they will generate the same KEY value. Here are some methods customers are using to handle creating different unique key.
1. Using range for each system. For example:
Server 1 – using range from 1-999,999,999
Server 2 – using range from 1,000,000,000-1,999,999,999
Server 3 – using range from 2,000,000,000-2,999,999,999
2. Using a distinct set of unique value for each system. For example if you have 2 servers:
Server 1 – use SEQUENCE that generate odd number (CREATE SEQUENCE supplier_seq MINVALUE 1 START WITH 1 INCREMENT BY 2 CACHE 20;)
Server 2 – use SEQUENCE that generate even number (CREATE SEQUENCE supplier_seq MINVALUE 1 START WITH 2 INCREMENT BY 2 CACHE 20;)
In the case of having more than 2 nodes, we should use the same following convention. There are a couple of considerations that customer should considered.
- Assigned node number for each server in an active-active setup.
- Consider the max number of node you will have in an active-active setup.
- When creating SEQUENCE, use the following template
CREATE SEQUENCE sequence_name MINVALUE 1 START WITH node_number INCREMENT BY n CACHE 20;
Node_number is the assigned node number in an active-active setup.
n is the number of max node in an active-active setup.
As you select a method to handle uniqueness collision, we always has to keep in mind on how to recover the database and reset the sequence to its rightful values.
With method 1, to re-create the sequence, you will have to scan the unique key for largest value in the range, for example “max (col) where col > min_range and col < max_range”.
With method 2, you will have to find the largest value and depending on the node number, you will need to set the next available value. For example:
“(Trunc ( (max (col) + n) / n)) + node_number”
Where n=Number of nodes in replication and node_number = the assigned node number. The same formula can be used on any node.
Tom Chu, SharePlex for Oracle Product Manager