I am intermittently seeing the following exception
Exception: Sqlite3 Error: cannot start a transaction within a transaction
Once I encounter this error it does not seem as if we recover (or at least in a timely fashion). I resort to restarting mapD. I do not get this error when writing to my tables, I get it when I am trying to create new tables.
A little background…
Our data is very frequently updated, each record is, at least for a period of time, subject to many updates. We are also doing a significant amount of calculations and aggregations (and as a side note, distinct counts are very important to us). In our specific case indexes do not help us and we are often in a situation where we are forced to brute force scan. That is where the power of the GPU is key to us. MapD does not (yet) support updates and deletes so in order to be able to work with mapD and with our frequently updated data, we keep track of the most recent “version” of each row (for each table) in a separate table. We will then join to that table for each query.
Fo example for a table called “Customers” we would have a corresponding table called “Customer_Current_Id”
While less than ideal, this works and keeps our big and complex queries sub-second (and this approach out performs any view, self-join or other non-materialized technique we have tried). The downside is that we have to regularly build this table in the background (dropping the previous). We are currently doing this every minute. It is in this process that I begin to receive the aforementioned error.
My questions are:
What causes this error (concurrency?)
Why does it seem we cannot recover from it?
Is the technique we are using insane?