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

**URL:** <https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603>\
**Category:** Ignition\
**Tags:** perspective, ignition81, database\
**Created:** [May 11, 2023, 4:43pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603 "2023-05-11T16:43:09Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 11, 2023, 4:43pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/1 "2023-05-11T16:43:09Z")

</div>

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:

```python
tx = system.db.beginNamedQueryTransaction('project', 'db')
system.db.closeTransaction(tx)

```

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 ?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 11, 2023, 5:14pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/2 "2023-05-11T17:14:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 11, 2023, 5:27pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/3 "2023-05-11T17:27:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 11, 2023, 5:29pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/4 "2023-05-11T17:29:28Z")

</div>

> [@\[IGN-3829\] Error executing system.db.commitTransaction](https://forum.inductiveautomation.com/t/ign-3829-error-executing-system-db-committransaction/49595):
>
> When I use a named query transaction, but do not actually run any queries against it I receive an error. If I do the same thing using system.db.beginTransaction I do not get an error. Any ideas on how to get around this? txId = system.db.beginNamedQueryTransaction("myDatabaseConnection") system.db.commitTransaction(txId) system.db.closeTransaction(txId) Java Traceback: Traceback (most recent call last): File "\<input\>", line 28, in \<module\> at com.inductiveautomation.ignition.common.scrip…

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 11, 2023, 5:31pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/5 "2023-05-11T17:31:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 12, 2023, 6:34am UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/6 "2023-05-12T06:34:47Z")

</div>

Sadly, I can't.

> [@pascal.fragnoud](#):
>
> perspective 8.1.17 (requirement for this project)

@paul-griffith 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:

```python
tx = system.db.beginNamedQueryTransaction('project', 'db')
try:
    system.db.runNamedQuery('insert_foo', tx=tx)
    for b in bar:
        system.db.runNamedQuery('insert_bar', tx=tx)
except Throwable as t:
    system.db.rollbackTransaction(tx)
else:
    system.db.commitTransaction(tx)
finally:
    system.db.closeTransaction(tx)

```

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).  
Eh.

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.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 12, 2023, 1:13pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/7 "2023-05-12T13:13:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 12, 2023, 1:17pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/8 "2023-05-12T13:17:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 12, 2023, 1:21pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/9 "2023-05-12T13:21:28Z")

</div>

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.

edit:  
Putting this on a button works perfectly:

```python
	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.db.commitTransaction(tx)
	system.db.closeTransaction(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 ?

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

```python
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))
try:
	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:
	system.perspective.print(t)
	system.db.rollbackTransaction(tx)
else:
	system.db.commitTransaction(tx)
finally:
	system.db.closeTransaction(tx)

```

I get  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/c/9c44720a953907f174a0910c018b47dedae3ee25.png)

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 ?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 12, 2023, 1:48pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/10 "2023-05-12T13:48:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 12, 2023, 1:53pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/11 "2023-05-12T13:53:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 12, 2023, 2:05pm UTC](https://forum.inductiveautomation.com/t/system-db-closetransaction-error-caused-by-transaction-closed/73603/12 "2023-05-12T14:05:20Z")

</div>

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.