You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
The pysqlite3 context manager does not perform a rollback when a transaction fails because the database is locked by some other process performing non-DML statements (e.g. during the sqlite3 command line .dump method).
To reproduce the problem, open a terminal and run the following:
sqlite3 /tmp/test.db 'drop table person; create table person (id integer primary key, firstname varchar)'echo -e 'begin transaction;\nselect * from person;\n.system sleep 1000\nrollback;'| sqlite3 /tmp/test.db
Leave this shell running and run the python3 interpreter from a different shell, then type:
importsqlite3con=sqlite3.connect('/tmp/test.db')
withcon:
con.execute("insert into person(firstname) values (?)", ("Jan",))
pass
You should receive the following:
1 with con:
2 con.execute("insert into person(firstname) values (?)", ("Jan",))
----> 3 pass
4
OperationalError: database is locked
Without exiting python, switch back to the first shell and kill the 'echo ... | sqlite3' process. Then run:
sqlite3 /tmp/test.db .dump
you should get:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/**** ERROR: (5) database is locked *****/
ROLLBACK; -- due to errors
This means that the python process never executed a rollback and is still holding the lock. To release the lock one can exit python (clearly, this is not the intended behaviour of the context manager).
I believe the reason for this problem is that the exception happened in the implicit commit that is run on exiting the context manager, rather than inside it. In fact the exception is in the pass line rather than in the execute line. This exception did not trigger a rollback because the it happened after pysqlite_connection_exit checks for exceptions.
The expected behaviour (pysqlite3 rolling back and releasing the lock) is recovered if the initial blocking process is a Data Modification Language (DML) statement, e.g.:
because this raises an exception at the execute time rather than at commit time.
To fix this problem, I think the pysqlite_connection_exit function in src/connection.c should handle the case when the commit itself raises an exception, and invoke a rollback.
The text was updated successfully, but these errors were encountered:
The pysqlite3 context manager does not perform a rollback when a transaction fails because the database is locked by some other process performing non-DML statements (e.g. during the sqlite3 command line .dump method).
To reproduce the problem, open a terminal and run the following:
Leave this shell running and run the python3 interpreter from a different shell, then type:
You should receive the following:
Without exiting python, switch back to the first shell and kill the
'echo ... | sqlite3'
process. Then run:you should get:
This means that the python process never executed a
rollback
and is still holding the lock. To release the lock one can exit python (clearly, this is not the intended behaviour of the context manager).I believe the reason for this problem is that the exception happened in the implicit
commit
that is run on exiting the context manager, rather than inside it. In fact the exception is in thepass
line rather than in theexecute
line. This exception did not trigger arollback
because the it happened afterpysqlite_connection_exit
checks for exceptions.The expected behaviour (pysqlite3 rolling back and releasing the lock) is recovered if the initial blocking process is a Data Modification Language (DML) statement, e.g.:
because this raises an exception at the
execute
time rather than atcommit
time.To fix this problem, I think the
pysqlite_connection_exit
function in src/connection.c should handle the case when the commit itself raises an exception, and invoke a rollback.The text was updated successfully, but these errors were encountered: