sqlalchemy.exceptions.SQLError: (OperationalError) database is locked

[Matthew Newhook]

I'm getting errors in my application.

Error - sqlalchemy.exceptions.SQLError: (OperationalError) database is
locked u'INSERT INTO carts (user_id, "lastModified") VALUES
(?, ?)' [0, None]
Error - sqlalchemy.exceptions.SQLError: (OperationalError) database is
locked u'INSERT INTO carts (user_id, "lastModified") VALUES
(?, ?)' [0, None]

I'm currently using sqlite with pylons 0.9.5, SQLAlchemy 0.3.9 +
pysqlite2.

Evidently this can occur on any database operation. When this occurs
the user, of course, gets an error 500. What is the recommended way to
deal with this? What could be done is to wrap all such database
operations in a while(true) try ... catch sleep; retry block, however,
that seems very tedious and error prone and far better handled by the
frame work. Any sage words?

[Matthew Newhook]

I looked into this problem some more. Since this likely isn't a pylons
problem I followed up in the SQLAlchemy group. See the following link
for details if you are interested.

http://groups.google.com/group/sqlalchemy/browse_frm/thread/78148926d1036284?hl=en

On Oct 30, 11:19 am, Matthew Newhook <matthew.newh...@gmail.com>
wrote:

[Matthew Newhook]

Back to this forum :) For those that care this is similar to a post I
made on the earlier referenced post in the SQLAlchemy forum.

Changing the isolation_level to immediate stopped the deadlock from
occurring. However, another problem comes up very soon.

When testing my cart I was noticing that its possible to get multiple
items of the same item in the cart if I really hammer at the
"add cart" button (instead of a single item with a quantity > 1). Each
of these are an AJAX call and therefore I think can processed prior to
the previous call returning. What happens is:
- I load the shopping cart (and other stuff)
- I start a transaction
- I check to see if the item is already in the cart. If its there I
increment the quantity, if not I add a new item to the cart.
- I commit the transaction

What can I suspect is the addCart can be called concurrently -- the
transaction protects the database lock, but not the now cached data in
the in-memory model. This also likely caused the removeCart issue that
occurs above since it probably results in removing the same cart item
twice causing an error when one of them is flushed to the database.

So what is the correct solution here? I don't think that mutex
protection of the cart model itself will work as each copy of the cart
is presumably a new copy of the underlying data. I could, of course,
protect the actual addCart and removeCart method in the web server (as
opposed to the model). However, that sucks since it precludes multi-
threaded access to separate carts which should be possible.

Michael Bayer replied in the SQLAlchemy forum:

"this would seem to me like it should be fixed at the ajax level, or
possibly the level of the webserver where it receives ajax events.
your ajax functionality constitutes a GUI, and most GUIs I've worked
with have a single "event queue" whereby all events are serialized
into a single stream. the nature of the XMLHttpRequest is
asynchronous so it seems like either your ajax library would have to
delay the second ajax request until the first one completes, or the
web server would have to enforce similar behavior (probably easier on
the ajax side)."

I understand where he is coming from, however, I have to say I'm not
very convinced :) It seems to me that in theory at least http requests
can arrive from the same host concurrently and there is little that
can be done to prevent that ... Any thoughts from you guys?