SQLite Forum

[Bug] Use «RETURNING» in transaction cause error 5 on commit.
Login

[Bug] Use «RETURNING» in transaction cause error 5 on commit.

(1) By ELKirill on 2021-06-21 00:42:46 [link] [source]

SQLite 3.35.5, Microsoft Window 10, SSE3 x64 CPU. C interface.
Query (at least «INSERT») after «BEGIN TRANSACTION» that use «RETURNING» will fail «COMMIT TRANSACTION» with error code 5 (SQLITE_BUSY) and message: «cannot commit transaction - SQL statements in progress».

(2) By Keith Medcalf (kmedcalf) on 2021-06-21 01:43:00 in reply to 1 [source]

Did you run the INSERT to completion?

That is, until it sqlite3_step returned SQLITE_DONE?

If you did not complete execution of the INSERT ... RETURNING statement then of course you cannot COMMIT the transaction.

(3) By Keith Medcalf (kmedcalf) on 2021-06-21 01:52:03 in reply to 2 [link] [source]

Example:

Python 3.9.5 (tags/v3.9.5:0a7dcbd, May  3 2021, 17:27:52) [MSC v.1928 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection()
>>> db.execute('create table x(x)')
<newapsw.Cursor object at 0x000001210CFC19A0>
>>> db.beginimmediate()
<newapsw.Cursor object at 0x000001210CFC18E0>
>>> cr = db.execute('insert into x values (1) returning x');
>>> cr
<newapsw.Cursor object at 0x000001210F75F220>
>>> next(cr)
Row(x=1)
>>> db.commit()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python\Lib\site-packages\Local\newapsw.py", line 344, in commit
    return self.cursor().execute('COMMIT TRANSACTION;')
  File "C:\Python\Lib\site-packages\Local\newapsw.py", line 769, in execute
    self.__cursor.execute(sql, newbindings)
apsw.BusyError: BusyError: cannot commit transaction - SQL statements in progress
>>> next(cr)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python\Lib\site-packages\Local\newapsw.py", line 868, in __next__
    data = self.__cursor.__next__()
StopIteration
>>> db.commit()
<newapsw.Cursor object at 0x000001210F75F340>
>>>