"InvalidRequestError: The transaction is inactive due to a rollback..." using sqlite with multiple commits.

[Daniel]

Hello,

In my application I have a function that looks more or less like this

def run(self):
# process first object in sequence
for firstObject in firstObjects:
self.session.add(firstObject)
self.session.commit()

# process second object in sequence
# lots of these, so break along the way
count = 0
for secondObject in secondObjects:
self.session.add(secondObject)
count += 1
if (count > 100):
#**********************************
self.session.commit()
#**********************************
count = 0
time.sleep(1) # pause to let other process access
the db
self.session.commit()

# process third objects
for thirdObject in thirdObjects:
self.session.add(thirdObject)
self.session.commit()

The commit nested inside the second loop (highlighted by asterisks) is
potentially called many times (occasionally there are thousands of
objects to deal with). intermittently that commit will produce the
following error:

Traceback (most recent call last):
File "C:\Aptina\pop\tester\AptinaStagingService.py", line 106, in
__init__
self.run(self.pushPath,self.stagingPath)
File "C:\Aptina\pop\tester\AptinaStagingService.py", line 231, in
run
self.session.commit()
File "c:\python25\Lib\site-packages\sqlalchemy\orm\session.py", line
673, in commit
self.transaction.commit()
File "c:\python25\Lib\site-packages\sqlalchemy\orm\session.py", line
378, in commit
self._prepare_impl()
File "c:\python25\Lib\site-packages\sqlalchemy\orm\session.py", line
351, in _prepare_impl
self._assert_is_active()
File "c:\python25\Lib\site-packages\sqlalchemy\orm\session.py", line
247, in _assert_is_active
"The transaction is inactive due to a rollback in a "
InvalidRequestError: The transaction is inactive due to a rollback in
a subtransaction. Issue rollback() to cancel the transaction.

I've read elsewhere in this group (http://groups.google.com/group/
sqlalchemy/browse_thread/thread/b87af73232998fe4) about this error
message, but I'm not sure what they mean by "squelching the original
exception somewhere". Can someone please help me understand why I'm
getting this error and ideas on how to fix it.

Thanks.

[Michael Bayer]

squelching typically means one of two things.

either you're doing this:

try:
# do stuff with session
except:
print "error !"
# .. keep going

or, you are allowing concurrent access to a single session with
multiple threads, one of your threads is throwing an exception
(usually due to the corrupted state of the session, since the session
is not mutexed) and the other thread gets this error.

[Daniel]

I've learned a bit more. Apparently the sqlite database occasionally
gets locked by another process and that lock lasts longer than the
five second default timeout. SQL Alchemy quietly issues a ROLLBACK,
but doesn't say anything more about it. The result is that the
session is no longer active and eventually produces the error I
mentioned above. Here are the log messages showing the insert failing
after the five second default timeout.

2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70
INSERT INTO table ("id", "value") VALUES (?, ?)
2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70
[1, 'my value']
2009-04-28 15:10:53,148 INFO sqlalchemy.engine.base.Engine.0x...ae70
ROLLBACK

Is there some way to configure SQL Alchemy to raise an exception when
it issues the rollback, or when it finds the database locked? It's a
bit confusing to see a ROLLBACK with no indication of why (and if I
didn't know that the sqlite3 default timeout was five seconds, I might
still be scratching my head).

Meanwhile, my application works with an increased timeout, but that
may change when the system is under high load.

Thanks for your input...

[Michael Bayer]

Daniel wrote:
>
> I've learned a bit more. Apparently the sqlite database occasionally
> gets locked by another process and that lock lasts longer than the
> five second default timeout. SQL Alchemy quietly issues a ROLLBACK,
> but doesn't say anything more about it. The result is that the
> session is no longer active and eventually produces the error I
> mentioned above. Here are the log messages showing the insert failing
> after the five second default timeout.
>
> 2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70
> INSERT INTO table ("id", "value") VALUES (?, ?)
> 2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70
> [1, 'my value']
> 2009-04-28 15:10:53,148 INFO sqlalchemy.engine.base.Engine.0x...ae70
> ROLLBACK
>
> Is there some way to configure SQL Alchemy to raise an exception when
> it issues the rollback, or when it finds the database locked?

SQLAlchemy definitely raises an exception if the DBAPI sends one. a
ROLLBACK does not occur if there was no exception thrown. check if
you're catching it and not re-raising.

[Daniel]

I'm not catching it or re-raising it. Where else could I look to
solve this.

[Michael Bayer]

how is it that you know this is due to the SQLite timeout ? did you
create a test case ? creating a fully reproducible test case would be
the next step.

[Daniel]

Hello Michael,

Thank you for your replies. I've just tried to create a testcase but
proven to myself that it does raise an exception. I'll go back and
double check where I might be catching and passing on the exception.

Daniel