bobm_dashlogistics schrieb am 07.06.2012 um 16:00 (-0000):

> 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?

One solution would be to brutally serialize the problem out of the way:
First do Sales, then do Timeclocks. Get rid of the concurrency, get rid
of the race.

Another would be to re-schedule Sale records without matching Employee
record for later processing. Timeclock records may create Employees,
but Sale records may not. Back them up to a separate file or separate
table and process them again in due time. Employee records will have
been created and the problem will have been avoided.

Michael