Unexpected behavior with commits and rollbacks...

[mtnbiker66]

Good afternoon, all --

Just wanted to ask everyone if something I'm seeing in my application is expected behavior. I'm creating an app that imports one SQLITE database into another, and I'm performing insert statements with QSqlQuery and bind variables into the destination DB. Unexpectedly, without issuing a db.commit command after I execute the insert statement, data is actually getting committed to the DB. I tried to issue a rollback following my query.exec command but the transaction was not active so no rollback occurred, and when I checked the database, the data had been committed.

However, if I issue a db.transaction() command before my query.exec I *am* able to execute the commit and rollback commands successfully. I guess what I'm seeing is that without the db.transaction() command the default behavior is to automatically commit the data, but that was wholly unexpected. I really could not find any answers out in the cyberspher, so I'm just wondering if what I'm seeing is by design. SQLITE supports transactions so I didn't expect to have had to issue the db.transaction() command - not that that's any issue whatsoever, but I am curious.

Thanks in advance!

Kodi

[wysota]

Every database I know immediately commits a query that is not part of an open transaction. I don't see what's so unexpected here.

[ChrisW67]

I second that.

Sqlite supports transactions (BEGIN ... COMMIT or ROLLBACK) and db.transaction() is doing that on you behalf. Like every other RDBMS I have ever worked with (Ingres, Oracle, Postgres, Sqlite, DB2, Mysql, Sybase, MS Sql Server and Access (cringe), Informix, and few more exotic options) a single DML command outside of an explicit transaction is treated as a transaction in its own right, executed immediately, and committed if successful or rolled back if not. Sqlite (and others) call this "autocommit" and beginning an explicit transaction takes you out of this mode. I'd be very surprised if this transaction behaviour is not part of the relevant standards.

If you are doing many thousands of inserts then do them inside a single transaction if at all possible: this will be much faster. see the Sqlite FAQ for reasoning.

[mtnbiker66]

I guess because I work with Oracle daily and have for 15 years I was expecting to have to explicitly issue a "commit" before it got recorded - I don't have to open a transaction explicitly either. The only way a transaction is committed w/out issuing a "commit" is if I connect to another database with an open transaction.

Every database I know immediately commits a query that is not part of an open transaction. I don't see what's so unexpected here.

---

Added after 4 minutes:

Now that I know I have to issue a db.transaction() I am planning on a single transaction because I need to rollback everything if something fails...

As for the autocommit - because I work in Oracle and I suppose that must be turned off, as I said above I have to issue a commit and rollback for everything...Nothing is automatically committed with either the DB or the software that runs on top of it so I was caught a little off-guard with SQLite's auto commit...


Kodi

[wysota]

It's not "SQLite's autocommit", it's "autocommit". if you work on a database that has everything transacted, then it means this particular database instance (and not database type) is explicitly configured to do so (meaning it has autocommit explicitly disabled). I've worked with Oracle databases (once or twice, but still) and I didn't have to do any explicit commits for every single query. Please do not think that if you're used to some particular behaviour, it's everyone else who do something "non-standard".

[mtnbiker66]

Thanks for attempting to "teach me" about databases and programming; I believe my 28 years in the business has taught me that "my configuration" is the standard and all others "non-standard" is a bogus and foolish thought. I've worked in 3 different database systems in multiple configurations on different platforms and in every configuration the auto-commit was *not* the default. It may be that the DBA's specifically set the DBs up that way on some of them (ie RDBMS), so I was just surprised to find SQLite's auto-commit to be the default, and if you re-read my initial question, that's all I was asking -- is that the default.

BTW -- Oracle itself only performs an implicit commit if you execute DDL, otherwise you have to explicitly commit or rollback. You can set up SQL*Plus to perform autocommits but the default for that, too, is OFF...

[wysota]

I won't argue however oracle claims to think otherwise, e.g.: http://docs.oracle.com/javadb/10.5.1...mref25753.html

specifcally:

JDBC specifies that the default auto-commit mode is ON.

so it more seems a matter of the driver used than the database configuration.


This one -- http://infolab.stanford.edu/~ullman/...nstandard.html -- claims that Oracle's command line client sqlplus is the one responsible for opening a transaction upon connect and closing that transaction upon quit (or an explicit commit or rollback) so again implicit transactions don't seem to be the default SQL behaviour but rather a property of a particular mechanism of connecting to the database.

My personal experience of doing queries (and writing triggers which ARE defined to be an implicit transaction of their own) on a particular configuration of Oracle doesn't allow me to argue with your 28 years of experience, though. It seems all other databases I have worked with are very "non-standard" as they differ from what you consider "standard". It also seems Qt complies with the standard "non-standard" behaviour too. So in the end everybody has their own "standards" in this "non-standard" world (which was my main point all along when trying to, as you said, "teach you" about approaching standards).