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

General
1

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

2

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

3

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?

4

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.

5

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

6

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

1 Like
7

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.

8

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.

9

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

thanks

10

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 ?

11

Potentially, yes.

12

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