Transaction Important

[Rajat Sharma]

Hello everyone, hope you all are doing well.

Initially I have no idea regarding how transaction works in progress but now I started working and playing with them, I have few queries regarding transaction:

1. In FOR EACH loop, each iteration comes as an individual transaction. So for second iteration of FOR EACH loop, how lock table and .BI files entries are maintained for previous transaction. Either every current transaction delete the previous entry or append it (I thinks transaction deletes previous entry, please confirm).

For ex:

I know this transaction (mentioned below) works slow because each record is updated in database as a single record. But how .BI and lock table entries are maintained here.

FOR EACH customer where customer.country = “something”:
      Balance = Balance * 5.
END.

2. If I put DO TRANSACTION loop around this FOR EACH loop (mentioned above) then it becomes ALL or NOTHING kind of stuff and all the changes occurred in FOR EACH loop are written to database at the end of the DO TRANSACTION(as a single transaction). So before writing them into database, where are these updated record resides, I thing they become part of temporary memory. Please confirm.

I read that in DO TRANSACTION case that, lock table and BI entries are very heavy. So how they are maintained in this case?

3. In the example below, if transaction halts in between then neither the order instruction nor name of the customer are updated.

FIND FIRST customer WHERE balance >= “something” EXCLUSIVE-LOCK.
     IF AVAILABLE customer THEN
     UPDATE name.
RUN update_order.

PROCEDURE update_order: 
  FOR EACH order of customer:
        UPDATE instruction.
  END.
END PROCEDURE.

If I put DO TRANSACTION around FIND FIRST (please refer below code for this) then I could prevent myself from this situation. But is the right solution for this problem, because I studied about REPEAT loop that it takes one transaction for each iteration of the loop and we don’t need to worry about lock table overflow problem as well. I tried using REPEAT in this situation but unable to find right approach of using that.

For ex:

DO TRANSACTION:
  FIND FIRST customer WHERE balance >= “something” EXCLUSIVE-LOCK.
  IF AVAILABLE customer THEN
  UPDATE name.
END.
RUN update_order.

PROCEDURE update_order: 
   FOR EACH order of customer:
         UPDATE instruction.
   END.
END PROCEDURE.

As far as I understand, progress starts transaction whenever we write EXCLUSIVE-LOCK or when we UPDATE or WRITE something to database or when we use TRANSACTION keyword with DO or FOR EACH or REPEAT block.

So Here, I am not able to understand the meaning of TRANSACTION blocks. As per the documentation, FOR EACH, REPEAT and PROCEDURE as transaction blocks (why, are they going to start the transaction or what) and DO is not a transaction block (why). I tried many things between theses blocks but I didn’t find anything different.

Please Suggest.

Thanks & Regards!
Rajat.

 

[TomBascom]

Your sample code doesn't help you much because you are running it in isolation and without benefit of examining listing files. You would learn more if you tried running concurrent sessions with some strategically placed PAUSE statements and also looked carefully at where the COMPILE LIST option says your record buffers and transactions are being scoped.

Anyway...

"transaction blocks" will implicitly start a transaction. That mans that you do not need to add the TRANSACTION keyword to them, it is a default behaviour.

DO blocks do not have this property -- you must add the TRANSACTION keyword if you want a transaction scoped to a DO block -- otherwise the transaction scope will expand outward until it finds a block with the transaction property (ultimately that could be the containing procedure).

The default behavior made a nice demo in 1985.

It is, however, best practice to explicitly indicate which blocks you want to start a transaction, to strong scope the record buffer to that block and to keep the size of the block to a minimum. In this way the compiler will help you to find mistakes by producing warnings about transactions being declared when there is already a transaction active. That warning should always be taken very seriously -- it means that the compiler sees that your transaction scope is wider than you have declared (via your use of the TRANSACTION keyword) that you believe it should be.

IMHO it is also best practice to use a named buffer for updates. IOW:

define buffer updCustomer for customer.
do for updCustomer transaction:
  find updCustomer exclusive-lock where updCustomer.custNum = 1.
  updCustomer.discount = 0.
end.

In this way it is excutiatingly obvious that the only appropriate purpose for updCustomer should be to update the record. Any other usage would be obviously wrong. And any reference to updCustomer outside of a properly constructed DO FOR block will result in the compiler scolding you. (In the original meaning of "Hungarian Notation", before Microsoft misunderstood its own creation and foisted the practice of prepending utter gibberish onto perfectly readable names, that sort of thing is exactly what it was all about, http://www.joelonsoftware.com/articles/Wrong.html, not the current brain-dead idiocy about data types and so forth which even Microsoft has finally realized was, is and always will be a horrible idea.)

Many applications written in the 80s (or written by people who learned Progress in the 80s or who have spent their careers working on certain infamous code bases...) do not follow these best practices. That does not mean that you should emulate bad code and continue to perpetuate the problem.

The mechanics of how Progress internally tracks record locks and transactions to ensure the database's ACID properties are a very different conversation.

To quickly illustrate that point consider that the database supports two distinct languages -- 4GL and SQL92. The language constructs do not map directly to what the db is doing at a very low level. In other words the db really doesn't care about the properties of DO blocks and it doesn't actually know anything about "define buffer" or SELECT. It works at a much lower level than that. (If you really must discuss that stuff, rather than how block properties work, a new thread might be appropriate.)

 

[Rajat Sharma]

Thanks for replying Tom!

It’s difficult for me to understand the difference between sub transactions and nesting of transaction that could cause WARNING-TRANSACTION error message.

For Ex:

REPEAT:
      /* DATABASE UPDATE */
   REPEAT:
     /* DATABASE UPDATE */
   END.
END.

According to above code, transaction starts with first REPEAT block and found the database updating statements in second REPEAT block. Instead of creating another transaction it will continue with the existing transaction as previous (understood) and here, we call internal repeat block as sub transaction. But why not with this code (below):

DO TRANSACTION: 
   DO TRANSACTION:
   END.
END.

According to above code, first DO TRANSACTION block starts the transaction and second DO TRANSACTION block also demands a different transaction and cause WARNING-TRANSACTION error message (because first transaction is already active).

When first DO TRANSACTION block start the transaction then why second block doesn’t continues with the previous transaction (Same case of sub transaction). I think, Either transaction starts with DO TRANSACTION block is different form transaction which will began when we manipulate database record (via insert, create, update using EXCLUSIVE-LOCK) or something else.

Please suggest.

Thanks & Regards!
Rajat.

 

[TomBascom]

You are confusing compile time messages with run time behavior.

The compiler warning is telling you that you have coded something that looks wrong. You have explicitly declared that you are starting a transaction within another transaction. If this were real code, rather than a contrived example, you almost certainly did not really mean to do that and the compiler is drawing your attention to the mistake. If you really did mean to do that you are almost certainly violating one or more best practices related to transaction and record scoping. I am not going to sympathize with that.

If you leave your sample code alone it will run. And a "sub transaction" will exist just as you expect. But you have not specified any error handling in your example and it is unlikely that this code will behave as you hope should an error occur in the inner block.

Best practice is to do as we have been saying in this and many other threads. Explicitly declare your transactions using strong scope, named buffers and tight blocks of code.

Implied transactions and sub-transactions violate that guidance. Pretty much anything that creates a sub-transaction is probably violating that guidance. (There may be a reasonable exception but I cannot think of one right now.)

Yes, there is a lot of crap code in the world. Don't emulate it, eliminate it.

 

[RealHeavyDude]

The concept of a sub-transaction is only known to the client - not to the database. The database only knows the big transaction. In theory a client could undo a sub transaction while the outer database transaction still is committed. This means that the client handles the undo with the aide of the local before image. As Tom already stated, this is a recipe for disaster - unexpected behavior. Whenever the compiler issuess a sub transaction warning ( or dead code nowadays ) you should inspect the code and fix it. In all my years programming with the ABL ( or 4GL as it was called back then ) I did never come across a reasonable use case.

I can only second that carefully designing - and I mean deliberately designing - buffer and transaction scope is the recipe for building application that are stable, performant and scale well. Still there is so many worst practice out there and most of it has its origins in the official ABL documentation from Progress. Progress is notorious for including bad or worst practice in their sample code. One explanation for that is that the documentation is created by people that develop the language and provide samples that illustrate how a feature work. In most cases you should take the ABL sample with a big grain of salt.

Heavy Regards, RealHeavyDude.

 

[Rajat Sharma]

Hello Everyone, Hope you all are well.

I have further questions on Transaction and Release statement:

Is it true that lock on the table could exist even if the transaction is being completed?. I tired many scenarios for achieving that, but found that once transaction is completed then the record is also accessible (in different session). Please suggest or share any example where buffer lock still exists even if transaction is completed.

I read in one of the previous post that we could use RELEASE statement for releasing lock on the table. I tried RELEASE statement inside transaction and try to access the record in different session but it doesn’t work for lock release.

For Ex:

DO TRANSACTION:
     FOR EACH customer WHERE balance < 100:
           UPDATE name.
     END.
     RELEASE customer.
     PAUSE 20.
END.

In the code above, when PAUSE statement executes, I tried to access the customer record (in different session) but records aren’t accessible there.

Please suggest.

Thanks & Regards!
Rajat.

 

[Rob Fitzpatrick]

Is it true that lock on the table could exist even if the transaction is being completed?

Yes, record locks can exist outside of transactions. Specifically, share locks.

I read in one of the previous post that we could use RELEASE statement for releasing lock on the table.

The RELEASE statement is not for releasing record locks, which are on the server. It is for releasing record buffers, which are on the client.

 

[TheMadDBA]

Not to mention that your transaction is explicitly set around the entire FOR EACH and not inside of the FOR EACH.

DO FOR <buffer> TRANSACTION is your best friend.

The record scope documentation is actually pretty decent and you should give it another read or two.

 

[Rajat Sharma]

Thanks for replying Rob, TheMadDBA,

Rob,
1.Does exclusive-lock can exist even if transaction completed?.
2. Regarding your second point on RELEASE statement, Here i don't know how to differentiate between client side and server side lock/record buffer.

TheMadDBA,
Sure, i will give another read to record scoping docs. I know if i use DO FOR <buffer> TRANSACTION then it would become strong scope transaction block (please suggest how could i relate this with my question) or perhaps, scope of my concern is slight different.

Please suggest.

Thanks & Regards!

 

[TheMadDBA]

1) It would be downgraded to a share-lock, but this still blocks exclusive-locks
2) Forget that the RELEASE statement even exists, you don't need to use it and many people are confused by what it is for (not for releasing locks).

The point is that you want all of your transactions to be a strongly/tightly scoped as possible. If you write your code properly then a lot of these issues/questions go away. Like in your example...

1) read the customer table with NO-LOCK
2) update a variable instead of the customer.name if the user is updating the value
3) use a buffer with DO FOR bfCustomer TRANSACTION to EXCLUSIVE-LOCK the bfCustomer record and update it.
4) assuming no other transactions are active at a higher level the customer record is unlocked as soon as the end of the DO FOR TRANSACTION ends.