Help! I'm running into a serious and puzzling problem running multiple
web2py shell processes against a common database. I'm using web2py
1.97 and sqlite3 under Linux Mint 10 with an ext4 file system. In a
nutshell, the problem is that I'm getting various DatabaseErrors when
one process attempts to read while another is inserting or updating. I
was under the impression that the DAL would apply the necessary locks
to prevent this kind of conflict but it's not working out very well.
I've made sure all my inserts and updates are wrapped in code (see
below) that performs rollbacks and retries. Do I also need to
manually lock the db (and if so, how)?
One puzzling aspect of this problem is that while it occurs repeatably
on different machines running Linux, I do not see it when testing the
same code under OS X.
Any advice appreciated!
----------- ERROR EXAMPLE------------------
In this example process id 20180 has entered the update routine and process id
19980 attempted a query that failed when SQLite reported a "malformed" error.
The update, however, succeeded with no errors reported.
---------------------------------------------------------
DEBUG:20180:Entering db_update().
ERROR:19980:Traceback (most recent call last):
File "applications/sunrex/modules/fls_data_handler.py", line 631, in
runMissingReports
create_missing_report_records(earliest, end)
File "applications/sunrex/modules/fls_data_handler.py", line 428, in
create_missing_report_records
rows = db(qry).select(tbl.ALL).as_list()
File "/home/solarmax/web2py/gluon/dal.py", line 5394, in select
return self.db._adapter.select(self.query,fields,attributes)
File "/home/solarmax/web2py/gluon/dal.py", line 1176, in select
rows = response(sql)
File "/home/solarmax/web2py/gluon/dal.py", line 1166, in response
self.execute(sql)
File "/home/solarmax/web2py/gluon/dal.py", line 1251, in execute
return self.log_execute(*a, **b)
File "/home/solarmax/web2py/gluon/dal.py", line 1246, in log_execute
ret = self.cursor.execute(*a,**b)
DatabaseError: database disk image is malformed
DEBUG:20180:Leaving db_update().
------------------------------------------------------
-------- CODE USED TO WRAP ALL INSERTS AND UPDATES -------------
def db_insert(table, data):
"""
Make sure all insert operations go through this routine to
ensure transactional integrity. Table is a DAL table object,
data is a dictionary of keyed values.
"""
## In web2py, updating to id 0 is an insert operation.
db_update(table, 0, data)
def db_update(table, record_id, data):
"""
Make sure all update operations go through this routine to ensure
transactional integrity.
Table is a DAL table object,
record_id is an integer >= 0
data is a dictionary of keyed values.
"""
debug("Entering db_update().")
assert isinstance(table, db.Table)
assert isinstance(record_id, int) and record_id >= 0
assert isinstance(data, dict)
retries = 0
while True:
try:
table[record_id] = data
except:
db.rollback()
if retries > 10:
raise
else:
retries += 1
warning("db_update() failed. Retrying ...")
## Allow interfering process time to complete.
time.sleep(1)
else:
db.commit()
break
debug("Leaving db_update().")
-----------------------------------------------------------------
