Redshift: How to fix serializable isolation violation(1023) caused by concurrent MERGE operations? 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.

Since Redshift doesn't enforce primary key(s) constraints, I'm using aws documentation Merge examples - Example of a merge that replaces existing rows to enforce unique rows. This method works fine when there is only 1 instance of lambda function running.

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


But as soon as > 1 lambda functions are running concurrently and accessing the same table, I'll receive: 

"ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift" when performing operations in a transaction concurrently with another session. 


How should I modify this example to allow it to run in a concurrent environment?
"Redshift is good at parallelism, not so good at concurrency. It's a feature."


Option 1

Is there more than one table you are loading to? 


  One strategy is to enforce no more that one concurrent lambda per destination table. 


You can do that with one primary lambda, limit it to 1 concurrent invocation. That primary can then in turn invoke a secondary lambda multiple times, each secondary handling a single destination table by passing on a filtered event message. You only need two lambda functions deployed, and only the secondary one is allowed multiple concurrent invocations. There are variations on that approach which involve queues or pubsub solutions. The performance gain here is in loading multiple tables concurrently rather than a single table. 

Option 2


  Just use update rather than a delete then insert.


The challenge is that if two sessions both delete from the sales where saletime &gt; somedate and then both insert records to the same table, then there is no order in which they could run serially which would produce the same result as when run concurrently. It's also ambiguous what you would want to happen when they run concurrently when the delete uses a wide date range like that. 


  This could still fail if two sessions both open transactions that try
  to update the same rows.


If that is the case then you are abusing Redshift, effectively trying to use it for write transactions like an app database, doing OLTP in an analytical OLAP system, and you should consider a different datastore that is optimized for writes, perhaps something like Cassandra, or Postgres. You can also use something like Snowflake with separate dedicated write compute cluster and read cluster over the same data. Even with those alternatives it is just like every other OLAP system, optimized for bulk loads not small transactions, you don't get MVCC or fancy transaction isolation modes simultaneously with distributed parallel read performance. If you have budget then there are some hybrid options like MemSQL that allow a bit of both, but have their own compromises. For example there's no large cheap data storage on S3 / Google Buckets / Azure Blob storage, RAM is fast but scaling RAM is not cheap.

Option 3


  Do more specific deletes by filtering to a specific set of primary key values.


If you deleted specific rows e.g. based on a list of primary key values, then the two sessions would be unlikely to clobber each other in the way it does with the date filter.

Most ELT/ETL tools - e.g. stitch, fivetran, hevo, too many others - that load to Redshift will dump to S3, then COPY to a staging table and then uses insert when it is certain they are new rows or update when it is not certain. If you split out the known inserts and do them first then you can minimize the impact of the less performant updates. 

When you do updates in Redshift, it will mark the rows for deletion (which will happen later in a vacuum operation) and insert the rows you are updating. 

So it is already going to do implicitly, what you are trying to do explicitly. I do like the idea of running the delete then the insert though, because it gets to the point of what is going to happen anyway, it's just that you have now surfaced the serializability challenge with that strategy. In fact I have worked with a system that does exactly that, however it runs on a single EC2 and writes serially to Redshift, and despite the best practice recommendations it is fast, but uses a significant amount of IO and CPU on the Redshift cluster. Bulk loading with COPY is cheaper, especially if you split files on S3 into a sympathetic number of files in ratio to your count of cluster nodes, and use a manifest; the only way to really achieve parallel loads.

If you have multiple sources updating the same destination table then I would consider landing the data in separate tables before combining them. 

If you have frequent changes coming from the same source to the same destination, then know that Redshift has a single commit queue and forces everything to be serialized, there is no performance gain to be had from attempting concurrent writes. To get the concurrent sessions to run, you either have to lock the table explicitly, a locking means one of them waits... which means they end up running serially. The other way you can get them to run without the error is organize your explicitly coded transactions to run serially, i.e. just limit it to a single invocation, in the knowledge that there isn't any real write concurrency anyway, so it's not going to lose any performance.

Redshift is just not optimized for that kind of write transaction isolation, the way that Postgres or other OLTP RDBMS are.

For more see How do I resolve the error "ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift"?