Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: choice of primary key to avoid constraint violation within a transaction

    We have a table with columns (ID, START_DATE, END_DATE) where active records are those where END_DATE='12/31/9999' to keep audit history of each record or ID.

    ID, START_DATE, END_DATE
    1 01/01/2012 01/02/2012 <-- expired
    1 01/02/2012 12/31/9999 <-- active
    2 01/01/2012 12/31/9999 <-- active

    Approach 1: primary key (ID, END_DATE) because only one record ID should exist for END_DATE=12/31/9999 or one active transaction can exist for a given ID. However on 01/03/2012, lets say we expire and insert record ID=2 as
    Code:
    UPDATE TABLE
    SET END_DATE=01/03/2012 
    WHERE ID=2 AND START_DATE=01/01/2012
    AND
    Code:
    INSERT a record with ID=2, START_DATE=01/03/2012, END_DATE=12/31/9999
    WITHIN SAME TRANSACTION, it fails due to constraint violation?

    Approach 2:
    An alternative to this is expire FIRST,
    SET END_DATE=01/03/2012
    WHERE ID=2 AND START_DATE=01/01/2012 [/CODE]
    AND THEN
    Code:
    INSERT a record with ID=2, START_DATE=01/03/2012, END_DATE=12/31/9999
    IN SEPARATE transactions.

    Problem with Approach 2 is that if the second insert transaction fails, table is in an inconsistent state until we fix the data.

    Approach 3: Solution to above inconsistent state is choosing a PRIMARY KEY (ID, START_DATE, END_DATE) so I can expire and insert ID=2 in same TRANSACTION and end up with
    ID, START_DATE, END_DATE
    1 01/01/2012 01/02/2012 <-- expired
    1 01/02/2012 12/31/9999 <-- active
    2 01/01/2012 01/03/2012 <-- expired
    2 01/03/2012 12/31/9999 <-- active

    Problem here is that you could also accidentally end up with incorrect data:
    ID, START_DATE, END_DATE
    1 01/01/2012 01/02/2012 <-- expired
    1 01/02/2012 12/31/9999 <-- active
    2 01/01/2012 01/03/2012 <-- expired
    2 01/03/2012 12/31/9999 <-- active
    2 01/04/2012 12/31/9999 <-- active

    where you have two active records with ID=2 for START_DATE=01/03/2012, 01/04/2012 satisfied by this primary key.

    As you can see both approaches have different problems. I always thought Approach 1 is correct i.e., expire first and insert second in the order WITHIN same DB2 transaction should also work as if they run in separate transaction. Why isn't this working this way?
    Last edited by chikkubhai; 07-04-12 at 18:07. Reason: syntax

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by chikkubhai View Post
    We have a table with columns (ID, START_DATE, END_DATE) where active records are those where END_DATE='12/31/9999' to keep audit history of each record or ID.

    ID, START_DATE, END_DATE
    1 01/01/2012 01/02/2012 <-- expired
    1 01/02/2012 12/31/9999 <-- active
    2 01/01/2012 12/31/9999 <-- active

    Approach 1: primary key (ID, END_DATE) because only one record ID should exist for END_DATE=12/31/9999 or one active transaction can exist for a given ID. However on 01/03/2012, lets say we expire and insert record ID=2 as
    Code:
    UPDATE TABLE
    SET END_DATE=01/03/2012 
    WHERE ID=2 AND START_DATE=01/01/2012
    AND
    Code:
    INSERT a record with ID=2, START_DATE=01/03/2012, END_DATE=12/31/9999
    WITHIN SAME TRANSACTION, it fails due to constraint violation?
    [...]
    This should not fail due to primary key violation, can you post ddl. statements and error messages? Here's an example that works for me:

    Code:
    ]$ db2 "create table t (id int not null, start_date date not null, end_date date not null, primary key (id, end_date))"
    ]$ db2 "insert into t (id, start_date, end_date) values (2, '01/01/2012', '12/31/9999')"
    
    ]$ db2 +c "UPDATE t SET END_DATE='01/03/2012' WHERE ID=2 AND START_DATE='01/01/2012'"
    ]$ db2 +c "insert into t (id, start_date, end_date) values (2, '01/03/2012', '12/31/9999')"
    ]$ db2 commit
    --
    Lennart

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why didn't you use (ID, START_DATE) as primary key?
    So, it would be not necessary to update primary key.

  4. #4
    Join Date
    Jul 2012
    Posts
    6
    Quote Originally Posted by lelle12 View Post
    This should not fail due to primary key violation, can you post ddl. statements and error messages? Here's an example that works for me:

    Code:
    ]$ db2 "create table t (id int not null, start_date date not null, end_date date not null, primary key (id, end_date))"
    ]$ db2 "insert into t (id, start_date, end_date) values (2, '01/01/2012', '12/31/9999')"
    
    ]$ db2 +c "UPDATE t SET END_DATE='01/03/2012' WHERE ID=2 AND START_DATE='01/01/2012'"
    ]$ db2 +c "insert into t (id, start_date, end_date) values (2, '01/03/2012', '12/31/9999')"
    ]$ db2 commit
    Correct, I tried this in Aqua Data Studio one by one with auto commit off and it works just fine, so is this a spring jdbcTemplate issue?

    Here is how we do it:
    Code:
    @Transactional
    jdbcTemplate.execute(new HibernateCallBack(){
        public void doInHIbernate(Session session) throws Exception {
            session.update(updateObject); // update EXP_DATE to '01/03/2012'
            session.insert(insertObject); // inserts new ID=2 with EXP_DATE='12/31/9999'
        }
    )};
    May be Spring jdbcTemplate is not ordering them within the same transaction??
    Last edited by chikkubhai; 07-05-12 at 01:23.

  5. #5
    Join Date
    Jul 2012
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    Why didn't you use (ID, START_DATE) as primary key?
    So, it would be not necessary to update primary key.
    Cannot because I can then have

    1 01/01/2012 12/31/9999
    1 01/02/2012 12/31/9999

    where as only one record should exist for 12/31/9999.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In order to make sure there are no overlapping date ranges in a table like this, triggers can be used to reject any inserts or updates that are not allowed. You could also use a trigger to fill in an gaps in date ranges based on updates.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    If he use use (ID, START_DATE) as primary key
    The table could be end up with incorrect data:

    ID, START_DATE, END_DATE
    2 01/03/2012 12/31/9999 <-- active
    2 01/04/2012 12/31/9999 <-- active


    I agree with Lennart,
    Do you do some test for your Approach 1?
    It will not fail due to constraint violation within a same transaction,
    because within a same transaction, the result of the first update is already visible to the following insert.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If (ID, START_DATE) was used as primary key,
    current(active) data would be
    current_date BETWEEN start_date AND end_date - 1 DAY
    or
    end_date = '12/31/9999'

    And Approach 1 would work.

    ID, START_DATE, END_DATE
    1 01/01/2012 01/02/2012 <-- expired
    1 01/02/2012 12/31/9999 <-- active
    2 01/01/2012 12/31/9999 <-- active

    Approach 1: ...
    on 01/03/2012, lets say we expire and insert record ID=2 as

    Code:
    UPDATE OUR_TABLE
       SET END_DATE = '01/03/2012'
     WHERE ID = 2
       AND END_DATE = '12/31/9999'
    AND
    Code:
    INSERT INTO OUR_TABLE (ID , START_DATE , END_DATE)
    VALUES (2 , '01/03/2012' , '12/31/9999')

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Tested on DB2 9.7.5 on Windows/XP.

    Create test table:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE our_table
    ( id         int  not null
    , start_date date not null
    , end_date   date not null
    , primary key (id , start_date)
    );
    
    
    INSERT INTO our_table
    (id , start_date , end_date)
    VALUES
      ( 1 , '01/01/2012' , '01/02/2012' )
    , ( 1 , '01/02/2012' , '12/31/9999' )
    , ( 2 , '01/01/2012' , '12/31/9999' )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    Update and Insert:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     update_valid_to_invalid AS (
    SELECT id , end_date
     FROM  FINAL TABLE
          (UPDATE our_table
              SET end_date = '01/03/2012'
            WHERE ID       = 2
              AND end_date = '12/31/9999'
          )
    )
    SELECT *
     FROM  FINAL TABLE
          (INSERT INTO our_table
                 (id , start_date , end_date)
           SELECT id , end_date   , '12/31/9999'
            FROM  update_valid_to_invalid
          )
    ;
    ------------------------------------------------------------------------------
    
    ID          START_DATE END_DATE  
    ----------- ---------- ----------
              2 2012-01-03 9999-12-31
    
      1 record(s) selected.
    Result:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM  our_table;
    ------------------------------------------------------------------------------
    
    ID          START_DATE END_DATE  
    ----------- ---------- ----------
              1 2012-01-01 2012-01-02
              1 2012-01-02 9999-12-31
              2 2012-01-01 2012-01-03
              2 2012-01-03 9999-12-31
    
      4 record(s) selected.

  10. #10
    Join Date
    Jul 2012
    Posts
    6
    Quote Originally Posted by fengsun2 View Post
    If he use use (ID, START_DATE) as primary key
    The table could be end up with incorrect data:

    ID, START_DATE, END_DATE
    2 01/03/2012 12/31/9999 <-- active
    2 01/04/2012 12/31/9999 <-- active


    I agree with Lennart,
    Do you do some test for your Approach 1?
    It will not fail due to constraint violation within a same transaction,
    because within a same transaction, the result of the first update is already visible to the following insert.
    I expected same, see post #4

  11. #11
    Join Date
    Jul 2012
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    Tested on DB2 9.7.5 on Windows/XP.

    Create test table:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE our_table
    ( id         int  not null
    , start_date date not null
    , end_date   date not null
    , primary key (id , start_date)
    );
    
    
    INSERT INTO our_table
    (id , start_date , end_date)
    VALUES
      ( 1 , '01/01/2012' , '01/02/2012' )
    , ( 1 , '01/02/2012' , '12/31/9999' )
    , ( 2 , '01/01/2012' , '12/31/9999' )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    Update and Insert:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     update_valid_to_invalid AS (
    SELECT id , end_date
     FROM  FINAL TABLE
          (UPDATE our_table
              SET end_date = '01/03/2012'
            WHERE ID       = 2
              AND end_date = '12/31/9999'
          )
    )
    SELECT *
     FROM  FINAL TABLE
          (INSERT INTO our_table
                 (id , start_date , end_date)
           SELECT id , end_date   , '12/31/9999'
            FROM  update_valid_to_invalid
          )
    ;
    ------------------------------------------------------------------------------
    
    ID          START_DATE END_DATE  
    ----------- ---------- ----------
              2 2012-01-03 9999-12-31
    
      1 record(s) selected.
    Result:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM  our_table;
    ------------------------------------------------------------------------------
    
    ID          START_DATE END_DATE  
    ----------- ---------- ----------
              1 2012-01-01 2012-01-02
              1 2012-01-02 9999-12-31
              2 2012-01-01 2012-01-03
              2 2012-01-03 9999-12-31
    
      4 record(s) selected.
    I re tested again with just one record, in my case another column ID_1 is identity column and ID is the assigned key. ID_1 was used as primary key of the entity in my hibernate mapping as EXP_DATE can change and cannot be used as part of key in entity, so update and insert happen on specific record using ID_1, let me investigate and see Spring jdbcTemplate or Hibernate are ordering the statements as mentioned in #4
    Last edited by chikkubhai; 07-05-12 at 12:05. Reason: incorrect

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... in my case ID is an identity column ...
    Do you mean "GENERATED ALWAYS AS IDENTIY"?
    If so, why duplicated ID(in your sample) possible?


    By the way,
    My example is doing UPDATE and INSERT in one statement with this order.
    So, no need to consider execution sequence of statemens, nor boundary of transactions.
    And, primary key was (id , start_date), then violation of primary key constraint wouldn't happen.

  13. #13
    Join Date
    Jul 2012
    Posts
    6
    Sorry about the confusion about identity column, updated comments. I am investigating how spring jdbcTemplate or hibernate orders these.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I am investigating how spring jdbcTemplate or hibernate orders these.
    If DB2 provided usefull functionality for your requirements, why not use it directly?
    Why stic to poor(?) capability tools?

    See here by replacing primary key with UNIQUE.
    Quote Originally Posted by tonkuma View Post
    ...
    By the way,
    My example is doing UPDATE and INSERT in one statement with this order.
    So, no need to consider execution sequence of statemens, nor boundary of transactions.
    And, primary key was (id , start_date), then violation of primary key constraint wouldn't happen.

    Anyway,
    Here is another test.
    id_1 is PRIMARY KEY and DENTITY.
    (id , start_date) is UNIQUE.

    CREATE test table and populate it:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_table
    ( id_1       INT GENERATED ALWAYS AS IDENTITY
    , id         int  not null
    , start_date date not null
    , end_date   date not null
    , PRIMARY KEY (id_1)
    , UNIQUE (id , start_date)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_table
    (id , start_date , end_date)
    VALUES
      ( 1 , '01/01/2012' , '01/02/2012' )
    , ( 1 , '01/02/2012' , '12/31/9999' )
    , ( 2 , '01/01/2012' , '12/31/9999' )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_table;
    ------------------------------------------------------------------------------
    
    ID_1        ID          START_DATE END_DATE  
    ----------- ----------- ---------- ----------
              1           1 2012-01-01 2012-01-02
              2           1 2012-01-02 9999-12-31
              3           2 2012-01-01 9999-12-31
    
      3 record(s) selected.
    UPDATE and INSERT by one query: on 01/03/2012, expire and insert ID=2
    Note: I modified the query to handle exising ID and new ID by same query.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     new_valid_data
    (id , start_date , end_date) AS (
    VALUES (2 , '01/03/2012' , '12/31/9999')
    )
    , update_valid_to_invalid AS (
    SELECT id , end_date
     FROM  FINAL TABLE
          (UPDATE test_table
              SET end_date
                = (SELECT start_date
                    FROM  new_valid_data
                  )
            WHERE (id , end_date)
               IN (SELECT id , end_date
                    FROM  new_valid_data
                  )
          )
    )
    SELECT *
     FROM  FINAL TABLE
          (INSERT INTO test_table
                  (id , start_date , end_date)
           SELECT v.id , v.start_date , v.end_date
            FROM  (SELECT *
                    FROM  new_valid_data
                  ) AS v
            LEFT  OUTER JOIN
                  update_valid_to_invalid AS u
             ON   0=0
          )
    ;
    ------------------------------------------------------------------------------
    
    ID_1        ID          START_DATE END_DATE  
    ----------- ----------- ---------- ----------
              4           2 2012-01-03 9999-12-31
    
      1 record(s) selected.
    After execution of the query:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_table;
    ------------------------------------------------------------------------------
    
    ID_1        ID          START_DATE END_DATE  
    ----------- ----------- ---------- ----------
              1           1 2012-01-01 2012-01-02
              2           1 2012-01-02 9999-12-31
              3           2 2012-01-01 2012-01-03
              4           2 2012-01-03 9999-12-31
    
      4 record(s) selected.

    Query for New ID: on 01/02/2012 new ID=3 get active.
    Note: same query as previous except data (3 , '01/02/2012' , '12/31/9999')
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     new_valid_data
    (id , start_date , end_date) AS (
    VALUES (3 , '01/02/2012' , '12/31/9999')
    )
    , update_valid_to_invalid AS (
    SELECT id , end_date
     FROM  FINAL TABLE
          (UPDATE test_table
              SET end_date
                = (SELECT start_date
                    FROM  new_valid_data
                  )
            WHERE (id , end_date)
               IN (SELECT id , end_date
                    FROM  new_valid_data
                  )
          )
    )
    SELECT *
     FROM  FINAL TABLE
          (INSERT INTO test_table
                  (id , start_date , end_date)
           SELECT v.id , v.start_date , v.end_date
            FROM  (SELECT *
                    FROM  new_valid_data
                  ) AS v
            LEFT  OUTER JOIN
                  update_valid_to_invalid AS u
             ON   0=0
          )
    ;
    ------------------------------------------------------------------------------
    
    ID_1        ID          START_DATE END_DATE  
    ----------- ----------- ---------- ----------
              5           3 2012-01-02 9999-12-31
    
      1 record(s) selected.
    After execution of the query:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_table;
    ------------------------------------------------------------------------------
    
    ID_1        ID          START_DATE END_DATE  
    ----------- ----------- ---------- ----------
              1           1 2012-01-01 2012-01-02
              2           1 2012-01-02 9999-12-31
              3           2 2012-01-01 2012-01-03
              4           2 2012-01-03 9999-12-31
              5           3 2012-01-02 9999-12-31
    
      5 record(s) selected.
    Last edited by tonkuma; 07-06-12 at 09:52. Reason: Add a quote [QUOTE=tonkuma;6559416]...

  15. #15
    Join Date
    Nov 2011
    Posts
    334
    I don't know why you need to use the clause: "LEFT OUTER JOIN update_valid_to_invalid AS u ON 0=0" when insert into test_table。
    And maybe we can change it a little to process existing id and new id
    within one sql:
    Code:
    WITH
     new_valid_data
    (id , start_date , end_date) AS (
    VALUES (2 , '01/03/2012' , '12/31/9999'),
           (3 , '01/02/2012' , '12/31/9999')
    )
    , update_valid_to_invalid AS (
    SELECT id , end_date
     FROM  FINAL TABLE
          (UPDATE test_table t
              SET end_date
                = (SELECT start_date 
                    FROM  new_valid_data s
                    where t.id = s.id
                    and   t.end_date = s.end_date
                  )
            WHERE (id , end_date)
               IN (SELECT id , end_date
                    FROM  new_valid_data
                  )
          )
    )
    SELECT *
     FROM  FINAL TABLE
          (INSERT INTO test_table
                  (id , start_date , end_date)
           SELECT v.id , v.start_date , v.end_date
            FROM  (SELECT *
                    FROM  new_valid_data
                  ) AS v
     --       LEFT  OUTER JOIN
     --             update_valid_to_invalid AS u
     --        ON   0=0
          )
    ;

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
  •