Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    14

    transactions and table sequences/ids

    I am working on a database abstraction app and now adding transaction support. When I set autocommit to false on say a mysql query or set the commit to default on a oracle db query, it works as expected and does not commit until I add a commit statement. However, in my script, I have it printing the num of rows, affected rows, etc., on each operation and it still increments the table index when I do not commit the transaction. Upon a subsequent transaction, it begins at the incremented index from the non-commited query. Is this the correct behavior for transactions/tables?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I'm not sure what you mean by a "table index", but it sounds like you are talking about auto-incremented keys. In Oracle, these are implemented using a SEQUENCE, and a feature of sequences is that they are not part of the transaction: once the sequence has been incremented it stays incremented, even if the transaction rolls back.

  3. #3
    Join Date
    Feb 2005
    Posts
    14
    Thanks Tony. Yes I did mean auto-incremented keys/sequences. Essentially, I am inserting a single value into the table w/o commiting. I print the results from a select query and the last field printed is the incremented value. Say I have 10 rows and I do 2 inserts w/o commiting. The key is incremented to 12 and upon subsequent inserts where I do commit, the auto-increment value begins at 13 instead of 11.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Yes, that is normal and correct behaviour!

  5. #5
    Join Date
    Feb 2005
    Posts
    14
    Much obliged.

Posting Permissions

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