Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    62

    Unanswered: A possible COMMIT issue

    I'm trying to figure out what happens when I collect, say, 100 rows to be inserted into a table, and, before the COMMIT, an error occurs. The error in this case is caused by the client application which tries to insert a row with a duplicate primary key (no need to detail why the app does this, but let's assume it's not possible to change this).

    The situation is that the APP asked the DB to record, say, 50 rows and then it sends one row with a duplicate primary key. The DB refuses to receive this row and the APP collects an error message. Then, the APP sends another 50 rows, without any problems. Then the COMMIT comes to the scene.

    Is it possible that the second group of 50 rows are lost because of the duplicate key error?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (no need to detail why the app does this, but let's assume it's not possible to change this)
    I don't want to varnish over erroneous apps.
    But, you are free to go your way. I should not critisize you, or see quietly.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If a SQL statement fails, DB2 will undo all changes made by this SQL statement (and only this statement). Previous or succeeding SQL statement in the same transaction will succeed (or fail) independent of this one statement. Everything else can be derived from the ACID properties for transactions.

    In short: you have n statements inserting some rows, which succeeds. Then you have one statement, which fails - this doesn't cause the transaction to be rolled back (at least not in DB2). Then you have another set of n statements inserting some more rows. Then you commit. You'll have all the modifications of the successful statements in your system.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2009
    Posts
    62
    Thanks a lot, stolze.

Posting Permissions

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