StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Using <a href="http://en.wikipedia.org/wiki/Serializability" rel="nofollow">serializable</a> transaction isolation level should prevent both problems. If one transaction modifies the data that can affect results of previous reads in another transaction, there is a serialization conflict. Only one transaction wins, all others are aborted by the database to be restarted by the client. SQLite does this by locking the whole database, PostgreSQL employs much more complex mechanism (see <a href="http://www.postgresql.org/docs/9.1/static/transaction-iso.html#XACT-SERIALIZABLE" rel="nofollow">docs</a> for details). Unfortunately, there is no portable sqlalchemic way to catch serialization anomaly and retry. You need to write DB-specific code to reliably distinguish it from other errors.</p> <p>I've put up a sample program with two threads concurrently modifying the data (a very basic reproduction of your scheme), running into conflicts and retrying:</p> <p><a href="https://gist.github.com/khayrov/6291557" rel="nofollow">https://gist.github.com/khayrov/6291557</a></p> <p>With Pyramid transaction middleware and Zope transaction manager in use that would be even easier. After catching serialization error, instead of retrying manually, raise <code>TransientError</code> and middleware will retry the whole request up to <code>tm.attempts</code> (in the paster config) times.</p> <pre><code>from transaction.interfaces import TransientError class SerializationConflictError(TransientError): def __init__(self, orig): self.orig = orig </code></pre> <p>You can even write your own middleware sitting below <code>pyramid_tm</code> in the stack that will catch serialization errors and translate them to transient errors transparently.</p> <pre><code>def retry_serializable_tween_factory(handler, registry): def retry_tween(request): try: return handler(request) except DBAPIError, e: orig = e.orig if getattr(orig, 'pgcode', None) == '40001': raise SerializationConflictError(e) elif isinstance(orig, sqlite3.DatabaseError) and \ orig.args == ('database is locked',): raise SerializationConflictError(e) else: raise return retry_tween </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload