repeated database locked (Operational Error) issues in high activity, db intensive application.

[steelliberty]

Dear Sir :

I am using pony 0.7.1 with PyPy 5.6.0 and sqlite3 in an application that monitors hundreds of sites for being up using ping and http, tcp pings. Status records are generated every second of an hour logging information about the current 'ping.' I have on a frequent basis, an error as shown below.

I have felt comfortable with sqlite3 until recent increases in this error give me concern as to whether there is a better way to operate on a Status record ( I have added the volatility flag to the DB ) or if their is a typical way to wait and try again ( I do try again but always with the same exception) . I understand that poor programming can explain many issues with database access, but I have studied comments from various sources about this situation and I do not see where I am improperly attempting this operation.

I am reluctant to move away from sqlite3 because it is otherwise stable -- hoping to avoid a new collection of issues dealing with another db such as MySQL ..

File "scan_notify.py", line 669, in run
self.checkDownMonitors()
File "<auto generated wrapper of checkDownMonitors() function>", line 2, in checkDownMonitors
File "/tombstone/site-packages/pony/orm/core.py", line 413, in new_func
try: return func(*args, **kwargs)
File "scan_notify.py", line 357, in checkDownMonitors
mainCheck()
File "<auto generated wrapper of mainCheck() function>", line 2, in mainCheck
File "/tombstone/site-packages/pony/orm/core.py", line 413, in new_func
try: return func(*args, **kwargs)
File "scan_notify.py", line 156, in mainCheck
ss = Status.get_for_update(id=newsitesdowndict[nsdhost].id)
File "<auto generated wrapper of get_for_update() function>", line 2, in get_for_update
File "/tombstone/site-packages/pony/utils/utils.py", line 58, in cut_traceback
return func(*args, **kwargs)
File "/tombstone/site-packages/pony/orm/core.py", line 3586, in get_for_update
try: return entity.find_one(kwargs, True, nowait) # can throw MultipleObjectsFoundError
File "/tombstone/site-packages/pony/orm/core.py", line 3670, in find_one
if obj is None: obj = entity.find_in_db(avdict, unique, for_update, nowait)
File "/tombstone/site-packages/pony/orm/core.py", line 3729, in find_in_db
cursor = database._exec_sql(sql, arguments)
File "/tombstone/site-packages/pony/orm/core.py", line 696, in _exec_sql
connection = cache.prepare_connection_for_query_execution()
File "/tombstone/site-packages/pony/orm/core.py", line 1536, in prepare_connection_for_query_execution
except Exception as e: connection = cache.reconnect(e)
File "/tombstone/site-packages/pony/orm/core.py", line 1511, in reconnect
if not provider.should_reconnect(exc): reraise(*sys.exc_info())
File "/tombstone/site-packages/pony/orm/core.py", line 1535, in prepare_connection_for_query_execution
try: provider.set_transaction_mode(connection, cache) # can set cache.in_transaction
File "<auto generated wrapper of set_transaction_mode() function>", line 2, in set_transaction_mode
File "/tombstone/site-packages/pony/orm/dbapiprovider.py", line 53, in wrap_dbapi_exceptions
except dbapi_module.OperationalError as e: raise OperationalError(e)
OperationalError: database is locked

[kozlovsky]

Hi, can you try it with Pony 0.7.2? we fixed some issues with SQLite3 locking, maybe it solves your problem

[steelliberty]

I will do it ... and let you know.

I love PONY .. why would anyone do databases any other way .. surprising that many include db specific code in their python db-applications, when using pony abstracts away the issues with knowing multiple db code. Thanks -- I'll need to let it run a few hours before reporting results .

[steelliberty]

Okay .. it takes a few hours for a circumstance to cause the database lock. It happens during activity created because of sites being down -- The same errors appear again.

Would there be any difference if I used select for_update ?

File "scan_notify.py", line 148, in mainCheck
ss = Status.get_for_update(id=newsitesdowndict[nsdhost].id)
File "<auto generated wrapper of get_for_update() function>", line 2, in get_for_update
File "/tombstone/site-packages/pony/utils/utils.py", line 58, in cut_tombstoneback
return func(*args, **kwargs)
File "/tombstone/site-packages/pony/orm/core.py", line 3593, in get_for_update
try: return entity.find_one(kwargs, True, nowait) # can throw MultipleObjectsFoundError
File "/tombstone/site-packages/pony/orm/core.py", line 3677, in find_one
if obj is None: obj = entity.find_in_db(avdict, unique, for_update, nowait)
File "/tombstone/site-packages/pony/orm/core.py", line 3736, in find_in_db
cursor = database._exec_sql(sql, arguments)
File "/tombstone/site-packages/pony/orm/core.py", line 699, in _exec_sql
connection = cache.prepare_connection_for_query_execution()
File "/tombstone/site-packages/pony/orm/core.py", line 1539, in prepare_connection_for_query_execution
except Exception as e: connection = cache.reconnect(e)
File "/tombstone/site-packages/pony/orm/core.py", line 1514, in reconnect
if not provider.should_reconnect(exc): reraise(*sys.exc_info())
File "/tombstone/site-packages/pony/orm/core.py", line 1538, in prepare_connection_for_query_execution
try: provider.set_transaction_mode(connection, cache) # can set cache.in_transaction
File "<auto generated wrapper of set_transaction_mode() function>", line 2, in set_transaction_mode
File "/tombstone/site-packages/pony/orm/dbapiprovider.py", line 53, in wrap_dbapi_exceptions
except dbapi_module.OperationalError as e: raise OperationalError(e)
OperationalError: database is locked

[steelliberty]

or do you believe there is an issue using sqlite3 and because of the moderate increase in database access I should have used MySQL or one of the other DBs ?

[kozlovsky]

This exception may arise if some transaction is very long and cannot be finished before the default timeout of 5 seconds. If this is the case, you can try to increase the timeout value. Unfortunately, Pony does not provide API for that. In the next release we need to add timeout option to bind method when using 'sqlite' provider.

Right now, in order to check that the reason of the problem is insufficiently big timeout value, you can find line 516 of file pony/orm/dbproviders/sqlite.py:

pool.con = con = sqlite.connect(filename, isolation_level=None)

and set a timeout (in seconds) to some value bigger then the default one

pool.con = con = sqlite.connect(filename, isolation_level=None, timeout=1000.0)

On the other hand, if all transactions are short and increasing of timeout value gives no result, then it looks like a bug in Pony, and we need to debug and fix it.

[steelliberty]

sounds good .. I will let you know .. thank you.

…

On Wed, Jul 26, 2017 at 3:05 PM, kozlovsky ***@***.***> wrote: This exception may arise if some transaction is very long and cannot be finished before the default timeout <https://docs.python.org/2/library/sqlite3.html#sqlite3.connect> of 5 seconds. If this is the case, you can try to increase the timeout value. Unfortunately, Pony does not provide API for that. In the next release we need to add timeout option to bind method when using 'sqlite' provider. Right now, in order to check that the reason of the problem is insufficiently big timeout value, you can find line 516 of file sqlite.py <https://github.com/ponyorm/pony/blob/orm/pony/orm/dbproviders/sqlite.py#L516> : pool.con = con = sqlite.connect(filename, isolation_level=None) and set a timeout (in seconds) to some value bigger then the default one pool.con = con = sqlite.connect(filename, isolation_level=None, timeout=1000.0) On the other hand, if all transactions are short, then increasing of timeout value is no help, then it looks like a bug in Pony, and we need to debug and fix it. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#280 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AE4HdlPJL2YcqA38aEbK0_QtUtmtmg38ks5sR43qgaJpZM4Oi1eG> .

[steelliberty]

I changed the timeout to 10000 (10 seconds ) and it has not failed yet. It is not certain however, that was the issue yet , because the activity on the site has been less -- it is dependent on site responses. But .. I noticed a sharp slow down in return times for web-page results, The Web page prints status results, charts with thousand of data points sucked out of the database etc .. Doing say 50+ sites at time used to produce the results in less than a few seconds .. Now it appears to be 10 to 20 times that -- Could this increase in the length of time of the timeout -- cause an overall increase in the time multiple reads are take ? I did not have errors while reading the data before ( Only locked DBs when writing to the DB) -- Anyway, I will watch the site for a week or so to see what I notice -- Likely will bring the value to say 7 seconds and see what happens. Thank you for the help this is an important project for me and our team. jm

…

On Wed, Jul 26, 2017 at 4:24 PM, Jack Mullen ***@***.***> wrote: sounds good .. I will let you know .. thank you. On Wed, Jul 26, 2017 at 3:05 PM, kozlovsky ***@***.***> wrote: > This exception may arise if some transaction is very long and cannot be > finished before the default timeout > <https://docs.python.org/2/library/sqlite3.html#sqlite3.connect> of 5 > seconds. If this is the case, you can try to increase the timeout value. > Unfortunately, Pony does not provide API for that. In the next release we > need to add timeout option to bind method when using 'sqlite' provider. > > Right now, in order to check that the reason of the problem is > insufficiently big timeout value, you can find line 516 of file sqlite.py > <https://github.com/ponyorm/pony/blob/orm/pony/orm/dbproviders/sqlite.py#L516> > : > > pool.con = con = sqlite.connect(filename, isolation_level=None) > > and set a timeout (in seconds) to some value bigger then the default one > > pool.con = con = sqlite.connect(filename, isolation_level=None, timeout=1000.0) > > On the other hand, if all transactions are short, then increasing of > timeout value is no help, then it looks like a bug in Pony, and we need to > debug and fix it. > > — > You are receiving this because you authored the thread. > Reply to this email directly, view it on GitHub > <#280 (comment)>, or mute > the thread > <https://github.com/notifications/unsubscribe-auth/AE4HdlPJL2YcqA38aEbK0_QtUtmtmg38ks5sR43qgaJpZM4Oi1eG> > . >

[sashaaero]

Now it's possible to specify timeout option, as well as other keyword arguments for sqlite3.connect:

db.bind(provider='sqlite', filename='db.sqlite', timeout=10)