What are the implications of beginning a named query transaction, and having some system.db.runNamedQuery calls WITH tx, and others without? If the WITHOUT calls are SELECT (read only) queries, I would think it shouldn't having any bearing on the transaction. If the WITHOUT calls are UPDATE queries, I would expect that the DB updates would happen outside of the transaction.
If the runNamedQuery is called with tx=None, is that the same as not including the param in the sig?
When I do supply the tx param (to even scalar/SELECT queries) I'm getting a ProjectNotFoundException.
The same tx provided to a previous UPDATE NQ produces no exception.
I'm using system.util.getProjectName() to get the project name and it's a Gateway script.
Thanks! Cheers!
A lot of good questions here.
Just calling system.db.beginNamedQueryTransaction() doesn't do much. We create an object that holds the information you pass in, create a transaction number, and add it to an executor map. We don't contact the database yet because a transaction (often? always?) keeps a db connection tied up, so we wait until something of interest happens to do that.
Depending on the isolation level you set when you begin the transaction, other transactions (single statements in SQL are basically transactions even if they aren't wrapped in a transaction) may or may not affect the transaction in question. The Oracle docs on transactions have a decent summary of isolation levels, but unless you have a special need, I'd say just leave your isolation level at the default, which should be serializable. That will give you the behavior it sounds like you're looking for.
If you call runNamedQuery with any null or blank value for tx, that's the same as not including the parameter.
I'd need more information on the ProjectNotFoundException to be able to help you with that. Maybe a short example script that demonstrates the problem.
3 Likes
I have an idea of what the issue is: The first NQ in the transaction has an INSERT, which is then followed by an UPDATE into the same table using the SCOPE_IDENTITY() from the INSERT to SET one of the values.
INSERT INTO foo (fields) VALUES(values)
UPDATE foo
SET bar = 'foo' + cast (SCOPE_IDENTITY() as varchar(10))
WHERE id = SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY()
No exception is given when running this NQ (a Scalar Query Type), but the ProjectNotFound exception is thrown on the subsequent (even if it's a SELECT only).
Is this a known issue or will it require additional testing and/or proof?
I assume if I wrap the INSERT/UPDATE NQ in it's own BEGIN TRANSACTION, it will NOT be the same transaction as any formed with beginNamedQuery; that any rollback called wouldn't rollback the INSERT?
EDIT: If I remove the the UPDATE and final SELECT SCOPE_IDENTITY(), and make the Query Type = Update, I still get the ProjectNotFound exception. Also defaulted isolationLevel and timeout; no joy.
I think the issue is that I need to supply the project name to NQ. I suspect that when it's running outside of a transaction, and sill on the gateway, it's able to figure out the project name. I'll test later ...
Yes, there are cases where if it can figure out the project name (e.g. a Perspective session) it will supply that for you.