Re: Serializability of Transactions and Automatic (Number) Generators

"Jonathan Leffler" <jleffler_at_earthlink.net> wrote in message news:HZdpd.3351$NU3.1913_at_newsread1.news.pas.earthlink.net...
> In general, a set of transactions is considered serializable if it is
> equivalent to some serial execution of the same transactions (for
> example, see Bernstein, Hadzilacos and Goodman "Concurrency Control
> and Recovery in Database Systems", section 1.3 "Serializability").
>
> Modern DBMS usually have one or more ways of automatically generating
> values during a transaction - Oracle (and most other DBMS) have
> SEQUENCE types; Informix has the SERIAL type; other DBMS have other
> similar constructs.
>
> In general, the values automatically generated during a transaction
> are not generated again, even if the original transaction is rolled
> back. Further, there is nothing to stop a pair of transactions from
> alternately generating new values - V(n) to TxA, V(n+1) to TxB, V(n+2)
> to TxA, V(n+3) to TxB, and so on.
>
> Does this mean that such transactions are inherently non-serializable,
> or does it mean that the specific values generated by a transaction
> are not material for the discussion of equivalence of executions?

Yes. Sequence numbers and the like bear no relevance to the serializability of transactions. These constructions are merely a convenient way of creating unique numbers. The fact that they are generated in order is more a function of programming and performance than for their use in the relational model or implementation. They are not intended to be used as true "serial' numbers, and, if in > 2 transactions, a number in the sequence is missing, it is not considered to be a problem. The only guarantee is that each number is unique (and even that depends on how the sequencing is defined and other constraints). Remember, we are dealing with sets, which by definition, are UNordered. An RDBMS should not be expected to natively provide a means to cause a "natural" ordering of the data (FIFO). ORDER BY orders by whatever column(s) is/are used in the ORDER BY clause. If you want to impose an order (of serialization) on the data, it needs to be done by some other means, such as a time stamp. Once this is done, you can manually serialize transactions, but, really, if, say, Oracle's multi-version consistency model is used, serailization is irrelevant, as the DBMS ensures that no serialization errors, if they should occur, go unnoticed.

>
> If TxA were executed before TxB, it would obtain the values V(n) and
> V(n+1) -- rather than V(n) and V(n+2) as shown above -- but for all
> practical purposes, the results are equivalent: two new unique values
> unused by any other transaction were generated and the specific values
> are immaterial.
>
> Any thoughts?
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
> Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Received on Thu Nov 25 2004 - 21:20:35 CET