SQLite error: cannot commit transaction - SQL statements in progress using Java Code

Question

I am facing an SQLite error though I am not using any explicit AutoCommit true or false. can anyone provide any input on this error. What are the situation where you will get this error.

Thanks in advance. Regards, Manasi Save

Answer 1

I faced a similar problem repeatedly (in my case it was a rollback that was not possible) when I was inside a loop looping over table entries. As long as the cursor is processing the entries, an SQL statement is "in progress". I don't know exactly, if this also prohibits commits, but it could be.

When you try to process table entries and insert entries in the same or a different table, you might want to try to collect the data in memory and after the loop do the inserts or updates.

Addtitional info: "Autocommit" normally defaults to "True" in SQLite (it of course could also depend on the access layer you use -- I am using Python and apsw, so I can't tell you more about this in Java). This means, that every insert is autocommited immediatly.

==> this could be an other solution. Instead of storing the data in memory, you could try to explicitly open a transaction and commit it after the loop -- this way, the problem should go also away.

Answer 2

That error can happen when the values returned by a RETURNING clause are not used. I can provide a reproduction code, although it is in Python.

This works:

# Connect to an in-memory database
import sqlite3
conn = sqlite3.connect(":memory:")

# Create a table
conn.execute("CREATE TABLE t(c)")
conn.commit()

# INSERT ... RETURNING with fetchall()
cursor = conn.execute("INSERT INTO t VALUES (1) RETURNING rowid")

cursor.fetchall()  # returns [(1,)]
conn.commit()  # no error

This does not work:

# Connect to an in-memory database
import sqlite3
conn = sqlite3.connect(":memory:")

# Create a table
conn.execute("CREATE TABLE t(c)")
conn.commit()

# INSERT ... RETURNING without fetchall()
cursor = conn.execute("INSERT INTO t VALUES (1) RETURNING rowid")

# Traceback (most recent call last):
#   File "<stdin>", line 1, in <module>
# sqlite3.OperationalError: cannot commit transaction - SQL statements in progress
conn.commit()