title: Why is an implicit table lock being released prior to end of transaction in RedShift?
body: <p>I have an ETL process that is building dimension tables incrementally in RedShift.  It performs actions in the following order:</p>
  <ol>
  <li>Begins transaction</li>
  <li>Creates a table staging_foo like foo</li>
  <li>Copies data from external source into staging_foo</li>
  <li>Performs mass insert/update/delete on foo so that it matches staging_foo</li>
  <li>Drop staging_foo</li>
  <li>Commit transaction</li>
  </ol>
  <p>Individually this process works, but in order to achieve continuous streaming refreshes to <code>foo</code> and redundancy in the event of failure, I have several instances of the process running at the same time.  And when that happens I occasionally get concurrent serialization errors.  This is because both processes are replaying some of the same changes to <code>foo</code> from <code>foo_staging</code> in overlapping transactions.</p>
  <p>What happens is that the first process creates the <code>staging_foo</code> table, and the second process is blocked when it attempts to create a table with the same name (this is what I want).  When the first process commits its transaction (which can take several seconds) I find that the second process gets unblocked before the commit is complete.  So it appears to be getting a snapshot of the <code>foo</code> table before the commit is in place, which causes the inserts/updates/deletes (some of which may be redundant) to fail.</p>
  <p>I am theorizing based on the documentation <a href="http://docs.aws.amazon.com/redshift/latest/dg/c_serial_isolation.html" rel="nofollow noreferrer">http://docs.aws.amazon.com/redshift/latest/dg/c_serial_isolation.html</a> where it says:</p>
  <blockquote>
  <p>Concurrent transactions are invisible to each other; they cannot detect each other's changes. Each concurrent transaction will create a snapshot of the database at the beginning of the transaction. A database snapshot is created within a transaction on the first occurrence of most SELECT statements, DML commands such as COPY, DELETE, INSERT, UPDATE, and TRUNCATE, and the following DDL commands :</p>
  <p>ALTER TABLE (to add or drop columns)</p>
  <p>CREATE TABLE</p>
  <p>DROP TABLE</p>
  <p>TRUNCATE TABLE</p>
  </blockquote>
  <p>The documentation quoted above is somewhat confusing to me because it first says a snapshot will be created at the beginning of a transaction, but subsequently says a snapshot will be created only at the first occurrence of some specific DML/DDL operations.</p>
  <p>I do not want to do a deep copy where I replace <code>foo</code> instead of incrementally updating it.  I have other processes that continually query this table so there is never a time when I can replace it without interruption.  Another question asks a similar question for deep copy but it will not work for me: <a href="https://stackoverflow.com/questions/20692518/how-can-i-ensure-synchronous-ddl-operations-on-a-table-that-is-being-replaced">How can I ensure synchronous DDL operations on a table that is being replaced?</a></p>
  <p>Is there a way for me to perform my operations in a way that I can avoid concurrent serialization errors?  I need to ensure that read access is available for <code>foo</code> so I can't <code>LOCK</code> that table.</p>