Is there a way to transactionally move rows from one columnstore table (call it the source) to another table (call it the target) while the source table is being written?
For example, I’d ideally like to do something like this:
start transaction;
insert into facts_app_metrics select * from facts_app_metrics_staging where epoch = ...;
delete from facts_app_metrics_staging where epoch = ...;
commit;
However, if the source table is being written to during this, then it seems that an insert into the source table (from a separate thread) between the above insert into and delete from statements can result in the row not being moved but also being deleted.
I can change our implementation to have some sort of incrementing identifier so that we can add another condition onto the insert into / delete from statements. However, I was hoping to avoid that if possible. Any thoughts?
Unfortunately, MemSQL 6.7 does not support higher isolation levels than READ_COMMITTED, so the issue you observed cannot be worked around without adding extra logic to your application. Adding an extra identifier or otherwise building this logic into your application is unfortunately the best way to do this for now. In the future, we plan to support SNAPSHOT isolation level transactions, which will allow your app to work as written.
We did something similar moving ingested events from a staging table to a permanent table. However, we had both a timestamp of when the event happened, as well as when the event was inserted. So during the transaction, we always were dealing with some boundaries which would ensure we wouldnt have the issue you described. The one caveat is you have to be able to handle situations of late arriving events/rows.
Thanks everyone! We ended up going the timestamp route for this but will interested in hearing more about the snapshot isolation level transaction when it’s supported
