DB lock when deleting a dataset which has a running analysis

[javitonino]

Context

Happened to @AbelVM while working on a map. He deleted a dataset that still had a geocoding analysis running and his whole account froze up.

Steps to Reproduce

1. Create a dataset
2. Create a map over that dataset
3. Add a costly analysis, something that will take a couple of minutes to run. Data observatory works well.
4. Delete the dataset
5. Try to load any page in that user account

Current Result

Pages don't load at all. They will timeout, and will start working again when the analysis finishes. Looking at the database, it appears what happens is the following:

• There is a query running on the table, the analysis
• The DROP TABLE waits until the analysis is finished (trying to obtain an exclusive lock on the table).
• Most (all?) pages in CARTO include information about the user, including the quota. That means the quota is calculated in each request by using SELECT cartodb.CDB_UserDataSize('abel'). That function blocks waiting on the DROP TABLE

End result, everything stops working.

Ideas on what to do here?

• Maybe we should try to cancel the analyses before deleting a dataset. Is that viable?
• Another option would be to check if there is something we can do with CDB_UserDataSize so it doesn't block if there is a DROP pending
• We could also try to add a statement timeout to the DROP table so we can cancel it if it gets stuck.

cc @CartoDB/builder-backend @CartoDB/dataservices @CartoDB/platform

[rochoa]

Do we need to drop the table synchronously?
Is the quota something critical that must be computed in all pages?

We could provide a mechanism to cancel an analysis (CartoDB/Windshaft-cartodb#522), but still, any query from the user that we don't control could run into the same situation.

We already had a discussion about CDB_UserDataSize performance/problems at https://github.com/CartoDB/cartodb-platform/issues/970.

[javitonino]

Do we need to drop the table synchronously?

I think so. We need to delete the metadata from the table so the user doesn't keep seeing the table in the dashboard. And if we delete the metadata synchronously, but the table itself asynchronously, it's possible that ghost tables will recreate the metadata. We could expose the entire asynchronous thing to the user (show a message telling him that the dataset will be deleted soon), but I don't think we want that.

Is the quota something critical that must be computed in all pages?

It is used in a lot of places, certainly all the dashboard pages, to show the disk usage. So we need the data. We could discuss caching it somehow, instead of retrieving it from the database.

Yes, cancelling analysis just solved the more visible problems, but is still a problem for long-running queries in general. Maybe the timeout while executing DROP TABLE is the safer option then.

[javitonino]

This happened to @AbelVM again today, but not with an analysis. Instead, an import from SQL was running when the delete to the original table started running. Again, the account froze.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.