sqlobject thread safety

Question

My python script reads and increments a row attribute. I call this function from 4 different threads.

 def update_row():
      row = myTable.select(myTable.q.id==1, forUpdate=True)[0]
      row.count += 1
      print "thread %s updated count to %s" %(threading.currentThread(),row.count)

 th1 = threading.Thread(target=update_row, )
 th2 = threading.Thread(target=update_row, )
 th3 = threading.Thread(target=update_row, )
 th4 = threading.Thread(target=update_row, )
 print "Before starting threads count=",myTable.get(1).count
 th1.start()
 th2.start()
 th3.start()
 th4.start()

On several runs I observed that the count value does not always get incremented by 4.

My question: Is there any mechanism in sqlobject(other than forUpdate, which does not seem to work for me) to make update operations on the same object thread safe?

I know I can simply use threading.Lock() in the update_row() function for serialization, but I want to avoid it.

Additional info regarding env: Underlying database is MySql, python 2.7, sqlobject ver 1.5

score 4 · Answer 1 · answered Mar 18 '14 at 09:45

Found the answer after much googling:
It was previously not working for me because the underlying mysql table was using MyISAM engine instead of InnoDB engine. MyISAM does not support transactions and row level locking.

def update_row():
      try:
          trans = conn.transaction()
          row = myTable.select(myTable.q.id==1, connection=trans, forUpdate=True)[0]
          print "thread %s select done:"%threading.currentThread(),row.count
          row.count += 1
          print "thread %s updated count:"%threading.currentThread(),row.count
      except Exception, fault:
          print str(fault)
          trans.rollback()
      else:
          trans.commit()

sqlobject thread safety

1 Answers1