Issue: Sqlite database locked - regression introduced in 2.4.5?
Author: stenci
State: closed
Created: 2016-09-19 16:01:15

Below an excerpt from my CherryPy app. 

The app receives bursts of ajax calls to `cncprogram_done`. Sometimes just 1 or 2 calls, sometimes up to 10 or 20 at a time (I will eventually optimize it, but for now I'm OK with it).

I just upgraded to Peewee 2.8.3 and only the first call of each burst works, the other calls say `peewee.OperationalError: database is locked` while executing `part.save()`.

I tried to revert to older versions, and I found out that with Peewee 2.4.4 works well and with the 2.4.5 fails.

I reverted back to 2.4.4 on the production server and everything seems to work fine.

Any idea whether I am doing something wrong or this is really a regression? Where do I start investigating?

Here is (part of) the code:

```
db = peewee.SqliteDatabase(path_name + '/doc.db', threadlocals=True)

class PeeweeModel(peewee.Model):
    class Meta:
        database = db

class CncProgramPart(PeeweeModel):
    sheet = peewee.ForeignKeyField(CncProgramSheet, related_name='parts')
    part_number = peewee.CharField()
    time_finished = peewee.DateTimeField(default=0)
    remake = peewee.BooleanField(default=False)
    comment = peewee.CharField(default='')

    def render(self, settings):
        return render('cncprogram_edit_part_row.html',
                      {'part': self, 'settings': settings})

class DocFinder:

    @cherrypy.expose
    def cncprogram_done(self, rowid, value):

        with db.transaction():
            checked = value == 'true'
            now = time.time()
            part = CncProgramPart.get(CncProgramPart.id == rowid[9:])

            if checked and not part.remake and not part.comment:
                part.time_finished = now
                part.comment = ''
                part.remake = False
                part.save()
            elif part.time_finished:
                part.time_finished = 0
                part.save()

            return part.render(Settings())
```


Relevant commits:

https://github.com/coleifer/peewee/compare/2.4.4...2.4.5


Are you calling connect and close at the start and end of every request? Is the busy timeout suitably high to allow retrying queries? I've noticed that die to the way SQLite does its busy timeout, many queries sent at close to the same time will always retry at the same times.


What I've found is that by adding a slight bit of jitter to the default busy handler you'll see a lot less operational errors...I should really submit a patch...


I installed the 2.4.5, removed the method `SqliteDatabase.begin()` and it works.

The method was added in this [commit](https://github.com/coleifer/peewee/commit/623374855c8c5fe5cf6aa0717b50e2ad77fc877c).

The logged SQL while executing a burst of two calls shows SQL statements alternating from the two calls. With the `SqliteDatabase.begin()` the `UPDATE` from the first call succeeds, the one from the second call fails. Without it they both succeed.

Here is an excerpt from the log with the 2.4.5. The first two lines of the log disappear after removing the `SqliteDatabase.begin()`.

```
('BEGIN DEFERRED', None)
('BEGIN DEFERRED', None)
('SELECT "t1"."id", ...
('SELECT "t1"."id", ...
('UPDATE "cncprogrampart" SET ...
('UPDATE "cncprogrampart" SET ...
--- crash ---
```


Sorry, I was busy doing my tests and didn't notice your questions.

I don't think I am doing anything else than the `db = peewee.SqliteDatabase(path_name + '/doc.db', threadlocals=True)` when the app starts. After that I use CherryPy with its default settings. I think there are 10 threads sharing `db`.


Okay, I think that may be the source of the issue. It is probably wise to connect when the request comes in, then clowhen the response goes out. I imagine cherrypy provides some kinds of hooks as this is common to most all frameworks.

Docs: http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-request-hooks


I tried with the Flask code that you show in the documentation and I tried to do the same thing with CherryPy. Both the version crash with `SqliteDatabase.begin()`, they both work without it.

You can test both the version with the scripts below. You start the server, then you go to `http://127.0.0.1:8080/` for CherryPy and to `http://127.0.0.1:5000/` for Flask.

One second after showing the page, a burst of two requests will reach the server and the server will crash on the second request.

**CherryPy**

```
import cherrypy
import peewee
import time
import logging

logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

db = peewee.SqliteDatabase('test.db')

class PeeweeModel(peewee.Model):
    class Meta:
        database = db

class Table1(PeeweeModel):
    n = peewee.IntegerField()
    counter = peewee.IntegerField()

Table1.drop_table(True)
Table1.create_table(True)
Table1.create(n=1, counter=0)
Table1.create(n=2, counter=0)

class Root:

    @cherrypy.expose
    def index(self):
        cherrypy.log('index')
        return """<!DOCTYPE HTML>
<html>
<head>
<script>
setTimeout(function(){
xhttp = new XMLHttpRequest();
xhttp.open("GET", "increase?n=1", true);
xhttp.send();
xhttp = new XMLHttpRequest();
xhttp.open("GET", "increase?n=2", true);
xhttp.send();}, 2000);
</script>
</head>
<body>
<a href="result">See result</a>
</body>
</html>"""

    @cherrypy.expose
    def increase(self, n):
        cherrypy.log('increase start')
        with db.transaction():
            record = Table1.get(Table1.n == int(n))
            time.sleep(0.5)
            record.counter += 1
            time.sleep(0.5)
            record.save()
            time.sleep(0.5)
        cherrypy.log('increase end')
        return 'OK'

    @cherrypy.expose
    def result(self):
        cherrypy.log('result')
        return '<br>'.join(['n={} counter={}\n'.format(r.n, r.counter) for r in Table1.select()])

def connect():
    cherrypy.log('connect')
    db.connect()

def close():
    cherrypy.log('close')
    if not db.is_closed():
        db.close()

cherrypy.engine.subscribe('before_request', connect)
cherrypy.engine.subscribe('after_request', close)
cherrypy.quickstart(Root())
```

**Flask**

```
import flask
import peewee
import time
import logging

logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

db = peewee.SqliteDatabase('test.db')
app = flask.Flask(__name__)

@app.before_request
def _db_connect():
    app.logger.debug('connect')
    db.connect()

@app.teardown_request
def _db_close(exc):
    app.logger.debug('close')
    if not db.is_closed():
        db.close()

class PeeweeModel(peewee.Model):
    class Meta:
        database = db

class Table1(PeeweeModel):
    n = peewee.IntegerField()
    counter = peewee.IntegerField()

Table1.drop_table(True)
Table1.create_table(True)
Table1.create(n=1, counter=0)
Table1.create(n=2, counter=0)

@app.route('/')
def index():
    app.logger.debug('index')
    return """<!DOCTYPE HTML>
<html>
<head>
<script>
setTimeout(function(){
xhttp = new XMLHttpRequest();
xhttp.open("GET", "increase/1", true);
xhttp.send();
xhttp = new XMLHttpRequest();
xhttp.open("GET", "increase/2", true);
xhttp.send();}, 2000);
</script>
</head>
<body>
<a href="result">See result</a>
</body>
</html>"""

@app.route('/increase/<n>')
def increase(n):
    app.logger.debug('increase start')
    with db.transaction():
        record = Table1.get(Table1.n == int(n))
        time.sleep(0.5)
        record.counter += 1
        time.sleep(0.5)
        record.save()
        time.sleep(0.5)
    app.logger.debug('increase end')
    return 'OK'

@app.route('/result')
def result():
    app.logger.debug('result')
    return '<br>'.join(['n={} counter={}\n'.format(r.n, r.counter) for r in Table1.select()])

app.run(threaded=True)
```


I have something awesome you can try...

It's currently only in master branch, but if you want to try it out it should solve your problem.

In the `playhouse.sqliteq` module is a class named `SqliteQueueDatabase`. Replace this:

``` python

db = peewee.SqliteDatabase('test.db')
```

With this:

``` python
from playhouse.sqliteq import SqliteQueueDatabase
db = SqliteQueueDatabase('t.db', readers=4, autostart=True)
```

At the same time, remove all calls to db.connect() and db.close(). 


Another thing you should _always_ be doing is specifying `journal_mode='wal'` when you instantiate your Sqlite database. This allows multiple readers to co-exist with a single writer, and I think this may also magically solve your issues:

``` python
db = SqliteDatabase('foo.db', pragmas=[('journal_mode', 'wal')])
```


Oh, and lastly, you can use the `PooledSqliteDatabase` from the `playhouse.pool` module. This will also most likely solve the issues you are encountering. You would write:

``` python
from playhouse.pool import PooledSqliteExtDatabase

db = PooledSqliteExtDatabase(
    'foo.db', 
    pragmas=[('journal_mode', 'wal')],
    max_connections=30,
    stale_timeout=3600,
    check_same_thread=False)
```


Thanks for suggesting to use the WAL journal mode.

I tried your second and third suggestions, and unfortunately the magic didn't happen. 

I just cloned the Peewee repository. I never used git to clone one library used in another project. How do I tell my app to `import peewee` from the repository folder instead of using the one installed by pip? Do I need to uninstall it with pip and add the new path to the `PYTHONPATH`?

(Going home now, see you tomorrow)


You can run `import peewee` then on the next line just type `peewee` and press `<enter>`. It should print which module you're using. For example:

![s1474437363 4](https://cloud.githubusercontent.com/assets/119974/18699309/25bdfb64-7f96-11e6-9356-b067b23c62da.png)


Even awesome doesn't seem to be enough :(

I did `pip uninstall peewee` and `import peewee` from the cloned repository.

The test with Flask now fails and the server doesn't start (see the traceback below).

The test with CherryPy runs, but crashes like the old one when the second call finds the database locked. I tried both with and without the `connect()` and `close()`.

**Flask code**

```
import flask
import time
import logging
import peewee

logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

# db = peewee.SqliteDatabase('test.db', pragmas=[('journal_mode', 'wal')])

from playhouse.sqliteq import SqliteQueueDatabase
db = SqliteQueueDatabase('test.db', readers=4, autostart=True)

app = flask.Flask(__name__)

@app.before_request
def _db_connect():
    app.logger.debug('connect')
    db.connect()

@app.teardown_request
def _db_close(exc):
    app.logger.debug('close')
    if not db.is_closed():
        db.close()

class PeeweeModel(peewee.Model):
    class Meta:
        database = db

class Table1(PeeweeModel):
    n = peewee.IntegerField()
    counter = peewee.IntegerField()

Table1.drop_table(True)
Table1.create_table(True)
Table1.create(n=1, counter=0)
Table1.create(n=2, counter=0)

@app.route('/')
def index():
    app.logger.debug('index')
    return """<!DOCTYPE HTML>
<html>
<head>
<script>
setTimeout(function(){
xhttp = new XMLHttpRequest();
xhttp.open("GET", "increase/1", true);
xhttp.send();
xhttp = new XMLHttpRequest();
xhttp.open("GET", "increase/2", true);
xhttp.send();}, 2000);
</script>
</head>
<body>
<a href="result">See result</a>
</body>
</html>"""

@app.route('/increase/<n>')
def increase(n):
    app.logger.debug('increase start')
    with db.transaction():
        record = Table1.get(Table1.n == int(n))
        time.sleep(0.5)
        record.counter += 1
        time.sleep(0.5)
        record.save()
        time.sleep(0.5)
    app.logger.debug('increase end')
    return 'OK'

@app.route('/result')
def result():
    app.logger.debug('result')
    return '<br>'.join(['n={} counter={}\n'.format(r.n, r.counter) for r in Table1.select()])

app.run(threaded=True)
```

**Flask traceback**

```
Traceback (most recent call last):
  File "C:\eclipse\plugins\org.python.pydev_5.2.0.201608171824\pysrc\pydevd.py", line 1531, in <module>
    globals = debugger.run(setup['file'], None, None, is_module)
  File "C:\eclipse\plugins\org.python.pydev_5.2.0.201608171824\pysrc\pydevd.py", line 938, in run
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "C:\eclipse\plugins\org.python.pydev_5.2.0.201608171824\pysrc\_pydev_imps\_pydev_execfile.py", line 25, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "C:\workspace\test\test_flask_peewee_burst.py", line 36, in <module>
    Table1.drop_table(True)
  File "C:\workspace\peewee\peewee.py", line 4904, in drop_table
    cls._meta.database.drop_table(cls, fail_silently, cascade)
  File "C:\workspace\peewee\peewee.py", line 3804, in drop_table
    model_class, fail_silently, cascade))
  File "C:\workspace\peewee\playhouse\sqliteq.py", line 199, in execute_sql
    queue.put(cursor)
  File "C:\Anaconda3\envs\flask\lib\queue.py", line 127, in put
    if self.maxsize > 0:
TypeError: unorderable types: NoneType() > int()
```


What happens if I use the 2.4.4 (or if I delete the `SqliteDatabase.begin() from a more recent version`)?

Do I risk that the statements inside a transaction are not really a transaction, even if I wrap them in a `db.transaction()`?

Or...?


Oh shit, python 3. I'll fix that.


Why is this issue closed?

The fix mentioned above fixes a problem mentioned on [a comment](https://github.com/coleifer/peewee/issues/1071#issuecomment-248661857), not the original problem.

I think this is an important problem and should be solved. I am just sending two requests faster than they can be processed and the database fails instead of waiting with the second one. I am surprised that nobody ever found this problem. Am I doing something so uncommon? Or am I doing it in the wrong way?

I just realized that I was already using features introduced after the 2.4.4 (like `union_all(()`) and I can't go back to the latest working version. So I upgraded to the 2.8.3, did some (manual) testing and found a few places where the app either locks the database forever and the server needs to be restarted or it crashes complaining that the server is locked.

I added the following monkey patch to both the development and production servers and everything seems to work fine, but... monkey patching?... I'm scared!:

```
import peewee
del peewee.SqliteDatabase.begin
```


Whoops!


OK, so I don't believe there is a bug here. In your example flask application I see that you've added calls to `time.sleep()`. I presume this is to force the application into attempting to open a write transaction while another write transaction is open. SQLite only allows one writer at a time, though, so yes -- you will get errors if you try to do this. If the DB comes back saying it's locked by another writer, `pysqlite` will retry the query up to a certain time limit (default is 5 seconds, I believe). Should it fail to get the lock in that time limit, you get an error.

This is the correct behavior.

I'm not sure how you had your application set up previously, how you were managing connections (it sounds like you weren't), and how you were managing transactions. There are too many variables in there for me to guess as to why this appeared to work for you.

When you use SQLite in a multi-threaded environment you need to take care of how you manage your transactions, simple as that. I would not recommend monkeypatching the begin method unless you want a subtly broken app, or an app that works by accident rather than design.

Suggestions:
- Use the `playhouse.sqliteq.SqliteQueueDatabase`. I've added some preliminary docs [here](http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#sqliteq).
- Keep your write transactions as small as possible, so that they can run quickly and then get out of the way to allow the next writer to acquire the lock.
- Avoid keeping a connection to the database open for a long time after writing.


> If the DB comes back saying it's locked by another writer, pysqlite will retry the query up to a certain time limit (default is 5 seconds, I believe). Should it fail to get the lock in that time limit, you get an error.
> 
> This is the correct behavior.

I agree with you. You describe the behavior I would expect, which was the behavior until 2.4.4. If there was a write transaction in progress, the 2.4.4 would wait (up to the 5 seconds default timeout for sqlite) and then crash.

Now it just crashes, without waiting.

Your advice to make the transactions as small as possible and not to keep a transaction after writing is valid, but I can't shrink the time to zero. I can shorten the time and decrease the chance of crash, but can't get rid of it. The correct behavior would be for peewee to take care of it as it used to up to 2.4.4.

I tried all the solutions you suggested, but no one does what you say.

I cleaned my previous test so that now it's easier to follow. Requesting `localhost:5000` with the code below will send 3 requests at 0.1 seconds distance from each other:
- The first request will read a record, change some values and write it back to the database
- The second request will try to do the same and wait for the previous transaction to be finished
- Same with the third, but will rollback after saving, just to test the rollback

Using peewee 2.4.4 the result is as expected: the second save will wait for the first transaction to be finished and the third will wait for the second. At the end the value in the database will be 2 because the 3 has been rollbacked.

Using peewee 2.8.5 with all the combination that I can think of I get one of these 3 wrong results: 
- Immediate crash without waiting for the sqlite timeout
- Nothing is saved
- The value 3 is saved (no rollback was performed on the third transaction)

Here are the results of my tests:
![image](https://cloud.githubusercontent.com/assets/5955495/19086016/1f300340-8a32-11e6-9bba-db64752afe46.png)

Here is the code:

``` Python
import flask
import time
import logging
import peewee
import threading
import datetime

CREATE_CONNECTIONS          = 0
USE_SqliteDatabase          = 0
USE_PooledSqliteExtDatabase = 0
USE_SqliteQueueDatabase     = 1
DEL_BEGIN                   = 0

print('peewee.__version__ =', peewee.__version__)

if DEL_BEGIN:
    del peewee.SqliteDatabase.begin

logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

if USE_SqliteDatabase:
    if peewee.__version__ == '2.4.4':
        db = peewee.SqliteDatabase('test.db')
    else:
        db = peewee.SqliteDatabase('test.db', pragmas=[('journal_mode', 'wal')])
if USE_PooledSqliteExtDatabase:
    from playhouse.pool import PooledSqliteExtDatabase
    db = PooledSqliteExtDatabase(
        'test.db',
        pragmas=[('journal_mode', 'wal')],
        max_connections=30,
        stale_timeout=3600,
        check_same_thread=False)
elif USE_SqliteQueueDatabase:
    from playhouse.sqliteq import SqliteQueueDatabase
    db = SqliteQueueDatabase(
        'test.db',
        pragmas=[('journal_mode', 'wal')],
        readers=4,
        autostart=True)

class PeeweeModel(peewee.Model):
    class Meta:
        database = db

class Table1(PeeweeModel):
    n = peewee.IntegerField()
    time_stamp = peewee.CharField(null=True)

Table1.drop_table(True)
Table1.create_table(True)
Table1.create(n=0)

app = flask.Flask(__name__)

@app.before_request
def _db_connect():
    if CREATE_CONNECTIONS:
        log('connect')
        db.connect()

@app.teardown_request
def _db_close(exc):
    if CREATE_CONNECTIONS:
        log('close')
        if not db.is_closed():
            db.close()

def now():
    return datetime.datetime.now().strftime('%H:%M:%S.%f')

def log(txt):
    print('  {} {:6d} {}'.format(now(),
                                 threading.current_thread().ident,
                                 txt))

@app.route('/')
def index():
    log('index')
    return """<!DOCTYPE HTML>
<html>
<head>
<script>
setTimeout(function(){
    xhttp = new XMLHttpRequest();
    xhttp.open("GET", "set_time/1", true);
    xhttp.send();
}, 100);

setTimeout(function(){
    xhttp = new XMLHttpRequest();
    xhttp.open("GET", "set_time/2", true);
    xhttp.send();
}, 200);

setTimeout(function(){
    xhttp = new XMLHttpRequest();
    xhttp.open("GET", "set_time/3", true);
    xhttp.send();
}, 300);
</script>
</head>
<body>
hello
</body>
</html>"""

@app.route('/set_time/<n>')
def set_time(n):
    with db.transaction():

        log('  {}  set_time{} start transaction'.format(now(), n))
        time.sleep(1)

        log('  {}  set_time{} get'.format(now(), n))
        rec = Table1.get()
        time.sleep(1)

        log('  {}  set_time{} save'.format(now(), n))
        rec.n = n
        rec.time_stamp = now()
        rec.save()
        log('  {}  set_time{} saved'.format(now(), n))
        time.sleep(1)

        if n == '3':
            db.rollback()
            log('  {}  set_time{} rollback'.format(now(), n))
        log('  {}  set_time{} end transaction'.format(now(), n))

    return 'OK'

app.run(threaded=True)
```


This seems to be the commit that introduced the regression: https://github.com/coleifer/peewee/commit/623374855c8c5fe5cf6aa0717b50e2ad77fc877c

After using `pip install -U git+git://github.com/coleifer/peewee@6f01097` it works.

After using `pip install -U git+git://github.com/coleifer/peewee@6233748` it fails.


The problem is here:

```
Table1.drop_table(True)
Table1.create_table(True)
Table1.create(n=0)
```

You are implicitly creating a write transaction there, along with opening a connection and not closing it.

Change that to read:

``` python
# If you're using something other than the SqliteQueueDatabase:
with db.execution_context():
    Table1.drop_table(True)
    Table1.create_table(True)
    Table1.create(n=0)

# If you're using the SqliteQueueDatabase then just create the
# tables after calling db.start()
```


It's also worth mentioning that SQLite's default transactions use `DEFERRED` semantics, so multiple transactions can be opened and reads performed concurrently, but depending on whether or not you're using WAL mode, one of the concurrent writes may fail.

To simplify your example, consider a view that does a compare-and-swap with a sleep in between. Something like:

``` python
def my_view():
    with db.atomic():
        try:
            obj = Register.get(Register.value == 1)
        except Register.DoesNotExist:
            return 'cas failed, obj with value 1 not found'
        time.sleep(1)
        obj.value = 2
        obj.save()
    return 'cas succeeded'
```

If you fire off two requests A and B, with B just a millisecond behind A, what you will see will depend on the type of transaction opened, and whether WAL mode is enabled.

| lock type | wal mode? | result |
| --- | --- | --- |
| deferred | no | request A reports error because it cannot write due to B's read still being open. B succeeds. |
| deferred | yes | A succeeds, B reports error when it tries to commit |
| immediate | yes or no | A succeeds, B blocks until A finishes, then reports record not found |

deferred and not using WAL:
- T0 = A opens read transaction. Finds object. Starts sleeping.
- T1 = B opens read transaction. Finds object. Starts sleeping.
- T1000 = A sets value and attempts UPDATE. A cannot aquire lock because B is still reading. Error.
- T1001 = B acquires lock and updates successfully.

deferred and using WAL:
- T0 = A opens read transaction. Finds object. Starts sleeping.
- T1 = B opens read transaction. Finds object. Starts sleeping.
- T1000 = A acquires write lock and saves successfully.
- T1001 = B attempts to acquire write lock but cannot because A changed the DB snapshot. Error.

immediate with or without WAL:
- T0 = A opens transaction. Finds object. Starts sleeping.
- T1 = B attempts to open transaction, but A has lock. B waits.
- T1000 = A saves successfully.
- T1000.01~ = A released lock, B acquires lock. B cannot find object.


I tried all the combinations of the 3 objects `SqliteDatabase`, `PooledSqliteExtDatabase` and `SqliteQueueDatabase`, with and without creation of the connection, with and without `db.start()`, with and without `with db.transaction():`. The results are the same: crash, no save or save but no rollback.

I was never able to get it to correctly save and rollback when required (well, I was, by installing the good old 2.4.4)

I just saw your last comment popping up. I will investigate the lock type and journal mode.

I started using peewee and sqlite because it was easy and everything worked. Two months ago I started adding features to the old app, I upgraded many of the libraries, used some new feature (like `union_all(()`), then I received a few calls reporting crashes. I spent the last 10 days chasing this problem and trying to reduce it to a manageable test. (Sorry for the little venting, back to work now and thanks for your support.)


The above examples I showed represent the true and correct semantics for a SQLite database, in my opinion. Have a look at https://www.sqlite.org/isolation.html for some more info.

I want to highlight the example of using WAL mode with a DEFERRED transaction (the one where A succeeds and B reports an error). This happens because B is attempting to upgrade it's lock from a READ lock to a WRITE lock, but since A has swooped in and committed changes in the meantime, B's attempt to upgrade fails immediately.

It is described like this in the Sqlite docs:

> Another example: X starts a read transaction using BEGIN and SELECT, then Y makes a changes to the database using UPDATE. Then X tries to make a change to the database using UPDATE. The attempt by X to escalate its transaction from a read transaction to a write transaction fails with an SQLITE_BUSY_SNAPSHOT error because the snapshot of the database being viewed by X is no longer the latest version of the database. If X were allowed to write, it would fork the history of the database file, which is something SQLite does not support. In order for X to write to the database, it must first release its snapshot (using ROLLBACK for example) then start a new transaction with a subsequent BEGIN.

I hope this convinces you of the correctness of the implementation.

SqliteQueueDatabase is a special-case in that since all queries are interleaved on a single connection, transactional semantics pretty much can be thrown out the window. It's intended for dead-simple or advanced use-cases, but not in-between I guess you might say.


I understand your frustration. I now know more about SQLite than I ever thought I would need to, to write a useful tool. I think I misunderstood the problem you were encountering and offered you bad suggestions as a result, which didn't help you I'm sure, so I apologize for the "red herrings".


The frustrating part is not lack of/bad support (which is always good quality and way more than what I pay for, thank you! :) )

The frustrating part is that I need to change code that has been working for two years. My code is broken now and... there is no regression. I could digest a non backward compatible change between 2.\* and 3.0, but it's difficult to digest between 2.4.4 and 2.4.5. I guess incompatibility between versions can be part of the game, but this is my first time and it doesn't feel right.

I still don't know why it was working before and I can't use it anymore in the same way, but after looking at my code it looks like I can work around the lack of `rollback()` and switch to `SqliteQueueDatabase`. Back to work now, thanks.


Correct me if I'm wrong, but the fact is you've jumped from 2.4.4 (which is almost 2 years old at this point) to 2.8.5. So there's quite a lot of things that have changed besides just the decision to run SQLite in autocommit mode.

In the diff of version 2.4.4 to 2.4.5 you can see that [documentation](http://docs.peewee-orm.com/en/latest/peewee/database.html#sqlite-and-autocommit) was updated to reflect the [SQLite "autocommit mode" change](http://docs.peewee-orm.com/en/latest/peewee/database.html#sqlite-and-autocommit):

https://github.com/coleifer/peewee/compare/2.4.4...2.4.5#diff-1fdd0c1e085be7d7af1cc4f80529eecdL133

> The frustrating part is that I need to change code that has been working for two years

I understand, believe me -- but can you explain to me precisely why the code worked in 2.4.4? What was happening at the connection level? What was happening at the transaction level?

You see, the thing is, pysqlite (`sqlite3` in the standard library) is sneaky. From the documentation:

> By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

This is one of the reasons Roger Binns created [apsw](https://github.com/rogerbinns/apsw). From the documentation to APSW (emphasis his):

> **Nothing** happens behind your back. By default pysqlite tries to manage transactions by parsing your SQL for you, but you can turn it off. This can result in very unexpected behaviour with pysqlite.

Peewee follows the same principles, and manages to use the standard library SQLite driver so users of Peewee don't have to install an additional dependency.


Last week I tried to test the behavior with the different combinations of journal mode and isolation using `sqlite3` without `peewee`, but I noticed that something was added to the SQL statements that I was sending, so I gave up. The next time I will try with `apsw`.

I don't know why my code was working and stopped. It was my first time with SQL, with peewee and with python in general, so I used all the defaults. I recreated a simplified version that reproduces the problem on a new clean environment with only peewee.

The code below works with the 2.4.4 and fails with the 2.4.5. Changing the value of `autocommit` makes no difference, all the transactions happen in a `with db.transaction()` block. 

I really appreciate you spending time on this issue, but I understand if you consider it a waste of time because it is an old problem. 

``` python
import peewee
import time
import datetime
import threading
import os
import logging

logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

if os.path.exists('test.db'):
    os.unlink('test.db')

db = peewee.SqliteDatabase('test.db', autocommit=True)

class Table1(peewee.Model):
    n = peewee.IntegerField()
    time_stamp = peewee.CharField()
    class Meta:
        database = db

def now():
    return datetime.datetime.now().strftime('%H:%M:%S.%f')

def add_rec(n):
    with db.transaction():
        time.sleep(1)

        with db.transaction() as tx:
            rec = Table1.get()
            time.sleep(1)

            Table1.create(n=n, time_stamp=now())
            rec.save()
            time.sleep(1)

            if n == 3:
                tx.rollback()

with db.transaction():
    Table1.create_table(True)
    Table1.create(n=0, time_stamp=now())

t1 = threading.Timer(0.3, add_rec, args=[1])
t2 = threading.Timer(0.4, add_rec, args=[2])
t3 = threading.Timer(0.5, add_rec, args=[3])

t1.start()
t2.start()
t3.start()

t1.join()
t2.join()
t3.join()

for rec in Table1.select():
    print(rec.id, rec.n, rec.time_stamp)
```


I spent two weeks trying to get it to work as it used to.

My old code does not specify `autocommit` (it defaults to `True`) and works `with transaction` blocks. So, according to the [documentation](http://docs.peewee-orm.com/en/latest/peewee/database.html#sqlite-and-autocommit), it should still work, but it doesn't.

My previous comment shows an example working with 2.4.4 and failing with later versions.

I do understand why it fails (thanks for the explanation) and I don't understand why it was working. But it was working and now, after wasting two weeks, I'm still stuck, thinking how far I can go without transactions or if I should abandon either sqlite or peewee and restart with something else. Whatever option I chose will require me to change lots of code.

If you think that sqlite + multithreading should not allow the use of transactions and rollback, and the only way to go is `SqliteQueueDatabase`, then that's what I will try.

If you think that I should be able to use transactions with sqlite + multithreading, then I would appreciate an example of how to do it.


I'd just like to clarify: **you can use SQLite with multiple threads in Python, with or without Peewee**. You only need to be aware of the fact that only one connection can write to the database at a time.

> If you think that sqlite + multithreading should not allow the use of transactions and rollback, and the only way to go is SqliteQueueDatabase, then that's what I will try.

**Not at all**.

I **know** you can use transactions with SQLite and multithreading, because I am doing so myself on a handful of websites, services, and scripts.

I had thought that when I was showing examples using "compare-and-set" that the solution was clear, but to re-cap: I believe you can get the "old" behavior by specifying "immediate" as the transaction type. 

In Peewee 2.8.5, the version currently on PyPI, you would write:

``` python
db = SqliteExtDatabase('/my/db')

with db.granular_transaction('immediate') as txn:
    blah blah
```

I've renamed this method in `master` to plain old "transaction", so if you're running on master or a version newer than 2.8.5, just use `transaction`:

``` python
db = SqliteExtDatabase(...)

with db.transaction('immediate') as txn:
    blah blah
```

The caveat here is that you need to be using the `playhouse.sqlite_ext.SqliteExtDatabase`, as this database class implements sqlite-specific features including specifying transaction type.

The other thing to note is that you cannot currently do a similar thing with `atomic()`. This is because only the outer-most transaction can declare a lock type, anything nested below that uses a savepoint.

---

> I don't understand why it was working

I think this piece is crucial to your own feeling of confidence moving forward with whatever solution you end up choosing. I understand that it's complicated to try and figure out what's going on when you have multiple threads, connections, and pysqlite doing it's crazy magic, but it might be worth digging into in your spare time.