Re: Transaction Time in transactional DBMS?

On Tue, 16 Oct 2001 11:07:22 +0200, Christian.D.Schnell_at_t-online.de wrote:

[snip]
>With transactions, the Transaction Time interval of a database fact
>has two different beginnings, one for the writer (when he writes the
>fact) and one for all other readers (when the writer commits his
>transaction). Which one is it the right one?

AFAIK, it's the equivalent of SQL3's TRANSACTIONTIME reserved word. I think TRANSACTIONTIME is implementation-dependent.

[snippage throughout]
>That makes me think, that the only valid representation of transaction
>time instant in a transactional database management system is a tuple
>of (Transaction Time, Valid Time). Is this correct?

I don't think so, but I'm not sure I understand your question.

"Valid Time" has to do with the state of the real world; "Transaction Time" has to do with the state of the database. You can build tables that record the state of the real world without recording the state of the database, and you can build tables that record the state of the database without recording the state of the real world. (Without recording the state of the real world in a "Valid Time" state table, that is.)

If I understand your question correctly . . .

The meaning of "accurate representation of a transaction time instant" depends on what kind of state tables you're working with. (Please, let's be careful tossing about the term "valid". It's too easy to confuse me.) If you're working with a bitemporal table, the "accurate representation of a transaction time instant" consists of all the rows

 WHERE (TT_START <= 'The instant') AND ('The instant' < TT_STOP)

>Another question: What instant to choose for a Transaction Time value?
>In my understanding, it should be the time of commit, because that is
>the beginning of the time when the fact was available for /every/
>reader of the database.

Snodgrass's examples use the CURRENT_TIMESTAMP scalar function.

>A final question: Does the transaction time have it's name because it
>is required to be constant in each transaction? Is that actually
>required? The glossary isn't really clear about that, I think.

To me, it makes sense for a "Transaction Time" timestamp to be the same for all the parts of a SQL transaction. It doesn't make sense for "Transaction Time"--the time a fact becomes known to the database--to vary among the rows of a single SQL transaction.

-- 
Mike Sherrill
Information Management Systems