24
Views
1
Comments
Solved
[SQL Sandbox] Is Execute unsafe?
Question
Forge component by Leonardo Fernandes
17
Published on 24 Aug 2020

Hi,

I want to raise this post to see if anyone had similar issues and whether the developers could confirm whether this behavior has been observed.

We had severe problems using SQL Sandbox in a customer's Cloud environment. Use of the Execute action resulted in the entire development database locking up and timing out other queries (twice). In raising the issue with OS Support team, they explained that the queries which caused a huge load and prevented other queries from running on the database, occurred within the DELETE statements executed through the Execute action of the SQL Sandbox extension.

It's difficult to provide precise logs as evidence but we observed that by holding all other things equal, when we removed the Execute action, the load and timeout issues were resolved.

This particular scenario involved the Execute action being instanced at least 55 times within a single server action, sometimes more depending on the loop result. The DELETE queries were simple DELETE FROM [table] WHERE Id=@Id type statements. Finally, the debugger mode was on and the developer was stepping through the execution.

When we refactored the logic to replace Execute, we saw the issue disappear. We also saw that some of the DELETE statements actually had errors which we did not see before as they were not reported back to the debugger or thrown as an exception using Execute.

My guess is that because Execute creates it's own connection/transaction outside of OS, when there is an error during the step-through debug, the Execute action is not passing the error result or control back to OS causing the query to hang somewhere between. This is not always the case because some errors were reported but perhaps the build-up and overlap of Execute actions and step-through debugging caused a cascade of issues. We were able to lock-up the database twice this way and found it very difficult to discover the root cause other than tracing the locked queries to those run by SQL Sandbox and then observing the results after replacing Execute.

We replaced Execute with an in-line injection of the physical table name in the regular advanced SQL component which I guess stays within the OS connection/transaction.

Thank you,

Anthony

mvp_badge
MVP
Rank: #24
Solution

Hi Anthony, we appreciate your feedback and will take it in consideration.

This has never happened before.

We will review the component and refactor to avoid the issue.

Thank you.