I have multiple ETL processes that are inserting data at the same time. Some of the tables that this data is being inserted into have FK constraints that must be maintained during the ETL process. See pseudocode below for an example:
create table employee ( employee_id integer, employee_nbr integer unique constraint, employee_name varchar(50)); create table sales ( sales_id integer, employee_id integer references employee.employee_id, units integer); create table time_clock ( time_clock_id integer, employee_id integer references employee.employee_id, hours integer); During the ETL process for Sales, we need to verify that the Employee record already exists, and if not then we insert it before writing the Sales record so that we can satisfy the FK. The same process happens during the import of the TimeClock data. The problem occurs when a new employee appears in both the Sales and TimeClock data and are processed at the same time in different transactions. The process looks like this: T1: Start transaction T2: Start transaction T1: Process Sale record T1: Verify Employee - does not exist T1: Insert Employee record T1: Insert Sale record T1: Process next Sale record T2: Process Timeclock record T2: Verify Employee - does not exist (can't see other transaction) T2: Insert Employee record - violation of unique constraint Both transactions are using READ_COMMITTED at this point, which is of course the problem. I can't commit either transaction because the entire Sale or Timeclock dataset must succeed or fail. Any ideas on how to do this type of process successfully? Thank you.
