Transaction states in promon

George,

0) two phase commit is /always/ enabled. you cannot turn if off completely.

there are two variants: the "cheesy" two-phase commit and the "robust" two phase commit. the cheesy version is used until you enable the robust version. The difference is that in the cheesy version the ready-to-commit (i.e. prepared) state is not persisted in the transaction log. in the robust vertion it is. Thus in the cheesy version, a transaction that is ready-to-commit will be rolled back if a crash occurs before the commit occurs.

1) the transitions from phase 1 to phase 2 and phase 2 to dead can occur under the same lock because releasing the transaction table entry happens right after commtting and just requires zeroing part of the entry.

> On Nov 14, 2015, at 2:44 PM, George Potemkin wrote:

>

> Update from Progress Community [https://community.progress.com/]

>

> George Potemkin [https://community.progress.com/members/georgep12]

>

> Transaction states are changing in the following order:

>

> Dead -> Begin -> Phase 2 -> Dead

> Dead -> Begin -> Active -> Phase 2 -> Dead

> where the "dead" state means that an entry in the transaction table is free (in other words, it's just the "zero" status).

>

> Note that each "->" means one TXT latch lock that controls an access to the transaction table.

> It's easy to check that the allocated transactions create 3 TXT latch locks while the active transactions create 4 TXT latch locks.

> "Begin" = "ALLOCATED" in _Trans-State

> "Phase 2" = "COMMITTING" in _Trans-State

>

> Sometimes promon shows the "Phase 1" ("PREPARED"?) state.

> For example:

> 04/18/14 Status: Active Transactions

> 17:20:34

>

> Usr Name Type Login time Tx start time Trans id Trans State

>

> 932 username SELF/ABL 04/17/14 22:53 04/18/14 17:20 2022781219 Phase 1

>

> where Tx start time is always non-empty and matches the current time (unfortunately the precision is a minute). Trans id is always close to the highest IDs for the current snapshot.

>

> So transaction state seems to be sometimes changing in the order:

> Dead -> Begin -> Active -> Phase 1 -> Phase 2 -> Dead

>

> Knowledgebase P178708:

> Why does promon show transactions in phase 1 and phase 2 when two phase commit is not enabled?

> knowledgebase.progress.com/.../P178708 [http://knowledgebase.progress.com/articles/Article/P178708]

>

> If a transaction spans two databases, you may see transactions in phase 1 or phase 2 in promon R&D options > Status > 4 > 3. This can occur even if the databases are not enabled for two phase commit.

>

>

> I run some tests. I did not expect to catch the "Phase 1" state in promon. I just tried to reproduce 5 TXT latch locks per transaction. But when a transaction spans two databases it still locks the transaction table only four times.

>

> Though I was able to get 5 TXT latch locks in one database:

>

> CREATE tbl.

> ASSIGN tbl.i1 = 1. /* primary index in SAT1 */

> MESSAGE "i1" TranState() VIEW-AS ALERT-BOX INFO BUTTONS OK.

>

> ASSIGN tbl.i2 = 2. /* unique index in SAT2 */

> MESSAGE "i2" TranState() VIEW-AS ALERT-BOX INFO BUTTONS OK.

>

> The extra TXT lock is created by the second ASSIGN statement .

>

> But if I would move any of these indexes into another area then the transaction will again create only 4 TXT latch locks. In both cases the second ASSIGN statement changes nothing in transaction state. Why it locks the transaction table? And why the behaviour depends from the area type?

>

> But main question:

> When a transaction spans two databases and the transaction states are indeed changing in the order:

> Dead -> Begin -> Active -> Phase 1 -> Phase 2 -> Dead

> why it locks the transaction table only 4 times?

>

> Regards,

> George

>

> View online [https://community.progress.com/community_groups/openedge_rdbms/f/18/t/21398]

>

> You received this notification because you subscribed to the forum. To stop receiving updates from only this thread, go here [https://community.progress.com/community_groups/openedge_rdbms/f/18/t/21398/mute].

>

> Flag [https://community.progress.com/community_groups/openedge_rdbms/f/18/t/21398?AbuseContentId=0cbec276-6353-441a-bedc-384a2a58011e&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true] this post as spam/abuse.

Can I get any useful information from the fact that promon is catching the "phase 1" states of the transactions?

The recent case:

Session had died with bus error. It seemed to leave the corrupted block in the buffer pool (really a correct buffer was placed in the wrong offset). Db was crushed:

(4232)  Corrupt block detected when attempting to release a buffer.

There are the strange facts about the same process reported a few hours before the crush:

Its transactions had the "phase 1" state:

Time       Usr Name     Type       Login time     Tx start time  Trans id Trans State

10:41:29  1208 SERV     SELF/ABL  11/13/15 00:42 11/13/15 10:41 934005473  Phase 1            
11:21:30  1208 SERV     SELF/ABL  11/13/15 00:42 11/13/15 11:21 934137829  Phase 1            

The transactions did span two databases.

It had created the protrace file with 586 (!) traces for the different times (from to 08:52:31 to 11:21:38) but there are no errors in db log.

The last transaction of the session was a /bit/ corrupted: instead RL_TEND note the process had generated the second RL_TBGN for the same TRID:

Trid: 934142319 Fri Nov 13 11:21:37 2015. (2598)
Trid: 934142319 User Id: SERV (12531)
Trid: 934142319 code = RL_TBGN version = 1 (12528)
[snip]
Trid: 934142319 Fri Nov 13 11:21:37 2015. (2598)
Trid: 934142319 User Id: SERV (12531)
Trid: 934142319 code = RL_TBGN version = 1 (12528)

Bus error is almost the same as memory violation. But the process could not work a few hours with the corrupted data in memory.