On Jun 18, 2008, at 6:23 AM, Harish wrote:
>
> Hello,
>
> I am new to SQL Alchemy and this is a terrific tool for my project. I
> am facing problems when I run a multi threaded application. The
> situation is as below :
>
> I have a table in the DB to generate a unique key everytime I request
> for it. DB.py is the file which interfaces with the database. The code
> to get object ID is below :
>
> def getObjId(self):
> #ObjId is not Persistent
>
> DB.SessionLock.acquire()
> g = ''
>
> print "Currently Servicing : %s" %
> threading.currentThread().getName()
> if len(self.session.query(GlobalObjectID).all()) == 0:
> print "First Time"
> g =
> GlobalObjectID(GlobalObjectID.START_VAL)
>
> #If persistent in DB, Increment, Update and return.
> else :
> print "Querying DB"
> g = self.session.query(GlobalObjectID).one()
> print "Got : %d" % g.getCurObjId()
> g.setCurObjId(g.getCurObjId() + GlobalObjectID.INCR_VAL)
>
> print g
> #Update DB. Make the Object persistent in DB
> try:
> print repr(self.session.identity_map.values())
> self.session.save_or_update(g)
> print repr(self.session.identity_map.values())
> self.session.commit()
> print repr(self.session.identity_map.values())
> except:
> printException("Error Saving ObjectID Counter to
> DB!",False)
> self.session.rollback()
> os.system("pause")
> DB.SessionLock.release()
> return -1111
>
> DB.SessionLock.release()
> return g.getCurObjId()
>
>
> (DB is the class where this method is defined).
> GlobalObjectID is a class linked with globalobjectid table in the
> database. From my test code, I create multiple threads, each of which
> gets the same DB object (say dbo) and they print dbo.getObjecdtId 10
> times.
>
> After a while, I start getting exceptions during session.commit :
>
> KeyError: (<class 'Model.GlobalObjectID'>, (1078,), None).
>
> I dont understand why, because the critical operation of fetching an
> object ID from dB, Incrementing it and Saving it back is under a lock.
> Also, I dont understand what does it mean, when I see objects in
> session.identity_map.values() and dont see anything in session.new or
> session.dirty or session.deleted. What other state could the object
> be?
>
> The session above is a transactional session with autoflush set to
> True. Is there anything wrong with the code above? please let me know.
if there is any access to that Session, or any objects loaded from
that Session which are still attached, outside of your critical
section, then the Session is being accessed in a non-threadsafe
manner, and because you have autoflush set to True, a flush could
occur anytime the database is queried, including when an attribute is
lazily loaded upon first access. It is strongly recommended not to
share Sessions between threads.
The locking you're doing here would be better served using database
level locking; in this case, you can just issue a SELECT...FOR UPDATE
which will lock your "guid" row upon read, thereby making it safe
against any other thread or process for when you next issue an
UPDATE. You can do this with the ORM but its probably simpler to use
direct SQL expressions, such as:
def nextid():
conn = engine.connect()
trans = conn.begin()
try:
id = conn.execute(select([mytable.c.id],
for_update=True)).scalar()
id += 1
conn.execute(mytable.update().values(id=id))
trans.commit()
return id
except:
trans.rollback()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
