Questions about create table and transaction in sqlite3 of python

Question

I'm learning to use sqlite in python by sqlite3.

The SQL operations like insert, update support support transaction and the commit() should be called before close the connection, or nothing would change in the database. However, the 'create table' do not support transaction, or the create table is auto committed. For example,

con.execute('create table xxx (xxxx)')

the table would be created instantly. What's more, if we execute an insert statement before creating table, without commit(). Then execute create table, the insert would be committed, as well as the 'create table'.

I found nothing mentioned about such behavior in document https://docs.python.org/2/library/sqlite3.html#sqlite3-types ,https://www.python.org/dev/peps/pep-0249/#id3 or https://sqlite.org/atomiccommit.html.

My question is: 1. Is there any other operations behave like this? 2. What does other DBMS behave? 3. Is there any specification about this?

Answer 1

This behaviour comes neither from SQLite (commands like CREATE TABLE work perfectly well inside transactions) nor from Python; it's the Python sqlite3 module that tries to be clever and inserts automatic transactions at some places.

This is documented, but the reasons given are rather flimsy:

There are two reasons for doing that. The first is that some of these commands don’t work within transactions.

If the module wouldn't automatically start transactions, it would not be required to close them before those commands.

The other reason is that sqlite3 needs to keep track of the transaction state (if a transaction is active or not).

If the module wouldn't automatically start transaction, it would not need to keep track of the transaction state

Please note that the detection of DML statements does not work correctly in all cases.

I'd recommend to disable all this craziness by clearing isolation_level.