system.db.closeTransaction error (caused by: transaction closed)

Hello folks,

First time trying to use transactions, and I can't figure out what I'm doing wrong.
First, the basics:

  • perspective 8.1.17 (requirement for this project)
  • mssql 2022 20.04
  • I'm going mad

I tried a bunch of things, but the smallest sample I could get that raises the same error is this:

tx = system.db.beginNamedQueryTransaction('project', 'db')

I do get a transaction id, I printed tx and got a reasonable value.
The error:

java.lang.Exception: Error executing system.db.closeTransaction(a9ef64fc-09ce-4dcd-8c3e-84a0506ae01b)

There's this in the trace:

Caused by: java.sql.SQLException: Transaction "a9ef64fc-09ce-4dcd-8c3e-84a0506ae01b" is closed.

So, what's going on here ? What am I missing ?

I don't know that this is causing this issue, but normally you either commit or rollback the transaction before closing it. I wonder if that is at play at all.

I also don't see where you are setting the isolationLevel though that shouldn't matter actually.

This is a bug, I think. Look for a thread with the same script call and a response from Kevin Collins.

1 Like
1 Like

Yea I remember this post.

I just tried your script in 8.1.17 and it does give me the same issue just like you had @pascal.fragnoud. However, I also just tried it in 8.1.26 and had no issue. If you can upgrade that's probably your easiest solution.

Sadly, I can't.

@PGriffith Well... let's see if I understand the bug correctly:
A transaction is not actually opened when using 'beginNamedQueryTransaction', but put on a queue until a query is actually made. So, trying to close the transaction before running a query fails.

If I got that right, then there's something else wrong, because before skimming every other part of the script, it looked more like something like this:

tx = system.db.beginNamedQueryTransaction('project', 'db')
    system.db.runNamedQuery('insert_foo', tx=tx)
    for b in bar:
        system.db.runNamedQuery('insert_bar', tx=tx)
except Throwable as t:

This also gave me the same error.

edit: Just got back to the dev machine, gave this another shot and... no errors (with the full script).

edit again:
Aaaand it's back ;D
I think I'm starting to understand what's going on - maybe.
I might need help with properly catching errors, especially with their types.

I'm a bit confused about something. Does using transaction make system.db.runNamedQuery run on the gateway scope ?

The calls to that function fail with "project 'path/to/namedquery' does not exist". The project name is not part of the function parameters when in perspective scope. I call it from a button onActionPerformed event.
Running the same query, with the same call, at the same place, but outside of a transaction, doesn't raise that error.

Adding the argument name explicitly (path="path/to/namedquery") makes the error disappear and the query succeed.
So I'm confused.

1 Like

I remember when I was using .17, although the path is documented as a necessary argument, I had to use a keyword argument like you said path='somePath' to make things work. I was confused by it too.

The upgrade from .17 to .26 is free if that makes any difference. If transactions are going to be a big part of this project I would really hate to have to work around a bug like this tbch. I feel like this is alone warrants the upgrade. Especially if you use work arounds now and then in the future, they do decide to upgrade...

I don't have any answer for you just that I remember needing everything to be a keyword argument to work despite it not being documented in that fashion.

I can't upgrade.
I could ask the customer to do it, but I already know they won't.
They have to send analysts and architects and maybe even a priest before they upgrade things, so it's not something they do on a regular basis.

Actually, I don't have access to the gateway machine at all.
I can't use themes because of that. And I don't have the css stylesheet because 1.17.
It's a pain in the @$$, but that's something I have to deal with.

Putting this on a button works perfectly:

	a = system.db.runNamedQuery("test")

	project_name = system.perspective.getProjectInfo()['name']
	tx = system.db.beginNamedQueryTransaction(project_name, 'mssql')
	b = system.db.runNamedQuery("test", tx=tx)
	system.perspective.print("a: {}, b: {}".format(a, b))

The test query is a simple select 1, and I get the right results in the console.
No need for explicit path= here.
Why am I getting totally different behaviors ?

If I add a 'real' query just after the test one... like this:

from java.lang import Throwable

project_name = system.perspective.getProjectInfo()['name']
tx = system.db.beginNamedQueryTransaction(project_name, 'mssql')
system.perspective.print("project name: {}".format(project_name))
	system.db.runNamedQuery("test", tx=tx)
	system.perspective.print("test success")
	system.db.runNamedQuery("families/create", {'name': family_name}, tx=tx)
	system.perspective.print("family success")
except Throwable as t:

I get

The test query works, but the real one doesn't.
The only difference is the real one is an update query. Is that what's causing all this ?

What if you provide the project name and path as kwargs? Or args? The error does imply that its taking your first arg as the project name project not found: families/create

Personally I would tell them to call the priest because this bug is demonic imo.

My point is, that the test query doesn't require naming parameters, but the other one does.
I can't make sense of it !

I understand your point that it does not sense hence why I say I would die on this hill for upgrading lol.

Try turning on NamedQueryManager to trace in your gateway, I am curious what it says when you run your test query what project it is running it against.