Difference between beginTransaction and beginNamedQueryTransaction

Ignition
, , ,
1

The documentation doesn't seem to describe much difference between the system.db.beginTransaction and system.db.beginNamedQueryTransaction concerning which types of queries you're allowed to use with them.

If I want to run system.db.runPrepQuery and system.db.runNamedQuery in the same transaction, which function should I use to generate that transaction ID and why?

2

First my recommendation:

Consider if it is possible to combine the two queries into a single statement. If not consider writing a stored procedure in the DB and then using a named query to execute it.

Now for my reasoning:

If you wan't them to both be used in the same transaction, then you either need to convert the Prep query to a Named Query, and then use beginNamedQueryTransaction() or do the opposite and convert the named query to a prep query and use beginTransaction().

The documentation doesn't describe much difference between them because there isn't much. Literally one can be used with Named Queries and nothing else, and the other can be used with every thing else but not Named queries.

The reason is because Named Queries are a project resource and so in the back ground there are some hoops that must be jumped through in order to get the actual query that needs to be run. the beginNamedQueryTransaction() function places the transaction object in a place that is reachable by the code trace that runNamedQuery() must go through. (That's obviously an over simplification of all that's going on there)

Do you actually need a transaction for both queries? Are both queries update queries?

I would find it very odd that two separate queries that both perform an update would need to be executed in the same transaction. This implies that one query is influenced by the other, however, if a transaction is in place you have placed a lock on the table until the transaction is either rolled back or committed. In this case an additional query (I don't believe) would be able to operate on the same table simultaneously.

If asynchronous work is not needed, then it stands to reason that you would need two separate transactions, where the effects of each transaction are examined and rolled back or committed separately.

Remember, if either query is a select query, then it makes no since to have it wrapped in a transaction because you're not making any changes that could be rolled back.

3 Likes
3

You cannot. Use all named queries, or all regular or Prep update queries.

4

You can't tell me what I can and cannot do! :stuck_out_tongue_winking_eye:

image
image939×522 26.6 KB

I would, however, like you to tell me what I should do.

  • Is this intended behavior, the whole, it working part?
  • If it is intended behavior, should I be using it anyways?
  • If they both work, does it even matter which one I use?

I'm using Ignition v8.1.35 btw.

1 Like
5

I don't have access to the source. You'll have to wait for an IA employee to chime in. Your "it worked" is rather simplistic, as it does not tell you if the transaction restrictions are actually applied to each operation when the txid is the wrong kind.

6

That's true. And I don't have the time (or know-how) to test whether those transaction rules are in fact being enforced. I would consider this a dangerous bug if they weren't, since there's no indication that something failed.

For now, I'm not putting anything like this into production, but I'd still like an answer from IA on what's going on under the hood, when they get the chance :blush:

7

I disagree. Using a named query transaction ID for a non-named-query operation, and vice versa, is the bug. On your part.

Having more diagnostics would be nice, but you are still trying to use these in a non-documented way.

8

Ignition Docs for system.db.beginTransaction:

Description
Begins a new database transaction for using run* and runPrep* queries. Database transactions are used to execute multiple queries in an atomic fashion. After executing queries, you must either commit the transaction to have your changes take effect, or rollback the transaction which will make all operations since the last commit not take place. The transaction is given a new unique string code, which is then returned. You can then use this code as the tx argument for other system.db.* function calls to execute various types of queries using this transaction.

I don't think the documentation here is clear enough to say one way or the other. Reasonable minds may disagree. But this is the sentence that made me explore this further.

9

There are a few things that are gotcha's here.

  1. The transaction is really only important for Update queries unless you're at a higher isolation, I strongly doubt that you are.
  2. Wrapping a select in a transaction is done at Higher isolation level to protect them from dirty reads, in typical use there is no need for it as they are not making any changes that would need to be rolled back.
  3. You're only catching Python Exceptions, any SQL or Java error that occurred would also result in the transaction being committed.
  4. You're not examining the data returned from the query, so your "It worked" only means that no Python Exception occurred

I disagree. Why would you need a NamedQuery anything function if it wasn't specific? That in and of itself points to the logical conclusion that a NamedQueryTransaction is intended only for use with Named Quries, and that a normal transaction should not be used.

10

@lrose

Firstly, I appreciate your thorough replies.

Secondly, I'm not trying to ask for help on a specific set of queries or a particular problem I'm having. My question is more general, so that I can understand the mechanics better and make better design decisions going forward.

For your numbered points:

  1. I will regularly need to update a table row based on info I get back from a select statement, often on a different table. I do in fact use system.db.SERIALIZABLE in cases where atomicity and cross-table data relationship consistency are required.
  2. Yep, that's correct.
  3. Good to know! I was just trying for an example there, but I'll keep that in mind. I think a SQL or Java error would've printed a bunch of red in the output if one did occur, in this case, since those weren't explicitly caught.
  4. I did perform an example that I didn't post where I printed the results, and they both did print expected results. I don't yet know if the transaction isolation level was respected, however.

You're welcome to disagree with me on that. I can definitely see the intent behind the two functions in their name and descriptions. I don't think you're wrong about that. But the "system.db.*" sentence, combined with the fact that an error isn't thrown when using the wrong transaction numbers makes me wonder if there was ever any official support for mixing transaction types. That's why I made the post originally. I think we just need to wait for IA to comment at this point.

Either we get a fun new tool to use, or I recommend some additional detail in the documentation to make it more clear for people like me, who like to understand every possible way to use things.