[Twisted-Python] laxdb - an async dbapi wrapper

[Matt Goodall]

Christopher Armstrong wrote:
> On 9/1/05, Itamar Shtull-Trauring <itamar at itamarst.org> wrote:
> 
>>On Wed, 2005-08-31 at 13:31 +0100, Matt Goodall wrote:
>>
>>
>>>A while back, I started messing around with making a standard, blocking
>>>db-api module appear non-blocking but with a deferred API. I called it
>>>laxdb and it's in my sandbox.
>>>
>>>  svn://svn.twistedmatrix.com/svn/Twisted/sandbox/mg/laxdb.py
>>>  http://svn.twistedmatrix.com/cvs/sandbox/mg/laxdb.py?view=markup
>>
>>I get the impression from your example:
>>
>>    def connected(conn):
>>        curs = conn.cursor()
>>        d = curs.execute("select * from test")
>>        d.addCallback(lambda ignore: curs.fetchall())
>>        d.addCallback(lambda rows: pprint(rows))
>>        # ...
>>
>>that you keep dispatching to a thread, then passing it back to Twisted
>>thread, repeatedly, unlike runInteraction which only does this once. I
>>would guess that this will slow down complex database interactions
>>somewhat.
> 
> 
> Yeah, but the usual way to use adbapi is the same, isn't it? i.e.,
> runQuery calls. Sounds like you're comparing apples to oranges (on the
> other hand, I guess it would be nice if lax-db had a runInteraction).
> 

Yes, adbapi dispatches to a thread but runInteraction can be used to
keep the number of dispatches to a minimum by allowing you to perform
multiple db operations in one go.

I guess laxdb could have something equivalent to runInteraction that
would let you work with the blocking database objects rather than the
wrappers. I don't think it should ever automatically commit or rollback
(unless explicitly told to, perhaps) like adbapi does.

A laxdb runInteraction might even make naturally sequential database
code easier to read and maintain too - it can get a bit messy at times.


I really see two main benefits of laxdb:

  1. It's just like dbapi only async. Of course, with that comes the
     "complexity" of deferreds.
  2. It gives you tight control over transactions.


Let me give some examples of point 2 ...


When you need multiple bits of information to process something, i.e. a
web request, you have a choice with adbapi:

  1. call runQuery multiple times, in a deferred chain or as a
     DeferredList, and collect all the bits together
  2. call runInteraction once, collect all the bits during the called
     function and return them all from the function as a tuple or some
     other object

Option 1 is not really a good choice. Not only can it get very ugly but,
critically, each of the runQuery calls happens in a different
transaction! It's not difficult to see how that could cause problems for
 some of the bits of ACID, especially consistency and isolation.

Option 2 only works when you know all the data you need which is not
always feasible because, often, you have unrelated bits of the
application wanting unrelated data.


Another scenario is that you need to SELECT ... FOR UPDATE some data,
use that (locked) data to perform other deferred operation, and update
the locked rows using the data we just collected from the "other
deferred operation".

I believe this is currently impossible with adbapi because it
automatically handles transactions and you can't do deferred stuff from
the runInteraction function because it's not running in the reactor's
thread.


axdb helps in both these scenarios. You can get a connection, do
whatever you want, even mixing database and other async operations, and
commit or rollback when you're complete.


laxdb also allows you to do things like process an entire web request
within a single transaction, if that is a requirement of your application.


Wow, that was a bit of a rant. Hopefully it explains why something like
laxdb may be useful as part of Twisted though. It's just a different way
of working that adbapi.


- Matt

-- 
     __
    /  \__     Matt Goodall, Pollenation Internet Ltd
    \__/  \    w: http://www.pollenation.net
  __/  \__/    e: matt at pollenation.net
 /  \__/  \    t: +44 (0)113 2252500
 \__/  \__/
 /  \	       Any views expressed are my own and do not necessarily
 \__/          reflect the views of my employer.