SELECT * FROM sa_transactions(); connection_num transaction_id start_time start_sequence_num end_sequence_num committed version_entries -------------- -------------- ----------------------- -------------------- -------------------- --------- --------------- 1 1461861072 2015-01-29 15:06:49.610 1393639117 (NULL) (NULL) 0 SELECT Number, Value FROM sa_conn_properties() WHERE PropName = 'TransactionStartTime'; Number Value ----------- ------------------------ 46 45 44 43 42 41 1 2015-01-29 15:06:49.610 What is "transaction_id" and where does it come from? What is a "transaction sequence number" and where does it come from? Is "start_time" the only useful column? It is the only column that appears in an example in the Help, and it appears to behave the same as CONNECTION_PROPERTY ( 'TransactionStartTime' ): When a transaction is committed, the row in sa_transactions() disappears at the same time TransactionStartTime is set back to empty. SELECT min( start_time ) FROM sa_transactions() min(sa_transactions.start_time) ------------------------------- 2015-01-29 15:06:49.610 SELECT MIN ( Value ) FROM sa_conn_properties() WHERE PropName = 'TransactionStartTime' AND COALESCE ( Value, '' ) <> ''; MIN(sa_conn_properties.Value) ----------------------------- 2015-01-29 15:06:49.610 Are "end_sequence_num" and "committed" ever non-NULL? The rows seem to disappear as soon as the transactions are committed. What is "version_entries" and where does it come from? |
If you think more in terms of snapshots some of this will be clearer; or at least easier to provide examples for. This stored procedure reports of both kinds of 'transactions' in the system. As you may have already surmised version_entries relates to the number of rows in the (snapshot) version store. In the same vein, snapshots can hang around after a commit (say one with a With-Hold cursor open on it) and it is in those cases the values "end_sequence_num" and "committed" can have non-null values. {see sa_snapshots( ) and other sections on snapshots for some other aspects about snapshots} There may be a chance that (with an extremely busy server fighting for resources) where information about normal transactions may not be cleaned up immediately (a pure guess on my part) but that should be temporary and transitory. I can honestly say I have never seen that myself. And "transaction_id" it's just a unique value maintained by the current server instance. You can think of it as the primary key of a system-specific virtual table. Though there is no guarantee that the value won't can get reused {much like a pid} Ditto for the sequence numbers (unique and progressive though they are) ... but may be of little informational value outside the tracking/mapping of snapshots. Help any? Is it possible for a row to persist in sa_transactions() where the corresponding CONNECTION_PROPERTY ( 'TransactionStartTime' ) has become empty?
(29 Jan '15, 17:00)
Breck Carter
|
I use it to generate my own Transaction ID that I can reference in long running procedures. Reason to use it is to write changes made by a procedure, functions and trigger to a audit table. In this audit table I can identify all records that have been created during a transaction. The SA Transaction ID changes when you commit.
GetTableKey is a Function to build a unique primary key in my project. I think that can by handy sometimes. If somebody knows a simpler solution let me know. |