title: Redshift: How to fix serializable isolation violation(1023) caused by concurrent MERGE operations?
body: <p>My use case is to extract, transform and load data incrementally  and in real time from x number of Lambda functions. I expect multiple Lambda functions to be running concurrently and Redshift to stay alive for read queries.</p>
  
  <p>Since Redshift doesn't enforce primary key(s) constraints, I'm using aws documentation <a href="https://docs.amazonaws.cn/en_us/redshift/latest/dg/merge-examples.html" rel="nofollow noreferrer">Merge examples - Example of a merge that replaces existing rows</a> to enforce unique rows. This method works fine when there is only 1 instance of lambda function running.</p>
  
  <pre><code>-- Start a new transaction
  begin transaction;
  
  -- Delete any rows from SALES that exist in STAGESALES, because they are updates
  -- The join includes a redundant predicate to collocate on the distribution key 
  -- A filter on saletime enables a range-restricted scan on SALES
  
  delete from sales
  using stagesales
  where sales.salesid = stagesales.salesid
  and sales.listid = stagesales.listid
  and sales.saletime &gt; '2008-11-30';
  
  -- Insert all the rows from the staging table into the target table
  insert into sales
  select * from stagesales;
  
  -- End transaction and commit
  end transaction;
  
  -- Drop the staging table
  drop table stagesales;
  </code></pre>
  
  <p>But as soon as > 1 lambda functions are running concurrently and accessing the same table, I'll receive: </p>
  
  <pre><code>"ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift" when performing operations in a transaction concurrently with another session. 
  </code></pre>
  
  <p>How should I modify this example to allow it to run in a concurrent environment? </p>