Why Pre-reserving your keys prior to inserting data is critical to avoid having to deal with conflict resolution.
When you connect multiple systems together one of the first steps is determining what key(s) you will use to uniquely identify records in those systems. The reason for this is so that you have a reliable reference for a record throughout all of your systems so you can prevent duplicate entries even though your systems potentially have very different uses.
Automatic key generation
When you create a new record many systems will generate new keys for you automatically and that’s great when they do! If that’s the case, then you don’t have to worry about this problem. An identity column is the most common example of this in SQL environments for example:
Create Table Customers (
FirstName varChar(30),
LastName varChar(30),
Phone varChar(16),
CustomerID int identity(1, 1)
)
If a table is defined this way, then the CustomerID column will automatically be generated for me when I insert into this table. The 1,1 part indicates the seed (what number to start with) and how much to increment when the system generates the next key.
However many systems don’t handle this for you and that’s where you can run into a conflict resolution problem. That was the case at one of our clients so I wanted to share how we compensated for it.
Creating unique identification system for records
When systems don’t auto-generate the next value for you, you have to generate it yourself by finding the maximum last used value. Then typically you increment by one and voila there is your next ID and you insert it into the table. A simple example would be:
Declare @Max int
Select @Max= max(CustomerID) from Customers
Set @Max = @Max + 1 -–Now we have generated the next key for our insert
This sounds simple enough but it’s a dangerous mistake because you have made the assumption that your program is the only process creating data which is likely NOT the case.
Since there is a lag time between when you program has generated the next value to when it is actually inserted this creates a window where another process could potentially attempt to insert the same ID before you do causing a conflict resolution issue.
Solving conflict resolution issues
The solution for this issue is pre-reserving keys before you insert. This requires a table or other permanent storage that your processes refer to for getting the next values instead of each process independently deciding what the next key should be. An example of doing this with a table would be:
Create Table NextKeys (
Key nvarchar(50),
LastValue int
)
Then each program looks up the next value from the “NextKeys” table and simultaneously increments the last value so that other processes will not conflict with this one. An easy solution is to do this in a stored procedure if you have the ability to do so:
Create procedure dbo.GetNextCustomerID(
@CustomerID int output
) as
Select @CustomerID = LastValue from NextKeys where key = ‘CustomerID’
Set @CustomerID = @CustomerID + 1
Update NextKeys set LastValue = @CustomerID where key = ‘CustomerID’
Return
Creating a stored procedure
Once the stored procedure exists you simply call it to get the next key:
Exec dbo.GetNextCustomerID(@CustomerID output)
Or if your program needs to insert batches of records you can create one that will allow you to reserve a range:
Create procedure dbo.GetNextCustomerIDWithRange(
@StartingCustomerID int output,
@EndingCustomerID int output,
@Range int
) as
Select @ StartingCustomerID = LastValue from NextKeys where key = ‘CustomerID’
Set @ StartingCustomerID = @ StartingCustomerID + 1
Set @ EndingCustomerID = @ StartingCustomerID + @Range
Update NextKeys set LastValue = @ EndingCustomerID where key = ‘CustomerID’
Return
GO
Creating the stored procedure may not be possible if you don’t have server side access for your project and if that’s the case you just have to find a place to store the last key value that can be shared with all of your integrations. That way you avoid querying the main table directly and risk that the data has changed before your insert can complete.
Summary
Conflict resolution is something that can rarely be avoided in it’s entirety when even when implementing data integration best practices. Mainly for the simple fact that most systems are not designed to go together so nuances like this one are constantly challenging us to create the tightest business integrations possible. When you design your integrations be sure you understand your systems of record and perform profiling to walk through your integrations before you begin to do the work. That will help you identify the potential areas for conflict design a better integration.
Setting up Your Own Ecommerce Integration? Download Your Free Step-by-step E-book Guide
You May Also Like:
