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?
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.
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.