Recap for Concurrency: Tracking Database Changes

We talked how to track database changes with one auditing table last month. If we want to replicate these changes into another database, then we might use multi-threading to gain the high performance in data ingestion. However, based on our previous design, we track changes to column level, which generates a serious concurrency control issue. Let’s think about, we have a data table, with 10 columns updates at once, then all these changes are captured and inserted into my_audit_table. If at the same time, another table gets updated, then there might have some cross inserting in my_audit_table, which makes the multi-threading read even harder.

Is there a better approach that we should go? In this case, it’s worth to re-design the audit_table. Let’s see how it looks like (in Oracle PL/SQL):

Comparing previous design, we dropped column_name, old_value and new_value columns. The purpose is to avoid concurrency conflicts. We watch out table_name, compound primary_key and compound primary_value, which may determine which row gets updated, so we are able to capture row-level changes. Performance wise, it might not be worse than handling column-by-column changes, although we didn’t calculate it in detail.

Now let’s see how we handle the compound primary keys – for tables with more than one primary key.

The last line outputs a concatenated string like: pk_colname1,pk_colname2,pk_colname3.
We may use similar approach to handle primary column values and store in new_audit_table through trigger.

In this way, we may capture the entire table changes. As long as a row data changes, we know which its compound primary key and values, so we may capture it in new_audit_table. When we trace back, we use the action and status column to get status and trace back to original table from which we may get data for replication operation.

Should you have any questions in database design or database synchronization, please feel free to contact us. Stay tuned for our upcoming high performance enterprise database synchronization platform РLiveSync Automation.