DAL without web2py

Question

I am using web2py to power my web site. I decided to use the web2py DAL for a long running program that runs behind the site. This program does not seem to update its data or the database (sometimes).

from gluon.sql import *
from gluon.sql import SQLDB

from locdb import * 
# contains
# db = SQLDB("mysql://user/pw@localhost/mydb", pool_size=10)
# db.define_table('orders', Field('status', 'integer'), Field('item', 'string'),
#    migrate='orders.table')
orderid = 20 # there is row with id == 20 in table orders
#when I do 
db(db.orders.id==orderid).update(status=6703)
db.commit()

It does not update the database, and a select on orders with this id, shows the correct data. In some circumstances a "db.rollback()" after a commit seems to help.

Very strange to say the least. Have you seen this? More importantly do you know the solution?

---

UPDATE:
Correction: The select in question is done within the program, not outside it.

Sometimes, when doing a series of updates, some will work and be available outside and some will not be available. Also some queries will return the data it originally returned even though the data has changes in the DB since th4 original query.

I am tempted to dump this approach and move to another method, any suggestions?

Answer 1

This problem has been resolved: mysql runs at isolation level REPEATABLE READ (that is, once the transaction starts, the data reflected in the select output will not change till the transaction ends). It needed changing the isolation level to READ COMMITED and that resolved the issue. By the way READ COMMITED is the isolation level at which Oracle and mssql run by default. This can be set in the my.cnf. Details in the link below:

http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html