DB Query Reader with Database Transaction

[atothep]

Hi all,
yet another question regarding the usage of DB Transaction nodes.
My workflow looks roughly like this:

The first 4 nodes within the transaction do some typical delete-insert-update stuff which is necessary to be handled within one transaction. So far so good.
At the end of this, I need to do a SELECT (for which I use the DB Query Reader node) to get the data into KNIME. Only then, the transaction shall be committed, because the SELECT must see everything that happened in the transaction before, and all of this must be atomic.

Question: In this construct, is the DB Query Reader node really part of the transaction? If not, how can I make it part of the transaction? What state does it see when executed?

The ambiguous part is obviously that the DB Query Reader has no DB Session out-port that I could connect to the Transaction End node. Why?

[takbb]

Hi @atothep , the db session will be the same on the db query reader as it is on the DB Transaction End node because they are both been provided with the same session that was passed through from the DB SQL Executor. By connecting the flow variable port as you have, you have ensured that the DB Query Reader will execute before the Transaction End can execute so I believe all should be good.

Having a DB Session connector output port on the DB Query Reader possibly would make sense in terms of making a cleaner looking flow but isn’t necessary as long as you can trace the db session back to the same source, and you take steps to ensure the correct sequence of the nodes as you have done by using the flow variable connection.

I hope that helps.

[atothep]

Hi @takbb , thanks for the reply!
Meanwhile, I also investigated a bit more on MS SQL Server, and I am now pretty sure that it depends on the ISOLATION LEVEL whether the changes made in the transaction are visible to the SELECT in DB Query Reader or not.

To me, the crucial point is still that the DB Query Reader is (probably) not part of the transaction. I suspect that only those nodes that are located directly on the DB Session path between the Transaction Start and Transaction End nodes are part of the transaction. To my understanding, nothing else makes sense. Is that correct? Can someone from KNIME please confirm this?

So if that’s the case, then the DB Query Reader is outside of the transaction (although of course in the same session), but is executed BEFORE the transaction is committed. And that, in turn, means that ONLY IF the isolation level is set to “Read Uncommitted (1)”, the SELECT will actually see what happend in the transaction before. The not-so-nice side effect of this is obviously that every process outside the transaction will be able to see the uncommitted changes - which I typically don’t want.

And the question is still: How can I make the DB Query Reader part of the transaction?

[takbb]

Hi @atothep,

Certainly the isolation level will make a difference to what a particular session can see, and the help documentation on the DB Transaction Start mentions this, but if you have a single connection to a database (any SQL database that is configured to follow standard mechanism for transactions), then ANY query or other action that takes place prior to a commit will be within the same transaction.

The flow variable link between the DB Query Reader and the DB Transaction End ensures that the DB Query Reader’s query will be executed before the transaction ends. If it were on a different session it would be outside of the transaction, but to be on a different session it would have to have had its session created by a different Microsoft SQL Server Connector.

You should be able to test this theory by adding a second MS SQL Database connector and add to it a DB Query Reader of its own. Manually execute your existing transaction up to the (existing) DB Query Reader but not the DB Transaction End.

Manually execute the second DB Connector and DB Query Reader (configured to run the same query as the first). Are the results of the two DB Query Readers the same or different?

You could also try adding a DB SQL Executor after the DB Query Reader and issuing a Rollback and then execute a further DB Query Reader to see if there are differences between the DB Query Reader which I believe is inside the transaction, and the (new) one that follows.

In all of this I’m making an assumption that on your database you don’t have things such as auto-commit turned on (e.g. IMPLICIT TRANSACTIONS turned off in SQL Server and that ) or any other settings that might affect the way it works.

I have now read that SQL Server has settings ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT which if set would potentially mean that a query within a transaction will see the data as it was prior to the start of the transaction. I’ve never used that setting and to be honest find that quite a scary concept in that you cannot review the changes you’ve just made before committing. If you have those turned on, then in theory (I’ve never tried it) the DB Query Reader wouldn’t see the modifications.

If in doubt, I would think you could add DB SQL Executor before the DB Transaction Start and issue a

SET IMPLICIT_TRANSACTIONS ON /* ensure no autocommit */

and

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

---

All that being said, from what you say, I believe you are wanting to ensure that the DB Query Reader can read the data that has been modified by the DELETE, INSERT and MERGE that has taken place.

When you execute the DB Query Reader (but not the DB Transaction End) Are you saying that the DB Query Reader (as placed in your flow above) isn’t seeing those changes, and that if you were to place a second DB Query Reader after the DB Transaction End, that it would be returning different results?

[tobias.koetter]

Hello @atothep ,
I can confirm the very good explanation of @takbb regarding the DB Query Reader node being executed in the same transaction. In addition, as long as the transaction is in process, the input database connection cannot be used outside of the transaction (for more details see the documentation).
Bye
Tobias