Transaction states for transactions can be reviewed through the
_trans VST or:
promon > R&D > 1. Status Displays ... > 4. Processes/Clients ... > 3. Active Transactions
When the 2GB file limit has been reached without largefiles enabled or exceeded with largefiles enabled, checking what transaction state a transaction is in can be useful when debugging issues involving excessive bi growth.
A long-running transaction is one in the
ACTIVE state - data are then being processed in the transaction which would be implicated in bi growth, but can the same be said for a transaction in the
BEGIN or
ALLOCATED state?
If a transaction remains in the BEGIN or ALLOCATED state, will it cause bi growth?A transaction in the BEGIN or ALLOCATED state is one for which:
- "A transaction table entry was allocated, and a start record is being logged". (Database Administration Guide and Reference).
- These transaction states mean precisely the same, depending on the Progress or OpenEdge branded product these are BEGIN and ALLOCATED respectively.
- The BEGIN or ALLOCATED state is the first state a transaction will enter.
- A transaction in the BEGIN or ALLOCATED state will not cause bi growth, since at the point in time the transaction enters the BEGIN or ALLOCATED state, no writes are made to the bi file.
- If the transaction never does anything, both the transaction BEGIN note and the transaction END note will be optimized away when the transaction ends.
The following example illustrates the behaviour:1. Create a test database
$ prodb test sports2000
3. Start the test database
$ proserve test
Check the size of the bi file:
$ ls -l test.b1
-rw-rw-rw- 1 user group 2228224 test.b1
4. Connect Client
session #1 and run the following code, do nothing. Do not space bar through the PAUSE.
$ mpro test -p transaction.p
/* transaction.p */
DO TRANSACTION:
FIND FIRST customer EXCLUSIVE.
PAUSE.
END.
PROMON (R&D > 1 > 4 > 3) shows that the transaction started under
session #1 is in the
BEGIN state.
Note that the
Transaction Start Time is not populated.
Status: Active Transactions
Usr-Name-----Type-Login-time-----Tx-start-time--Trans-id-Trans-State
--6-pluto----SELF------08:39--------------------------76-Begin
5. Connect Client
session #2 and run the following code to add Customer Table entries:
$ mpro test -p addrecord.p
/* addrecord.p */
DEFINE VARIABLE newnum AS INTEGER INIT 1 NO-UNDO.
DEFINE VARIABLE itrans AS INTEGER NO-UNDO.
DO itrans = 1 TO 1:
DO WHILE newnum <=1000:
CREATE customer.
ASSIGN Customer.Name = "breakbi" + string(newnum)
customer.comment = FILL('S',30000).
IF newnum MODULO 250 = 0 THEN DISPLAY newnum ETIME / 1000.
newnum = newnum + 1.
PROCESS EVENTS.
END.
PAUSE 10 NO-MESSAGE.
END.
PROMON (R&D > 1 > 4 > 3) shows that the transaction started under
session #1 is still in the
BEGIN state and
session #2 is in the
ACTIVE state:
Note that the
Transaction Start Time is populated for the transaction in the
ACTIVE state.
Usr-Name-----Type-Login-time-----Tx-start-time--Trans-id-Trans-State
--6-pluto----SELF- -08:39----------------------76-Begin
--7-donald---SELF- -08:40- -09:13----10275-Active
When the procedure finishes, wait at least 60 seconds (specifically, the value for -G for the database - by default this is 60 seconds in early Progress Versions but 0 in later OpenEdge versions).
Waiting ensures that the clusters used have aged and have been flushed (-Mf).
Check the size of the bi file:
$ ls -l test.b1
-rw-rw-rw- 1 user group 34209792 11:30 test.b1
7. Re-run client
session #2:
If the
BEGIN state tx of Client
session #1 was preventing bi cluster reuse, the bi would now immediately start to grow.
Since its transaction would be preventing reuse of the bi space from the first bi cluster onwards.
However, the bi doesn't grow, the clusters are being reused by
session #2 from those that were already added to the bi cluster chain during the previous run (usr 7)
Check the size of the bi file: Note it remains the same size.
$ ls -l test.b1
-rw-rw-rw- 1 user group 34209792 11:30 test.b1.
8. Start a third client
session #3 and run similar code to the second client session, but with an additional PAUSE to do nothing on, just leave it:
DEFINE VARIABLE newnum AS INTEGER INIT 1 NO-UNDO.
DEFINE VARIABLE itrans AS INTEGER NO-UNDO.
DO itrans = 1 TO 1:
DO WHILE newnum <=1000:
CREATE customer.
ASSIGN Customer.Name = "breakbi" + string(newnum)
customer.comment = FILL('S',30000).
IF newnum MODULO 250 = 0 THEN DISPLAY newnum ETIME / 1000.
newnum = newnum + 1.
PROCESS EVENTS.
PAUSE. /* additional PAUSE */
END.
PAUSE 10 NO-MESSAGE.
END.
PROMON (R&D > 1 > 4 > 3) now shows that there are two
ACTIVE state transactions, while the first is still in the
BEGIN state:
Usr-Name-----Type-Login-time-----Tx-start-time--Trans-id-Trans-State
--6-pluto----SELF- -08:39----------------------76-Begin
--7-donald---SELF- -08:40- -09:13----10275-Active
--8-minnie---SELF- -08:50- -09:30----10399-Active
The third client
session #3 (usr 8), while not doing anything after the first create, (PAUSE), is now
locking bi clusters.
If the second client
session #2 (usr 7) session continues to run, bi growth will result until the third client
session #3 either completes (actions the PAUSE) or terminates their session.
The first client
session #1 is still in
BEGIN state and has had no bearing on bi growth.
As an alternative to reviewing the transaction information in PROMON, the following
_trans VST code can be used and modified to fit requirements:
DEFINE VARIABLE iTimeInSeconds AS INTEGER NO-UNDO INITIAL 10.
FOR EACH _trans WHERE _Trans._Trans-State EQ "Active" AND
_Trans._Trans-Duration GT iTimeInSeconds NO-LOCK
BY _Trans._Trans-Duration DESCENDING:
FIND _Connect WHERE _Connect._Connect-Usr = _Trans._Trans-Usrnum.
DISP _Trans._Trans-state
_Trans._Trans-Usrnum
_Connect._Connect-Name
_Connect._Connect-PID FORMAT ">>>>>9" SKIP
STRING(TIME,"H H:MM:SS") COLON 1 LABEL "TIME NOW"
_Trans._Trans-Txtime FORMAT "X(40)"
_Trans._Trans-duration
.
END.
