Unexpected behavior with commits and rollbacks...

[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).