New submission from Luca Citi:
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.
----------
components: Extension Modules
files: fix_pysqlite_connection_exit.patch
keywords: patch
messages: 268678
nosy: lciti
priority: normal
severity: normal
status: open
title: pysqlite3 context manager not performing rollback when a database is
locked elsewhere for non-DML statements
versions: Python 3.6
Added file: http://bugs.python.org/file43420/fix_pysqlite_connection_exit.patch
_______________________________________
Python tracker <rep...@bugs.python.org>
<http://bugs.python.org/issue27334>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe:
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com
