Active Transaction - Forum - OpenEdge RDBMS - Progress Community
 Forum

Active Transaction

  • OE 11.4

    HP-UX 11.31

    When is it a active transaction?  when the trans has a start time?  If that is the case then promon is not correct in the database summary screen.

    Thoughts?

  • What makes you think promon is incorrect?

  • Promon VS ABL

    My ABL program does not match the number of active transactions in Promon ( I know it is a moving target), however, should be close.

    Snapshot

    Promon = 111 active trans

    ABL        = 13 active trans

    here is my code.

    FOR EACH _trans WHERE _Trans-State EQ "ACTIVE" BY _Trans._Trans-Duration

    DESCENDING:

     cnt = cnt + 1.

     FIND FIRST _Connect WHERE _Connect._Connect-Usr = _Trans._Trans-Usrnum.

     DISP STRING(TIME,"HH:MM:SS") LABEL "TIME NOW"

          _Connect._Connect-Name

          _Connect._Connect-PID FORMAT 99999

          _Trans-State

          _Trans._Trans-Usrnum

          _Trans._Trans-Txtime FORMAT "X(40)" WITH WIDTh 132 .

    END.

    DISPLAY cnt.

  • "Active trans" in promon/Activity: Summary means the number of transactions also reported by promon/Status: Active Transactions. "Trans State" on last screen can be of any type. These promon's screens were created when transactions had only one state: "active".

    > When is it a active transaction? when the trans has a start time?

    Will a transaction in the BEGIN or ALLOCATED state cause bi growth?
    http://knowledgebase.progress.com/articles/Article/P98099

    Why does the client hold the Transaction State 'Begin' for more than a minute?
    http://knowledgebase.progress.com/articles/Article/P17119

    Explanation of the Transaction State values of Begin, Active, or Phase 2 as seen under promon:R&D, #1 Status, #4 Processes/Clients, #3 Active Transactions
    http://knowledgebase.progress.com/articles/Article/P65988

  • Thanks, that helps!  I had a user today sitting in an update field, doing  nothing, however, it had a start time and my bi is now over 5GB.  

  • there are three possible transaction start situations (v9 and later):

    0) when the transaction starts in the (4GL) client. But here it is delayed as long as possible so the database is not told about it right away even though the application may be executing inside a "do transaction" block. the datbase does not know the clinet wants a transaction, or the transaction may be started in one connected database and not in another. promon does notshow a transaction for the client.

    1) the 4GL has told the database it wants to start a transaction but the transaction start is being postponed until the clinet performs a database operation that makes a change to the database. the database knows about the transaction and is lying to the client about it. promon show a transactionstate of "Begin" and is lying to you.

    2) the transaction bein note has been written o the transaction log in the database. promon shows the transaction as "Active".

    of the three, the only one that matters is the last.

  • show us the data

  • > I had a user today sitting in an update field, doing  nothing, however, it had a start time and my bi is now over 5GB.

    If you have AI files then you can find all details about the user's transaction.

    > sitting in an update field, doing  nothing

    Example:

    CREATE Customer.

    UPDATE Customer.Name.

    This code create an active transaction but only because of the CREATE trigger defined for the "Customer" table. The trigger hiddenly updates the "cust-num" field.

  • yes AI is running, I know what the user was doing, I double vision into the user screen -  the user login at 6:12 AM and sat in a update field, and at 2:20 PM, I F4 the user out of the screen and the BI stop growing.  Poor application, should have a timeout feature.  I monitor loooong running trans - the BI threshold is 12 GB and I have BISTALL.  It just wild that one user can cause this kind of BI growth.  

    Thanks!!!

  • > Poor application, should have a timeout feature.

    Many applications uses a service that disconnects the sessions with long open transactions. It's simple.

  • yeah, I was thinking about adding a script or ABL code to disconnect users running transaction longer then 90 minutes.  

  • I would recommend for a "transaction watchdog" to start couting the table's/index' updates/creates/deletes for /any/ sessions that have the active transactions - no matter how long are their transactions. So when a transaction will stay opened for a long time you will approximately know how large it is and does the session still activily update a database (in other words if the total number of UCD is still growing).

  • Good point.  I will do that.  Most of the time a user is just sitting in a line item in Order entry, or non-menu screen.

    Thanks for the suggestions!

  • > Most of the time a user is just sitting in a line item in Order entry, or non-menu screen.

    Even the batch processes that create the large transactions used be idle 90% of transaction time. At least it's what I saw in AI scans that the customers of ours sent me. Most likely they spend the time just for reading data.

  • A bit off-topic:

    > FIND FIRST _Connect WHERE _Connect._Connect-Usr = _Trans._Trans-Usrnum.

    The faster:

    FIND FIRST _Connect WHERE _Connect._Connect-Id = _Trans._Trans-Usrnum + 1.

    Original code will read all _Connect records until it finds a record you need.

    The code with _Connect-Id will read only one record.

    The similar tricks are especially important when you deal with _UserTableStat/_UserIndexStat.

    BTW, it would be a good idea to accumulate the tricks for reading VSTs in one document. I thought about making a presentation but the subject seems to be too small for it. A thread on Community will be lost in the time but it would allow to collect the ideas from the different authors.What do you think?