On Oct 12, 2011, at 7:48 AM, Michael Ellis wrote: > 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!
SQLite does its own locking as well: http://www.sqlite.org/lockingv3.html I'd turn on the ext4 barrier option just for the heck of it; see what happens. http://lwn.net/Articles/283161/ > > ----------- 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().") > -----------------------------------------------------------------
