Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Unanswered: SEQUENCE in a ROLLBACK

    I am not sure about the ANSI/ISO rules on this; anyone know? We have a SEQUENCE and insert it into a table.

    CREATE SEQUENCE Cheque_Seq
    AS INTEGER
    START WITH 1
    INCREMENT BY 1;

    CREATE TABLE Cheque_Book
    (cheque_seq INTEGER*NOT NULL PRIMARY KEY,
    cheque_amt DECIMAL (15,2) DEFAULT 0.00 NOT NULL);

    If I do an insertion with the SEQUENCE in a transaction and then ROLLBACK the transaction, what happens to the sequence value? My thought is that the value should reset to the state it was in before the transaction (basic definition of a ROLLBACK). But it does not in the products I have looked at.
    trac

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    No, the value is lost.

    That's one of the reasons why sequences are much faster than any other way of generating unique numbers.
    This is true for Oracle, Postgres, DB2 and SQL Server.

    Quote Originally Posted by The Manual
    When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back
    Taken from: http://docs.oracle.com/cd/E11882_01/...htm#SQLRF01314
    Last edited by shammat; 10-16-14 at 19:06.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Joe:

    Following your logic of the behavior of a ROLLBACK on a SEQUENCE object, should the other transactions which have used later values in the SEQUENCE be renumbered by the ROLLBACK? I'm pretty sure that would be a disaster!

    As far as I'm concerned, a SEQUENCE is a programming artifact. It is a pure SK (Surrogate Key) and its value should not be affected by a ROLLBACK operation any more than an NK (Natural Key) would be altered by the ROLLBACK.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •