OperationalError: database is locked #127

Closed
apexxxx opened this Issue Jun 6, 2015 · 7 comments

Projects

None yet

5 participants

@apexxxx
apexxxx commented Jun 6, 2015

Hi,
Recently discover a bug with threading and ponyorm.
Here a litle poc : http://pastebin.com/VDgmJpBu

from pony.orm import *
import threading
from datetime import datetime
import time
import random

my_db = Database("sqlite", "my_db.sqlite", create_db=True)
sql_debug(False)


class Log(my_db.Entity):
    id = PrimaryKey(int, auto=True)
    timestamp = Required(datetime)
    message = Required(unicode, sql_type='collate nocase')

my_db.generate_mapping(create_tables=True)

@db_session
def callback_function():
    print 'fu'
    wait = random.randint(0,10)
    print "wait for {}".format(wait)
    Log(timestamp=datetime.now(), message="test")
    time.sleep(wait)
    print 'srodha'
    for i in range(0, 100000):
        Log(timestamp=datetime.now(), message="testFin {}".format(i))

threads = []
for i in range(5):
    t = threading.Thread(target=callback_function)
    threads.append(t)
    t.start()

and here the traceback :
Exception in thread Thread-5:
Traceback (most recent call last):
File "C:\Python27\lib\threading.py", line 810, in bootstrap_inner
self.run()
File "C:\Python27\lib\threading.py", line 763, in run
self._target(_self._args, *_self._kwargs)
File "<auto generated wrapper of callback_function() function>", line 2, in callback_function
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 356, in new_func
finally: self.exit(exc_type, exc, tb)
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 388, in exit
commit()
File "<auto generated wrapper of commit() function>", line 2, in commit
File "C:\Python27\lib\site-packages\pony\utils.py", line 88, in cut_traceback
return func(_args, *_kwargs)
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 284, in commit
transact_reraise(CommitException, exceptions)
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 268, in transact_reraise
reraise(exc_class, new_exc, tb)
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 278, in commit
try: primary_cache.commit()
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 965, in commit
if cache.modified: cache.flush()
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 1030, in flush
if obj is not None: obj.save()
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 4144, in save
if status == 'created': obj.save_created()
File "C:\Python27\lib\site-packages\pony\orm\core.py", line 4048, in save_created
% (obj, e.classname, msg), e)
File "C:\Python27\lib\site-packages\pony\utils.py", line 126, in throw
raise exc
CommitException: Object Log[new:5] cannot be stored in the database. OperationalError: database is locked

(have to remove some double underscore in the traceback to avoid automatic bold here the full traceback http://pastebin.com/XKud32q6)

The bug seems to be thread and time.sleep related.
Sometime the POC doesn't crash. Let me know if you want more informations.
Run with python 2.7.10
Hope you guys could fixe this. Your work is really good and i enjoy to use your product.

@sm0k
sm0k commented Jun 6, 2015

Saw that too. time.sleep() called in threading context seams to be responsible.

@ApexHub
ApexHub commented Jun 8, 2015

i've not the poc but even when i've tried to do a 'real time' sleep function (a sleep function which doesn't sleep (just lot of time check)) the bug appears too. It seems to appear just with two thread and heavy entry in DB. hope this can help more. The doc speak about transaction and explicitly describe there is noway to obtain an operational error http://doc.ponyorm.com/transactions.html#sqlite. maybe the solution to our bug ? have to try with a retry in the DB decorator (@db_session(retry=3)) and tell you soon the result.

@amalashkevich
Member

we are working on the solution, stay tuned ;)

@ApexHub
ApexHub commented Jun 10, 2015

great. thank you to take care of this.

@kozlovsky kozlovsky added the bug label Jun 10, 2015
@kozlovsky kozlovsky added this to the 0.6.2 milestone Jun 10, 2015
@kozlovsky kozlovsky self-assigned this Jun 10, 2015
@kozlovsky kozlovsky closed this in a589ef7 Jun 10, 2015
@ApexHub
ApexHub commented Jun 10, 2015

already ! what a good news. Thank you so much.

@kozlovsky
Contributor

We fixed this bug, your example should work correctly now. Please check and confirm.

As a side notice I think that inserting 100000 objects in a single transaction is a bit too much, because when your transaction do first INSERT or UPDATE the SQLite database becomes locked and all concurrent transactions will wait pretty long time until all this 100000 objects are inserted. If atomicity of inserting of 100000 objects at once is not important, it may be better to do commit every 1000 or 10000 objects in order to give concurrent transactions a chance to get write access to the database. It may be done by adding manual commit in your loop:

    for i in range(0, 100000):
        Log(timestamp=datetime.now(), message="testFin {}".format(i))
        if i % 10000 == 0:
            commit()
@ApexHub
ApexHub commented Jun 10, 2015

Of course those 100000 input was for the poc purpose. In my case, it was just with few heavy sql statement that the bug append. Will tell you soon if it work find. Thank you for the commit advice.

@kozlovsky kozlovsky added a commit that referenced this issue Jan 11, 2016
@kozlovsky kozlovsky Pony ORM Release 0.6.2 (2015-01-11)
The documentation was moved from this repo to a separate one at https://github.com/ponyorm/pony-doc
The compiled version can be found at https://docs.ponyorm.com

# New features

* Python 3.5 support
* #132, #145: raw_sql() function was added
* #126: Ability to use @db_session with generator functions
* #116: Add support to select by UUID
* Ability to get string SQL statement using the Query.get_sql() method
* New function delete(gen) and Query.delete(bulk=False)
* Now it is possible to override Entity.__init__() and declare custom entity methods

# Backward incompatible changes

* Normalizing table names for symmetric relationships
* Autostrip - automatically remove leading and trailing characters

# Bugfixes

* #87: Pony fails with pymysql installed as MySQLdb
* #118: Pony should reconnect if previous connection was created before process was forked
* #121: Unable to update value of unique attribute
* #122: AssertionError when changing part of a composite key
* #127: a workaround for incorrect pysqlite locking behavior
* #136: Cascade delete does not work correctly for one-to-one relationships
* #141, #143: remove restriction on adding new methods to entities
* #142: Entity.select_random() AssertionError
* #147: Add 'atom_expr' symbol handling for Python 3.5 grammar
ab09f64
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment