sqlalchemy.exceptions.SQLError: (OperationalError) database is locked
Matthew Newhook
no response.
http://groups.google.com/group/pylons-discuss/browse_frm/thread/093ec04b48e49c3c?hl=en#
I've been looking into this problem a little more. From my reading of
the SQLite documentation there should be a 5 second timeout by default
(I'm using pysqlite 2.3.2). Looking at the source this looks correct.
However, in testing my app when I get this exception there is
definitely no 5 second timeout! It happens immediately. I also tried
500 seconds, and had no better luck. When I concurrently access my
webapp I immediately get this exception.
Also the exception confuses me. The 'database is locked' text
indicates that the error is SQLITE_LOCKED which is documented as:
SQLITE_LOCKED
This return code is similar to SQLITE_BUSY in that it indicates
that the database is locked. But the source of the lock is a recursive
call to sqlite_exec. This return can only occur if you attempt to
invoke sqlite_exec from within a callback routine of a query from a
prior invocation of sqlite_exec. Recursive calls to sqlite_exec are
allowed as long as they do not attempt to write the same table.
However, the documentation indicates that I should be getting
SQLITE_BUSY or IOERROR.
Anyone have any ideas how to solve this problem?
Michael Bayer
second step would be to create a test program illustrating the
behavior using pysqlite only (sqlalchemy doesn't have anything to do
with sqlite lock timeout issues). if you can confirm that the
timeout isnt working in that case, you can submit a bug report to
pysqlite and/or sqlite. the sqlite devs are *very* responsive to
issues.
Matthew Newhook
app. Changing the isolation_level to immediate seems to help the
problem, which is good!
> task.mapper.save_obj(task.polymorphic_tosave_objects, trans)
Module sqlalchemy.orm.mapper:1201 in save_obj
... some variables ...
rec (<coffeeshop.models.CartItem object at 0x1a3b750>,
{'cart_items_id': 12, 'quantity': 2}, <...;sqlalchemy.orm.map
rows 0
t table table_to_mapper update [(<coffeeshop.models.CartItem
object at 0x1a3b750>, {'cart_items_id': 12, 'quantity':
2}, ...<sqlalchemy.orm.map
per.Mapper object at 0x161b470>)]
updated_objects set([<coffeeshop.models.CartItem object at
0x1a3b750>])
<class 'sqlalchemy.exceptions.ConcurrentModificationError'>: Updated
rowcount 0 does not match number of objects updated 1
The SQL echo is:
$ cat /f/t
2007-11-02 18:46:17,556 INFO sqlalchemy.engine.base.Engine.0x..b0
BEGIN
2007-11-02 18:46:17,557 INFO sqlalchemy.engine.base.Engine.0x..b0
SELECT cart_items.product_id AS cart_items_product_id,
cart_items.price AS cart_items_price, cart_items.id AS cart_items_id,
cart_items.cart_id AS cart_items_cart_id, cart_it
ems.quantity AS cart_items_quantity
FROM cart_items
WHERE cart_items.id = ? ORDER BY cart_items.oid
2007-11-02 18:46:17,557 INFO sqlalchemy.engine.base.Engine.0x..b0
[u'12']
2007-11-02 18:46:17,559 INFO sqlalchemy.engine.base.Engine.0x..b0
COMMIT
2007-11-02 18:46:18,518 INFO sqlalchemy.engine.base.Engine.0x..b0
UPDATE cart_items SET quantity=? WHERE cart_items.id =
?
2007-11-02 18:46:18,519 INFO sqlalchemy.engine.base.Engine.0x..b0 [2,
12]
2007-11-02 18:46:18,519 INFO sqlalchemy.engine.base.Engine.0x..b0
ROLLBACK
Debug at: http://192.168.2.34:5432/_debug/view/1194000220
That doesn't look good! The code in question is something like:
try:
if c.cart.remove(id):
c.cart.flush()
transaction.commit()
except:
transaction.rollback()
raise
Its crapping out on the transaction.commit().
Regards, Matthew
Matthew Newhook
What I meant to say was that changing the isolation_level to immediate
helped with the problem in that its more difficult (but not
impossible, of course) to get a deadlock error as before. However, now
I've run into a new problem. The details follow in my original post.
Regards, Matthew
Matthew Newhook
causing this problem. What tipped me off is I am seeing multiple
copies of the same item in my shopping 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.
Any ideas?
Regards, Matthew
Gerhard Häring
> I posted this message in the pylons group but as of yet have received
> no response.
I saw it there and followed here. And subscribed while I'm at it - I'm
playing with SQLAlchemy myself recently.
> http://groups.google.com/group/pylons-discuss/browse_frm/thread/093ec...
>
> I've been looking into this problem a little more. From my reading of
> the SQLite documentation there should be a 5 second timeout by default
> (I'm using pysqlite 2.3.2). [...]
Please update to the latest pysqlite (version 2.3.5). I've improved
pysqlite's concurrency with 2.3.4 by deferring the implicit BEGIN/
COMMITs.
This was the problem and the patch: http://initd.org/tracker/pysqlite/ticket/184
-- Gerhard
Matthew Newhook
following test:
$ cat /f/t.py
import sqlite3
c1 = sqlite3.connect("db", timeout=5.0)
try:
c1.execute("create table person(lastname, firstname)")
c1.execute("insert into person values('newhook', 'matthew')")
c1.commit()
except sqlite3.OperationalError, e:
pass
c1 = sqlite3.connect("db", timeout=5.0, isolation_level="immediate")
c2 = sqlite3.connect("db", timeout=5.0, isolation_level="immediate")
c1.execute("insert into person values('newhook', 'matthew')")
c2.execute("insert into person values('newhook', 'matthew')")
While I understand why this deadlocks without BEGIN IMMEDIATE (of
course, this would always deadlock -- but this deadlocks immediately
as opposed to after 5 seconds). The documentation, imo, doesn't really
make it obvious:
timeout - When a database is accessed by multiple connections, and one
of the processes modifies the database, the SQLite database is locked
until that transaction is committed. The timeout parameter specifies
how long the connection should wait for the lock to go away until
raising an exception. The default for the timeout parameter is 5.0
(five seconds).
This isn't true of course as it depends on the isolation.
I verified with the next test that things do work correctly:
$ cat t1.py
import sqlite3
con = sqlite3.connect("db", timeout=5.0)
try:
con.execute("create table person(lastname, firstname)")
con.execute("insert into person values('newhook', 'matthew')")
con.commit()
except sqlite3.OperationalError, e:
pass
for row in con.execute("select * from person"):
print row
import threading
import thread
class TestThread(threading.Thread):
def __init__(self):
threading.Thread.__init__(self)
self._stop = False
self._lock = thread.allocate_lock()
self._count = 0
def run(self):
while True:
self._lock.acquire()
if self._stop:
self._lock.release()
return
self._lock.release()
con = sqlite3.connect("db", timeout=5.0,
isolation_level="immediate")
con.execute("insert into person values('newhook',
'matthew')")
con.commit()
time.sleep(0.1)
self._count = self._count + 1
def count(self):
return self._count
def stop(self):
self._lock.acquire()
self._stop = True
self._lock.release()
t1 = TestThread()
t2 = TestThread()
t1.start()
t2.start()
import time
time.sleep(2)
t1.stop()
t2.stop()
t1.join()
t2.join()
print "t1._count=%d" % t1.count()
print "t2._count=%d" % t2.count()
Regards, Matthew
Michael Bayer
On Nov 2, 2007, at 7:48 AM, Matthew Newhook wrote:
>
> Sorry for the continuing post spam... However, I think I know what is
> causing this problem. What tipped me off is I am seeing multiple
> copies of the same item in my shopping 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.
>
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).
Matthew Newhook
to see what those guys have to say.
I don't think this is really an AJAX specific problem - although the
asynchronous nature of AJAX means its more likely to occur. It seems
to me that any http request can arrive concurrently at the web server
from the same web browser sharing the same token or session -- unless
the app server (pylons) in this instance serialized access to the
controller under these circumstances then concurrent access to the
same data model could easily occur. Perhaps SQLAlchemy could
optionally cache any in-memory models and return the same instance to
multiple threads so that local serialization on the model could take
place?
On Nov 2, 10:29 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> 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).- Hide quoted text -
>
> - Show quoted text -
Michael Bayer
On Nov 2, 2007, at 11:03 AM, Matthew Newhook wrote:
>
> I've posted a synopsis of this on the earlier referenced pylons thread
> to see what those guys have to say.
>
> I don't think this is really an AJAX specific problem - although the
> asynchronous nature of AJAX means its more likely to occur. It seems
> to me that any http request can arrive concurrently at the web server
> from the same web browser sharing the same token or session -- unless
> the app server (pylons) in this instance serialized access to the
> controller under these circumstances then concurrent access to the
> same data model could easily occur. Perhaps SQLAlchemy could
> optionally cache any in-memory models and return the same instance to
> multiple threads so that local serialization on the model could take
> place?
I'd have to disagree here; SQLAlchemy is a database access library,
its not a caching or application framework. Providing a thread-
synrchronized caching container would be the job for Pylons or some
other third party software (or roll your own). Also this solution
wouldn't work for application models that are non-threaded (like
process-split models); out-of-sync requests would still hit a locked
database. The two ways to fix this would be to either serialize GUI
events at the AJAX level, or to get the database to allow graceful
locking.
I do think this is primarily an AJAX-specific issue, at least within
the field of web applications; the closest analogy for a non-AJAX web
application is the "double post" issue, which is not nearly as
prevalent as this one is since while a single non-ajax webpage by
definition should issue only one POST (and simple tokens can be used
to prevent a second post), a single ajax webpage can issue any number
of requests in any order, without the previous requests having yet
completed.
Matthew Newhook
> I'd have to disagree here; SQLAlchemy is a database access library,
> its not a caching or application framework. Providing a thread-
> synrchronized caching container would be the job for Pylons or some
> other third party software (or roll your own). Also this solution
> wouldn't work for application models that are non-threaded (like
> process-split models); out-of-sync requests would still hit a locked
> database.
Ok, after thinking some more I agree with the above. I'm not really
sure whether this can be fixed in general, however, at the AJAX or web
server level effectively.
If I started the transaction earlier and upgraded it to a write
transaction when I initially load the cart then I could ensure that
this failure scenario wouldn't occur. Is that currently possible?