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

    Unanswered: SEQUENCE in 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.

    Thoughts?

  2. #2
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    No, the rollback will not revert the sequence value to previous one. You insertion in table will got undone instead. So, the next insertion in table for the same sequence will generate the NEXTVALUE and Earlier value will be lost.

    Sequence values will also lost if db2 server is restarted / crashed and provide some sequence values are cached eg. CACHE 10
    ssumit

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Quote Originally Posted by Celko View Post
    ... 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.

    Thoughts?

    The produced values from a sequence are always ordered in the same direction (up or down, relative to the increment), although there may be gaps.

    In your scenario, other subsequently consumed-values from that sequence might be for transactions that committed before your rollback happened. If a consumed-value could be 'un-consumed' and made newly available then there would be a possibility to produce 'out of order' values - rendering the object no longer a sequence. That's my understanding of why consumption is independent of commit/rollback.
    Last edited by db2mor; 10-17-14 at 09:02.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    The reason I asked is that I was reading an ANSI document and was unsure about the transaction model. It seemed that it implied a SEQUENCE in a transaction would also ROLLBACK with the table(s), so that the whole schema would be restored to the initial state. Easy enough with a lock on a SEQUENCE, just like a table on SERIALIZABLE isolation. But I never found any product doing this, so I wanted a sanity check.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wait... YOU wanted a sanity check? I can do that, but probably not the answer that you wanted!

    -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
  •