Transaction is active or not in application level

[GaneshP]

Hi,
Help me regarding my doubt.
transaction is active or not in below block query?
for each customer exclusive-lock:
---<no update or assign statements>
end.
when i checked with "transaction" keyword inside block its saying as "yes", but _Trans (VST) table "_Trans-state" showing as nothing.
please tell me which one i can consider? either Active or Not and why?

 

[Rob Fitzpatrick]

A for block doesn't have to contain database update statements to cause a transaction to be started. As you have seen, a for each table exclusive-lock: end. will also start a transaction, from the client's perspective. So the transaction function will return true.

But the AVM is designed to defer updates to the database when possible, so it won't send a transaction begin note to the database until it needs to. If your for block never updates the database, the client never needs to tell the database that a transaction has started and so the client won't have a record in _trans.

Which one to consider depends on why you want to know about your transaction state. For example, if you want to know whether you are in a transaction to determine whether your code will potentially cause BI growth, you would look at _trans. If you don't have an _trans record, or you do but its _trans-state is not "Active" then this client, in its present state, won't cause BI growth.

On the other hand, if you want to know whether you are in a transaction to confirm your suspicions about your record scope, then the fact that the AVM says you are in a transaction is relevant and you should look at that rather than _trans.

 

[GaneshP]

Thanks Rob for explanation, if some one ask should i say transaction is not active still transaction function return true in application level until unless changes on the database level.

 

[TomBascom]

It really depends on why the question is being asked.

Generally speaking -- if a programmer asks then the TRANSACTION() function is more important.

The details about deferring transaction start notes are primarily of interest to a DBA and they can change over time. For instance, if you go back in time to v8 the block of code in your example would have created lots of BI notes. There have been many optimizations since then and there are like to be more in the future.

From a coding perspective managing record, transaction and lock scope in the code is extremely important and you should never use an excuse like "the db optimizes these notes to not be written in certain cases" as a justification for writing bad code.

 

[tamhas]

And, it should be noted that a transaction *is* active and anything you do inside that block *will* get undone if the transaction is not completed. You may not see this because there is no permanent record, but any undo variables, for example, will be returned to their starting state.

 

[RealHeavyDude]

On the for each you request an exclusive-lock. You can only acquire an exclusive-lock within a transaction. That is the very reason why the transaction function function will return true. This is done by the compiler and does not happen at runtime. Furthermore, in this case, every iteration of the for each is an individual transaction. Since you don't reveal what happens in the for each we don't know. But I do speculate that it isn't much and therefore, depending on the size of the data set, you have a lot of transactions with an extreme short duration on the database.

Therefore - somebody correct me please if I am wrong - IMHO it is pure luck, when you query the _Trans VST that it shows an active transaction. Or in other words, due to timing it would be hard to prove you point.

The rest is buffer and, more prominently, transaction scope. The scope of the transaction will be scoped to the next outer block with transaction scoping capabilities. Since a for block does have the transaction capabilities, the transaction is scoped to that block.

Very often bad transaction scoping comes from developers not understanding that an exclusive-lock can only be acquired within a transaction and that an innocent looking find ... exclusive-lock at the procedure block level will scope the transaction - which is caused by the find ... exclusive-lock - to the procedure itself. Depending on what is going on in the rest of the procedure, you might wind up with a monster transaction bringing the database down when the before image is exhausted.

Understand buffer and transaction scope is essential to developing applications in the ABL. More importantly, for static database references, if you don't take control - the compiler certainly will. Since dynamic database references are resolved at rumtime you might wind up hitting runtime errors if you don't take control.

Please don't try to prove a point with half-revealed demo code. You should be able to prove your point with a real world code example.

 

[GaneshP]

Thanks all

 

[TomBascom]

Therefore - somebody correct me please if I am wrong - IMHO it is pure luck, when you query the _Trans VST that it shows an active transaction. Or in other words, due to timing it would be hard to prove you point.

When testing these sorts of things I find it useful to add a PAUSE statement at the point where I want to know what the VSTs have to say about such things. In the case of an empty loop like this:

find first _myconn no-lock.
display _myconn.
pause.

for each customer exclusive-lock:
  pause.
end.

_TRANS will not have an active record at the PAUSE statement:

for each _trans no-lock where _trans-usr = 6:  /* usr# 6 taken from snippet above */
  display _trans.
end.

But that does not mean that the code is good or that it will not eventually cause problems.