CAP 7 - Transaction Already Closed error on SAP HANA Cloud

[geert-janklaps]

Hi,

After upgrading our multitenant solution to CAP 7, I'm facing an issue when performing multiple UPDATE transactions on HANA Cloud. Important remark, the first update transaction seems to work, as soon as I try to do a second update the server crashes and states: "Transaction already closed in the logs". Multiple SELECT statements don't have issues.

Good to know, this was working perfectly fine on latest CDS 6 release AND works perfectly fine when running the application with CDS 7 in BAS with SQLITE.

We're running all latest package versions and updated all the dependencies to the latest versions.

Code excerpt (1st update works, seconds update crashes the server):

Logs:

Am I doing something wrong here? Something that changed in CDS 7 that I might have missed? Or is this a bug?

Best regards,

Geert-Jan

[Willem_Pardaens]

When you mention that it works fine in BAS with CDS 7 and SQLITE, is this using the old sqlite (sqlite3) or the new sqlite (@cap-js/sqlite) package? The database services are going through a revamp, so it would be interesting to know if the error is due to sqlite-vs-hana or old-vs-new database approach in CAP 7. Don't have an immediate solution to your question, but just sharing what came to mind.

[geert-janklaps]

Hi willem.pardaens,

Seems I've missed the fact that there's an additional package for sqlite, so at this point I'm still using the old approach, I guess. I'm going to change the dependencies and switch to the new approach for sqlite as well and will come back asap.

[geert-janklaps]

Hi willem.pardaens,

Just switched to the new @cap-js/sqlite package and everything is still working as expected. So the issue still only occurs on SAP HANA Cloud. (double checked the configuration using cds env requires.db and the sqlite package is properly loaded)

[Willem_Pardaens]

Ok, then I'm not sure where to look next. Did you test with small volumes? I understand your code is doing parallel execution of individual SQL statements, which might be resource intensive and bump into a (new?) limit? I'd get more logs ("DEBUG=db cds run") and try to see where the HANA connection drops.

[geert-janklaps]

Hi willem.pardaens,

This is really small volume testing, in this case one header record and one item record. Updates are not really in parallel due to the await statements I guess.

Anyway, as I suspected, it's really the second update statement that causes the crash of the server, but when going through the debug log of the database, I noticed a strange thing in the generated SQL statement. So I compared it to the sqlite SQL statement.

On HANA it looks like:

On sqlite it looks like:

Might seem like a bug in the generation of the SQL statements in the HANA DB driver?

[florin1335]

Not sure when the transaction would be closed in your case, I assume you're in an event handler, but are you aware that the forEach doesn't wait for any of the updates to finish and instead it just starts them concurrently resulting your code to return immediately after the forEach before any of the update promises are finished? Try to store all the update promises in an array and await Promise.all them after the forEach, maybe this makes a difference.

[geert-janklaps]

Hi Florin,

Moving all the updates into an array was one of the improvements I was going to implement tomorrow. I'll let you know if this solves anything. (but I'm afraid the end result will be the same, in the end the current implementation worked before the upgrade to CDS 7 and is working fine on sqlite)

[former_member866625]

Hi!
I'm facing exactly the same issue after migrating an application from CAP 5 to CAP 7.

The function structureImportEnergyStation is a complex function where I'm doing multiple select queries, including projections on them and one INSERT query. As soon as the first query in the second function ( processHierarchy ) runs, the Transaction already closed error appears. I have no idea what is causing this and this was working just fine on CAPs 5 version.

To workaround the issue, I've wrapped the code inside the processHierarchy function in a manual transaction and it resolved the issue.

Unfortunately, it seems that this problem is happening in more places in the app, and, wrapping it in a manual transaction everytime this happens, doesn't look like a good solution in the long run.

Not sure if it's worth mentioning, but we are just using Hana, in the cloud, and to test locally.

[Willem_Pardaens]

I tried to replicate the issue with 'repeated HANA queries', but it seems to work fine for me. I put a test application here: https://github.com/willemi069808/CDS7HANA can you try and 'break' it? There is a 'test.http' file to do execute the queries.

[former_member866625]

Hello Willem!
I spent some more time around this, and I understood why in my case i got this error, and it made sense.
In the printscreen in m previous comment, the function processHierarchy is async, but i'm not awaiting for it, because I want the request to finish and this function to process in background. I guess that as soon as the request ends, the transaction is closed, and so, the first query ran by the processHierarchy after its closed, threw the error - which in my opinion is completly valid and I'm not quite sure why it used to work with cap 5.

In another part of the code where this transaction happened, was where I was doing multiple update queries inside a forEach, just exactly how the OP did in code in the part 2. I changed it to a "typical for" and it started working.

I will try to apply this logic on your code and see if I can also replicate the issue 🙂

[vansyckel · Accepted solution]

Hi Geert-Jan, all,

We improved transaction handling in cds7. What you are experiencing is protection against dangling transactions caused by, for example, missing awaits.

In particular, foo.forEach(async () => { ... }) doesn't do what you might think it does. It triggers execution in a promise, but does not await it. So the "main thread" move along and closes the transaction, while the promises are still underway. Use a for loop instead.

Best,
Sebastian

[geert-janklaps · Accepted solution]

Hi Sebastian,

Thank you for clarifying, reworked the whole logic to perform all database updates without using promises. Strange thing is that this issue doesn't occur on sqlite, so from a development perspective it's hard to determine what the root cause is. (I'm expecting the same issue to occur when running this locally in BAS)

I do think this definitely is in improvement, but I don't seem to find the warning about the new protection mechanism in the release notes. Maybe it would be good to mention it in the release notes as well?

Best regards,

Geert-Jan

[vansyckel · Accepted solution]

Hi Geert-Jan,

I doesn't happen on sqlite because there is no pool but a single connection with a queue. So on HANA, connections can be returned to the pool even though they will still be used (e.g., by not awaited promises). This needed to be improved.

I'll forward the feedback regarding the release notes.

Best,
Sebastian