Luigi: Redshift: Serializable isolation violation

[guyneedhamblis]

We often get an error when multiple jobs are attempting to update the table_updates table in Redshift at the same time. The error is thrown when the PostgresTarget output class calls self.exists just after writing to the table_updates table, when another task is writing to the table. The DB cannot guarantee the results of the query, and this gets thrown:

Runtime error:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/site-packages/luigi/worker.py", line 181, in run
    new_deps = self._run_get_new_deps()
  File "/usr/local/lib/python2.7/site-packages/luigi/worker.py", line 119, in _run_get_new_deps
    task_gen = self.task.run()
  File "/usr/local/lib/python2.7/site-packages/luigi/contrib/redshift.py", line 256, in run
    output.touch(connection)
  File "/usr/local/lib/python2.7/site-packages/luigi/postgres.py", line 166, in touch
    assert self.exists(connection)
  File "/usr/local/lib/python2.7/site-packages/luigi/postgres.py", line 177, in exists
    (self.update_id,)
InternalError: 1023
DETAIL:  Serializable isolation violation on table - 276701, transactions forming the cycle are: 8680257, 8680319 (pid:28411)

I'm considering making a pull request to add to the error handling in luigi/postgres.py, to catch the InternalError, wait for a time period, and then retry the select.

Firstly, does anyone know how best to catch the error? I don't know where in psycopy2.errorcodes the error code is, and don't want to just catch any InternalError.

Secondly, does such a backoff approach to handling this have any merit?

[dlstadther]

I often run into this error. A few people in the mailing list suggested some stuff to avoid it. However, i'm interested in where you're at with programmatically solving this issue.

[dlstadther]

@guyneedhamblis Any update here?

[dcheck]

We're running into this as well. We see this behavior a lot when two database jobs run simultaneously. Here's how is happens:

1. Job 1 Starts. It opens a transaction and begins a long running process such as a table load from s3.
2. Job 2 Starts. It opens a transaction and begins a shorter process than Job 1.
3. Job 2 Finishes. As part of finishing, it performs a touch on the marker table. It then commits a full transaction that includes the main job and the updates to the marker table.
4. Job 1 Finishes. As part of finishing, it performs a touch on the marker table. The INSERT statement is fine, but the SELECT statement that verifies that the INSERT worked fails because the current marker table has been changed by Job 2 since the transaction was started.

The virtue of the current system is that a job is only completed if the job itself, as well as the marker table insertion, happens and happens successfully. As a nice side effect, if the same job is somehow run in two places as once, we commit the transactions of the first to insert a row in the marker table and rollback the transactions of the second. (At least I _think_ this is true.)

So I think a successful solution:

1. Allows multiple db-based jobs to run against the same marker table simultaneously without triggering this exception.
2. Rolls back the entire transaction if the marker table can't be written to successful.
3. (Optional?) Guards against a situation where a row in the marker table with the update_id was not present when the job was started but is present when the job is completed and ready to be committed.

Are these requirements right? Thoughts on the third point especially?

[dcheck]

This solution, btw -- just removing the assert from touch -- seems to solve the issue. The only thing it misses, I think, is the third condition above. Does anyone think it's worth the added complexity that would required to guard against that condition?

If no one responds affirmatively, either @cabouffard should bring his solution over here as a PR, or I will.

[dcheck]

Thanks submitting the PR @cabouffard!