Re: Is transaction ID unique for the life of a DB?

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 12 Feb 2003 12:09:18 -0800
Message-ID: <130ba93a.0302121209.66b98be4@posting.google.com>

I think you meant DBMS_TRANSACTION, not DBA_TRANSACTION. Due to dynamic nature of the v$ views, I doubt transaction ID will be unique for the "life" of a database. There is no reasons for ORACLE to keep track of "committed" transaction ID. My test showed ORACLE indeed recycles the sequence number, xidsqn. So for sure xidsqn will not be unique for the life time of a database. I would venture to say USN.SLOT.SQN will not be unique. For active transactions, yes. But not for committed transactions. Here is a simple test. As can be seen, there are many repeated sqn.

SQL> declare
  2 x varchar2(100);
  3 begin
  4 for i in 1..100 loop
  5 update test1 set sumc2=0;
  6 x:=dbms_transaction.local_transaction_id;   7 dbms_output.put_line(x);
  8 rollback;
  9 end loop;
 10 end;
 11 /

8.41.57484
9.37.57361
1.12.57503
2.34.57467
3.19.57431
4.43.57498
5.41.57450
7.42.57431
6.9.57078

10.32.57346

8.17.57485
9.25.57362
1.20.57503
2.15.57467
3.23.57431
4.38.57497
5.34.57451
7.18.57431
6.34.57078

10.38.57346

8.4.57485
9.20.57363
1.40.57502
2.7.57468
3.16.57431
4.18.57499
5.11.57452
7.39.57430
6.1.57079
10.8.57346
8.39.57484
9.27.57362
1.0.57505
2.17.57467
3.27.57430
4.10.57498
5.3.57451

7.15.57432
6.46.57077
10.12.57346

8.19.57483
9.2.57362
1.24.57503
2.45.57467
3.24.57431
4.28.57499
5.1.57452
7.1.57432
6.21.57077

10.14.57347

8.42.57485
9.38.57362
1.1.57504
2.40.57467
3.36.57430
4.32.57498
5.38.57451
7.23.57432
6.41.57078

10.2.57346

8.2.57484
9.39.57361
1.16.57504
2.26.57468
3.44.57431
4.14.57497
5.20.57450
7.44.57432
6.37.57077

10.47.57346

8.11.57484
9.22.57362
1.35.57503
2.10.57468
3.28.57432
4.22.57498
5.35.57450
7.20.57431
6.47.57077

10.19.57347

8.0.57486
9.14.57362
1.41.57504
2.11.57468
3.42.57430
4.36.57498
5.0.57452
7.21.57431
6.30.57077

10.30.57346

8.22.57485
9.34.57362
1.5.57504
2.35.57468
3.22.57432
4.27.57498
5.15.57450
7.30.57431
6.16.57077

10.17.57345

PL/SQL procedure successfully completed.

• Jusung Yang

"David" <bozo_at_bozo.com> wrote in message news:<hrycnRGA2reE9dejXTWcqw_at_comcast.com>...
> Hi All,
>
> First a statement of what I believe to be true and then the question.
>
> A transaction ID consists of the combination of the the USN (i.e.
> v$transaction.xidusn), slot (v$transaction.xidslot) and sequence number
> (v$transaction.xidsqn). I can use package DBA_TRANSACTION function
> LOCAL_TRANSACTION_ID to get a session's transaction ID (in dot delimited
> format), optionally having the function begin a transaction if one is not
> already started. From what I've read, it's my understanding that a
> transaction ID is unique.
>
> Now comes the question. Is a transaction ID in fact quaranteed to be unique
> for the entire life of a database? I suppose that the root of the question
> boils down to how the sequence number part of the transaction ID is arrived
> at, since obviously the usn and slot won't be unique. I've rooted around
> quite a number of resources and have not been able to find any specific (or
> even vague, for that matter) information regarding how the sequence number
> is derived.
>
> David
Received on Wed Feb 12 2003 - 14:09:18 CST