# Database transactions - can I open in one node and close in a node further along a flow?

**URL:** <https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432>\
**Category:** General\
**Created:** [27 June 2023 11:04 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432 "2023-06-27T11:04:20Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![hughsheehy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hughsheehy/32/58679_2.png) [@hughsheehy](https://discourse.nodered.org/u/hughsheehy)\
**Post date:** [27 June 2023 11:04 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/1 "2023-06-27T11:04:20Z")

</div>

In Node-red, can I open a database transaction in one node (to lock a row, or several rows), perform calculations/checks etc on the data in subsequent function nodes, then update the database and close the transaction in a later database node further down the flow?

[Input] -\> [Open Transaction] -\> [Perform Operations] -\> [Close Transaction] -\> [Output]

Or is that asking for trouble somehow?

H

---

<div class="post-metadata">

**Author:** ![hughsheehy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hughsheehy/32/58679_2.png) [@hughsheehy](https://discourse.nodered.org/u/hughsheehy)\
**Post date:** [3 July 2023 16:11 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/2 "2023-07-03T16:11:51Z")

</div>

Anyone?

If the answer is "don't be silly, of course you can", that'd be fine.

Otherwise I'll be a loon checking db logs to make sure I'm not upsetting the db somehow.

H

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [3 July 2023 16:30 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/3 "2023-07-03T16:30:18Z")

</div>

Most database nodes have a configuration node for authorisation etc, so that the [Perform Operations] and [Close Transactions] happen in one node automatically, passing the results of the query, which is usually passed to the DB through msg.topic, in msg.payload.  
Is there a reason you need this to happen in different nodes?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [3 July 2023 18:35 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/4 "2023-07-03T18:35:00Z")

</div>

Depending on the node, you could probably execute multiple statements in a transaction.

But I don't know of any nodes that permit you to (separately) start a transaction, executive other nodes, call to database, then commit/rollback.

---

<div class="post-metadata">

**Author:** ![hughsheehy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hughsheehy/32/58679_2.png) [@hughsheehy](https://discourse.nodered.org/u/hughsheehy)\
**Post date:** [3 July 2023 18:38 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/5 "2023-07-03T18:38:21Z")

</div>

In answer to both questions....it's perhaps that I'm doing things in a bad sequence, but we have several dbs and sequences of logic required to identify whether the row we look up will - or will not - subsequently be acted on.

Hence wondering if it was possible to lock the queried row(s) while doing the other db calls (like other DBs) and the calculations to know what we'll actually do (or not) so that when we know whether we'll change the rows (or not) we don't have to worry about those rows having been changed in the interim.

I hope that makes sense.

H

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [3 July 2023 20:53 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/6 "2023-07-03T20:53:42Z")

</div>

> [@hughsheehy](#):
>
> Hence wondering if it was possible to lock the queried row(s) while doing the other db calls (like other DBs) and the calculations to know what we'll actually do (or not) so that when we know whether we'll change the rows (or not) we don't have to worry about those rows having been changed in the interim.

Surely that would be something to do using an SQL query?

---

<div class="post-metadata">

**Author:** ![hughsheehy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hughsheehy/32/58679_2.png) [@hughsheehy](https://discourse.nodered.org/u/hughsheehy)\
**Post date:** [3 July 2023 21:53 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/7 "2023-07-03T21:53:19Z")

</div>

> [@hughsheehy](#):
>
> I hope that makes sense.

Can I do all that inside an SQL query?  
Seriously? Query multiple databases, include results from APIs, calculations, etc?  
I was not aware of that.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [3 July 2023 22:27 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/8 "2023-07-03T22:27:37Z")

</div>

> [@hughsheehy](#):
>
> (to lock a row, or several rows)

I think you want to be very careful if you are actually considering this strategy. Database locking is not the way to go (read on ACID properties).

Why can't you perform the check/calculations inside the query ? You could create triggers that are automatically performed based on the trigger criteria.

---

<div class="post-metadata">

**Author:** ![hughsheehy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hughsheehy/32/58679_2.png) [@hughsheehy](https://discourse.nodered.org/u/hughsheehy)\
**Post date:** [3 July 2023 23:20 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/9 "2023-07-03T23:20:02Z")

</div>

> [@bakman2](#):
>
> Why can't you perform the check/calculations inside the query ? You could create triggers that are automatically perform

i'll have a look. As I said, the calculations need input from several sources, including our own db.

thanks

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [4 July 2023 05:50 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/10 "2023-07-04T05:50:44Z")

</div>

> [@hughsheehy](#):
>
> we don't have to worry about those rows having been changed in the interim

This is the key.

How come that the rows _ **can** _ be changed while things need to be checked ?  
Is there another process writing/updating the same records ?

---

<div class="post-metadata">

**Author:** ![hughsheehy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hughsheehy/32/58679_2.png) [@hughsheehy](https://discourse.nodered.org/u/hughsheehy)\
**Post date:** [4 July 2023 10:21 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/11 "2023-07-04T10:21:25Z")

</div>

Potentially, yes.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [2 September 2023 10:22 UTC](https://discourse.nodered.org/t/database-transactions-can-i-open-in-one-node-and-close-in-a-node-further-along-a-flow/79432/12 "2023-09-02T10:22:22Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.