"Error: cannot start a transaction within a transaction" during busy writes and creating new tables


#1

Hi,
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?


#2

Hi,

We have seen this issue before and are in the process of resolving it. I assume the error hits at apparent random?

The error is occurring on an internal metadata db we keep in sqlite, which does not do a great job of overlaping txns. So your high rate of drops and recreates triggers a bad state in that internal DB until it gets restarted. We are resolving this by managing our own locking around these structures.

Stopping and restarting mapd is currently the only way to fix that error.

If it works for you its not insane :wink:

regards