| msg268678 - (view) |
Author: Luca Citi (lciti) * |
Date: 2016-06-16 16:45 |
| I have reported this bug to the pysqlite module for python2 ( https://github.com/ghaering/pysqlite/issues/103 ) but I also report it here because it applies to python3 too. 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: ```bash 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: ```python import sqlite3 con = sqlite3.connect('/tmp/test.db') with con: 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: ```bash 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.: ```bash echo -e 'begin transaction; insert into person(firstname) values ("James");\n.system sleep 1000\nrollback;' |
sqlite3 /tmp/test.db ``` 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. Please see patch attached. |
| msg268680 - (view) |
Author: Berker Peksag (berker.peksag) *  |
Date: 2016-06-16 17:13 |
| You may also want to check that the method_name is "commit". A test case would nice to have. Note that the connection context manager will still fail cases like nested context managers. See issue 16958. |
|
|
| msg393839 - (view) |
Author: Erlend E. Aasland (erlendaasland) *  |
Date: 2021-05-17 22:40 |
| > In fact the exception is in the `pass` line rather than in the `execute` line. I can reproduce this without the `pass` line. I've taken the liberty to create a PR based on your patch, Luca. Berker's comments have been addressed in the PR. |
|
|
| msg393912 - (view) |
Author: Erlend E. Aasland (erlendaasland) * |
Date: 2021-05-18 23:15 |
| > 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. FYI, here's the SQLite API interaction from the context manager, chronologically (using the test from the PR). (I only show the relevant arguments passed to the API, for readability.) sqlite3_prepare_v2("insert into t values('test')", insert_stmt) => SQLITE_OK sqlite3_get_autocommit() # Note, the insert statement is now prepared, but not executed yet. # Transaction control now begins sqlite3_prepare_v2("BEGIN ", begin_stmt) => SQLITE_OK sqlite3_step(begin_stmt) => SQLITE_DONE sqlite3_finalize(begin_stmt) # Here, the insert statement is executed sqlite3_bind_blob_parameter_count(insert_stmt) sqlite3_step(insert_stmt) => SQLITE_DONE sqlite3_changes() sqlite3_last_insert_rowid() sqlite3_reset(insert_stmt) => SQLITE_OK sqlite3_get_autocommit() # Enter __exit__: no exception has been raised, so it tries to commit sqlite3_prepare_v2("commit", commit_stmt) => SQLITE_OK sqlite3_step(commit_stmt) => SQLITE_BUSY (database is locked) sqlite3_finalize(commit_stmt) # After the fix, rollback is now executed sqlite3_prepare_v2("rollback", rollback_stmt) sqlite3_step(rollback_stmt) => SQLITE_DONE sqlite3_finalize(rollback_Stmt) As you can see, it does not fail (and raise an exception) until commit is issued inside __exit__. |
|
|
| msg400253 - (view) |
Author: Pablo Galindo Salgado (pablogsal) * |
Date: 2021-08-25 10:59 |
| New changeset 7ecd3425d45a37efbc745788dfe21df0286c785a by Erlend Egeberg Aasland in branch 'main': bpo-27334: roll back transaction if sqlite3 context manager fails to commit (GH-26202) https://github.com/python/cpython/commit/7ecd3425d45a37efbc745788dfe21df0286c785a |
|
|
| msg400266 - (view) |
Author: Łukasz Langa (lukasz.langa) * |
Date: 2021-08-25 13:58 |
| New changeset a3c11cebf174e0c822eda8c545f7548269ce7a25 by Erlend Egeberg Aasland in branch 'main': bpo-27334: Fix reference leak introduced by GH-26202 (GH-27942) https://github.com/python/cpython/commit/a3c11cebf174e0c822eda8c545f7548269ce7a25 |
|
|
| msg400287 - (view) |
Author: Pablo Galindo Salgado (pablogsal) * |
Date: 2021-08-25 19:02 |
| New changeset 52702e8ba0d777ac92ec36038213976545c4759e by Erlend Egeberg Aasland in branch '3.9': [3.9] bpo-27334: roll back transaction if sqlite3 context manager fails to commit (GH-26202) (GH-27944) https://github.com/python/cpython/commit/52702e8ba0d777ac92ec36038213976545c4759e |
|
|
| msg400491 - (view) |
Author: Pablo Galindo Salgado (pablogsal) * |
Date: 2021-08-28 18:26 |
| New changeset 2a80893e5c023a73ccd32cc319f4f0404f548c00 by Erlend Egeberg Aasland in branch '3.10': [3.10] bpo-27334: roll back transaction if sqlite3 context manager fails to commit (GH-26202) (GH-27943) https://github.com/python/cpython/commit/2a80893e5c023a73ccd32cc319f4f0404f548c00 |
|
|
| msg400493 - (view) |
Author: Erlend E. Aasland (erlendaasland) * |
Date: 2021-08-28 18:41 |
| Thanks Luca, for the report, reproducer, and initial patch, Berker for helpful suggestion, and Łukasz, Pablo, & Victor for reviewing and merging. |
|
|
| msg400790 - (view) |
Author: STINNER Victor (vstinner) * |
Date: 2021-08-31 22:06 |
| Nice! |
|
|