Will the transaction id be unique even in a RAC environent
Manish Limaye, December 24, 2004 - 3:39 pm UTC
Thank you very much Tom. I attended Oracle OpenWorld earlier this month and your session was one of the best.
I have a followup query. It is my understanding that in a RAC environment, each instance has its own redo logs and archived logs. Will the transaction id obtained from DBMS_TRANSACTION.LOCAL_TRANSACTION_ID be unique to the instance or database? Our RAC environment is not ready yet and hence I can not test this. Here is what the manual says:"This function returns the local (to instance) unique identifier for the current transaction. It returns null if there is no current transaction".
If the id is unique only to the instance, Log miner may find 2 different transactions with same id. If this is true, we will have to store the instance id also.
Please comment.
Thanks.
December 24, 2004 - 4:01 pm UTC
XIDUSN Undo segment number
XIDSLOT Slot number
XIDSQN Sequence number
The XIDUSN, XIDSLOT, and XIDSQN identify the rollback segment, rollback segment slot, and rollback segment sequence (or wrap).
they are not "redo based". the RBS's are totally shared between the two instances. I'll file a doc bug.
However, I'd collect as much as I could or possibly thought I might want at some point, instance, the systimestamp for example would be two obvious ones.
RTM
Duke Ganote, December 24, 2004 - 3:46 pm UTC
Cool, even though I've been using DBMS_TRANSACTION.LOCAL_TRANSACTION_ID for several months, I'd overlooked that it has the optional create_transaction parameter, so you can create a transID without doing any some pseudo-DML:
SQL> set serveroutput on;
SQL> exec dbms_output.put_line(dbms_transaction.local_transaction_id);
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line(dbms_transaction.local_transaction_id(TRUE));
7.12.15
PL/SQL procedure successfully completed.
In production, I capture the transID from within a trigger and pass it and the old/new info to a package. Being able to generate the transID directly will help simplify some test case setups. Thanks!
unique transaction id really unique?
A reader, March 11, 2005 - 5:54 pm UTC
Hhm, interesting stuff ...
Just to clear it up for me:
1) Is the transaction id unique over time (never repeats)?
The "XIDSQN (Sequence number)" part seems to suggest that.
2) What would be the maximum length required for a
varchar(2) field to store that id?
Thanks Tom,
you are really the best!
Kind regards,
Steve
March 12, 2005 - 10:05 am UTC
1) the transaction id should be unique, the sequence is part of the undo segment/slot. I suppose over a really really really long time -- it could repeat (depends on whether they wrap it at 2/4 billion or not)
2) 48*3+2 for nnnnnnnnn.nnnnnnnnn.nnnnnnnnnnn would be "safe"
Why 48?
A reader, March 13, 2005 - 6:32 am UTC
Thanks a lot, Tom. That cleared it up for me.
But why 48 * 3? "Desc v$transaction" shows that
XIDUSN, XIDSLT, XIDSQN are numbers, so I would
have expected 38 * 3. Anything wrong with my reasoning
here?
Kind regards,
Steve
March 13, 2005 - 9:57 am UTC
the biggest format for a number is something like 44 characters (sign, decimal, whatever).
48 is safe.
How about DDL-statement?
Petri Rautakoski, April 26, 2005 - 2:56 am UTC
I just found out that DDL-statement does implicit commit and transaction id is changed that way. So how can I get unique transaction id that will be unchanged in DDL-statements? I also tested this:
CREATE TABLE test1 (test INTEGER);
INSERT INTO test1 VALUES (1);
CREATE TABLE test2 (test INTEGER);
ROLLBACK;
SELECT * FROM test1;
+------+
| TEST |
+------+
| 1 |
+------+
1 row in set (0.003 secs)
So rollback won't work if DDL-statement executed during transaction. Is this what's needed?
April 26, 2005 - 7:26 am UTC
ddl is processed like this:
begin
COMMIT;
begin
do the ddl;
COMMIT;
exception
when others then rollback;
RAISE
end;
end;
/
a commit always happens first, THEN the ddl, then a commit or rollback of the DDL.
What are you trying to do? What is the unique transaction ID for? (there are a couple of transactions in there with DDL).
How about DDL-statement? (continue)
A reader, April 26, 2005 - 7:50 am UTC
I'm tracking things (like tables created) with LOCAL_TRANSACTION_ID so that when this transaction id doesn't exist in sys.v$_transactions table I can be sure that owner of table is not valid anymore. But this DDL-part seems to make my design worthless.
April 26, 2005 - 8:08 am UTC
Can you elaborate more on what your goal is, I did not understand what you meant by this:
...I'm tracking things (like tables created) with LOCAL_TRANSACTION_ID so that when this transaction id doesn't exist in sys.v$_transactions table I can be sure
that owner of table is not valid anymore.....
with DDL, the transaction id comes and goes so far, it would be rare to even see it. Not sure what you mean by "that owner of the table is not valid anymore..."
What are you doing?
How about DDL-statement? (continue)
A reader, April 26, 2005 - 8:18 am UTC
I add creator of the table into log-table. Creator is here LOCAL_TRANSACTION_ID and named as "owner of table".
In my application tables are created and dropped all the time and I have to somehow track when owner has "died" and not dropped tables in normal way. So I select all valid transactions from sys.v$_transactions-table ang if I find not valid transactions from log-table I can drop tables related to that transaction id.
April 26, 2005 - 8:32 am UTC
I'm very worried about your application then.
Doing DDL like that is way up in the top five of "things NOT to do". Way up there.
Why are you compelled to create tables like that? Creating and dropping tables frequently is a *really* bad idea, what can we do to help you change this approach before you get too far into it.
How about DDL-statement? (continue)
A reader, April 26, 2005 - 8:49 am UTC
Thanks for your advice. ;o)
We have so large amount of data that it's impossible to process in memory. So, the logical place to do processing is database. These tables are totally temporary and dropped after process has ended. This data is also dynamic so it can't be handled in global temporary table.
April 26, 2005 - 9:01 am UTC
sure it can be (handled in a gtt) if needed. give yourself a gtt with 50 varchar2 4000's, 50 numbers and 50 dates. there you go (for example).
But Oracle handles lots of data for you -- you are going to have to convince me that you need these gtt's even -- I've yet to see it actually happen.
I can assure you, going down the path of tons of create tables and tons of drop tables is going to be *a really bad idea*.
I have ideas about how you could do what you ask (clean up tables left by sessions no longer in the database) via naming conventions, but I hesitate to even bring it up because it is a recipe for disaster here.
How about DDL-statement? (continue)
A reader, April 26, 2005 - 10:20 am UTC
I know it's a bad idea, but we couldn't solve it otherwise. We cannot use a gtt because sometimes we may need 1000 of varchar columns and sometimes 100 varchars and 100 numbers (for example). The number of rows is usually less than 10 millions, but can be up to 100 millions / file.
We tried to store columns as rows in another table, but the performance was not very good and implementing functions on that format was too difficult.
What bad can we expect when creating and dropping lots of temporary tables?
Thanks.
April 26, 2005 - 10:32 am UTC
doing dll is a serial thing.
you'll bash the heck out of the dictionary.
you won't be able to use stored procedures for anything, unless you use dynamic sql.
you'll hard parse like mad as not only is the sql unique, the objects are.
I cannot think of a good thing to say.
use 1000 varchar2's then if you must.
Describe your processing that requires such an implementation for us and why it cannot be done in an existing set of tables.
how serial is the serial
Michael Norbert, April 26, 2005 - 2:37 pm UTC
How serial is ddl? Until all temporary extents are allocated, converted to permanent? Does LMT make a difference?
April 26, 2005 - 2:50 pm UTC
it won't be for the entire duration of the statement (eg: the population of the table)
doing ddl in a live system alot is a recipe for disaster. it is not something I would ever recommend
How about DDL-statement? (continue)
A reader, April 28, 2005 - 6:05 am UTC
The role of our database is not very conventional, its not for storing but for processing data.
It has two funtions:
1. its serves as a virtual memory for some of our 'tools'. Instead of keeping the file in memory it is dumped into the database for processing -> no more out of memory errors.
2. it provides simple operations like sorting, grouping, removing duplicates, etc.
We have just few concurrent users (tools), transactions may take several minutes to complete. So, serial ddl is not a problem for us.
Not able to use stored procedures... well, we have implemented the tools by generating SQL in java and it works for us quite well.
Doing lots of hard parsing... If most of SQL executes long time, doing hard parse every time doesn't seem to be a big deal to me.
I really cannot explain here all the processing we do. But, we have a file format and code that maps that to the simplest possible database structure. This way we have been able to implement some really difficult tools. Doing the same with set of existing table means different table structure... much more difficult.
Could you please explain what you meant with "you'll bash the heck out of the dictionary"?
Thanks
April 28, 2005 - 8:24 am UTC
don't know how to put "bash the heck out of the data dictionary" differently. you will be doing tons of drops and creates.
(oh the damage to the shared pool too -- you should expect unconventional issues to hit you over time as you are doing very unconventional things here).
You might find if you actually designed your DATA oriented application to *use* the database -- your queries wouldn't run for long times. But anyway.
So, use a naming convention -- have each session append _<NNN> to the end of their object names, where _NNN is their AUDSID (auditing sid)
ops$tkyte@ORA9IR2> select audsid from v$session where sid = ( select sid from v$mystat where rownum=1);
AUDSID
----------
4938
cleanup process looks for objects named with audsids that don't exist anymore.
RAC and application transaction ids derived from sequences?
A reader, June 27, 2005 - 2:47 pm UTC
Tom,
We run Oracle applications on a 4 node RAC environment. One of the modules uses a db sequence with cache size of 20, to generate its unique ids. There is another module (both vanilla Oracle) that relies on the first app's record ids to be in serial order (by creation date of records). There are obviously times when that order gets whacked! The second app then crashes because it sees the end before the start!
So we're now discussing possible bug fixes with support/dev. Would the txn id from dbms_transaction, combined with systimestamp ensure this serial order?
Thanks!
June 27, 2005 - 5:16 pm UTC
that application is broken on a single instance of Oracle.
There is no assurance that the sequences will be in order by creation date of records on a single instance.
What are they trying to order? based on transaction commit time? the timestamp associated with the record? or what?
It seems that only their BUSINESS logic can answer this question.
order ?
lh, June 28, 2005 - 3:34 am UTC
Has this sequence been created with order - attribute ?
Order attribute is very useful in rac environments.
June 28, 2005 - 7:51 am UTC
order is a performace killer in RAC. This application has a bug that exists in single instance as well as RAC.
transaction id
sam, January 31, 2011 - 5:37 pm UTC
Tom:
If I want to capture audit data for DML and I have a trigger per table.
If one transaction update data in 4 tables and I want to capture the transacton id so I can know what the data updates for this transaction, Would you use the statement above to get the transaction id value in the trigger and store it in all of the 4 audit tables (one SHADOW audit table per production table)?
2) This does not sound a numeric number. What column size and type you would use for it?
3) Can i use a sequence number instead?
February 01, 2011 - 4:47 pm UTC
2) it is obviously not a numeric number:
ops$tkyte@ORA9IUTF> exec dbms_output.put_line(
dbms_transaction.local_transaction_id );
1.16.158
varchar2(30) should handle it.
3) you tell me - you are a programmer, if we can give you the local transaction id, could you use a sequence number to somehow relate to it?
(yes, you could, but it would require a lot of work on your part - your code would have to detect when the transaction id changed in a given session so you could know to use nextval instead of currval)
trnasaction id
sam, February 01, 2011 - 4:57 pm UTC
Tom:
I see what you are saying.
So you would capture this oracle generated string (TRANS_ID) in all the TABLE TRIGGERS to store the transaction id in all T_AUDIT tables?
Kind of strange they dont use a unique number for that.
February 01, 2011 - 6:31 pm UTC
... Kind of strange they dont use a unique number for that.
...
not really, that is the real transaction id - it contains information that can be used to see the undo segment associated with the transaction - which is important to US. It is our transaction id, it is really three bits of data and it is what we needed to identify where a transaction was.
The seq(wrap)?
jiang huang zheng, February 02, 2011 - 4:00 am UTC
Hi Tom,
you say:
The XIDUSN, XIDSLOT, and XIDSQN identify the rollback segment, rollback segment slot, and rollback
segment sequence (or wrap).
the rollback segment sequence(or wrap), if the sequence is 158, does it mean that the rollback segment just wraps 158 times? what does it mean?
Thanks,
February 02, 2011 - 7:59 am UTC
Just consider it a number used to make the entire key unique. The three components together form a unique key - since xidusn and xidslot are not unique - they needed a bit more to do so. Enter the xidsqn, every time a slot is reused, the sequence goes up.
Thanks and I got it!
jiang huang zheng, February 02, 2011 - 10:17 pm UTC
A reader, September 09, 2023 - 8:19 pm UTC
Hello,
What is the best way to get local current transaction id in XID format as it is in version query pseudocolumn: VERSIONS_XID ?
By Oracle documentation "VERSIONS_XID: Identifier (a RAW number) of the transaction that created the row version."
I need to use it in DML trigger to record some audit information about current transaction. So later on I will be able to join this info by XID to the VERSIONS_XID pseudocolumn of Oracle Flashback Version Query.
Thank you.
September 13, 2023 - 3:50 am UTC
It will vary by platform (because of little/big endian format) but you can glean it from dbms_transaction, eg
SQL> delete from emp;
14 rows deleted.
SQL> select dbms_transaction.local_transaction_id from dual;
LOCAL_TRANSACTION_ID
-------------------------------------------------------------
5.17.6128
SQL> select xid from v$transaction;
XID
----------------
05001100F0170000
SQL> select
2 to_char(5,'0X'),
3 to_char(17,'0X'),
4 to_char(6128,'0000000X')
5 from dual;
TO_ TO_ TO_CHAR(6
--- --- ---------
05 11 000017F0