[Twisted-Python] laxdb - an async dbapi wrapper
Hi, In response to the announcement of the Twisted Asynchronous Database Api (tada), <http://www.darkarts.co.za/projects/tada/, I thought I should mention something I wrote. [Unfortunately, tada does not use threads correctly so it's likely to break. The author knows this and has added a warning to the module for now.] 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 laxdb is quite literally an async db-api interface and not a t.enterprise.adbapi style interface. In particular, it provides exactly the same API (but deferred) and it doesn't take over transaction management. It also doesn't wrongly call a cursor, "Transaction" ;-). I recently went back to laxdb and finished it off, also adding a connection pool implementation. Now, I haven't used laxdb in a real project and there are no unit tests but I think it's reasonably complete. It should work with any dbapi module that has threadsafety level 1 or higher, just like adbapi. If there's interest in this way of accessing a database then I would be happy to write tests etc, move it into Twisted and maintain it. Cheers, Matt -- __ / \__ Matt Goodall, Pollenation Internet Ltd \__/ \ w: http://www.pollenation.net __/ \__/ e: matt@pollenation.net / \__/ \ t: +44 (0)113 2252500 \__/ \__/ / \ Any views expressed are my own and do not necessarily \__/ reflect the views of my employer.
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.
Itamar Shtull-Trauring 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.
Yep, I'm sure it will although I don't know how bad the effect is because I have done no timing tests. Has anyone else investigated this sort of issue before? There is actually a comment in the module's docstring about the use of threads: "The amount of thread context switching may hurt laxdb performance." - Matt -- __ / \__ Matt Goodall, Pollenation Internet Ltd \__/ \ w: http://www.pollenation.net __/ \__/ e: matt@pollenation.net / \__/ \ t: +44 (0)113 2252500 \__/ \__/ / \ Any views expressed are my own and do not necessarily \__/ reflect the views of my employer.
On 9/1/05, Itamar Shtull-Trauring <itamar@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). -- Twisted | Christopher Armstrong: International Man of Twistery Radix | -- http://radix.twistedmatrix.com | Release Manager, Twisted Project \\\V/// | -- http://twistedmatrix.com |o O| | w----v----w-+
Christopher Armstrong wrote:
On 9/1/05, Itamar Shtull-Trauring <itamar@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@pollenation.net / \__/ \ t: +44 (0)113 2252500 \__/ \__/ / \ Any views expressed are my own and do not necessarily \__/ reflect the views of my employer.
On Aug 31, 2005, at 8:31 AM, Matt Goodall wrote:
In response to the announcement of the Twisted Asynchronous Database Api (tada), <http://www.darkarts.co.za/projects/tada/, I thought I should mention something I 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.
I think it'd be nice to get something like one of these into twisted to supplement/replace adbapi, but it'd probably be best to make sure that the wrapper for blocking databases and pgasync can use the same interface first. James
James Y Knight wrote:
On Aug 31, 2005, at 8:31 AM, Matt Goodall wrote:
In response to the announcement of the Twisted Asynchronous Database Api (tada), <http://www.darkarts.co.za/projects/tada/, I thought I should mention something I 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.
I think it'd be nice to get something like one of these into twisted to supplement/replace adbapi, but it'd probably be best to make sure that the wrapper for blocking databases and pgasync can use the same interface first.
Well ... I did do that at first but pgasync isn't quite dbapi compatible and it also has a couple of oddities that I'm not sure about. My memory is a bit fuzzy about all this now but the main thing is that pgasync queues queries. There's basically a chain of deferreds internal to a pgasync connection. In some ways, it makes pgasync easier to use, because it looks like stuff is not deferred, but I actually found it irritating after a while. Personally, I think if something is async it might as well look async. There are also a number of bugs (in my opinion) which mean pgasync is not dbapi compatible but those can probably be fixed over time. Anyway, I agree that this would be good but there are a couple of API issues that need solving first before it's possible. Note: I don't think laxdb is an adbapi replacement. adbapi has its advantages but at the cost of losing a bit of control over how and when database operations happen. They're probably just different. - Matt -- __ / \__ Matt Goodall, Pollenation Internet Ltd \__/ \ w: http://www.pollenation.net __/ \__/ e: matt@pollenation.net / \__/ \ t: +44 (0)113 2252500 \__/ \__/ / \ Any views expressed are my own and do not necessarily \__/ reflect the views of my employer.
All this talk of adbapi's has finally prompted us to release our adbapi. It has been in use for about 10 months now on production sites and we consider it stable. It is also somewhat dbapi compliant. Also included in the package is a useful function decorator: defertrap, analagous to twisted's defgen in that it enables generators to yield deferreds, resuming them upon callback and synchronously raising exceptions on errback. Download the package here: http://www.point45.org/download/point45-0.01.tar.gz We put up a trac wiki, which at some point (soon) will be home for the svn repo of these modules. In the mean time it hosts some examples on using the adbapi and defertrap: http://www.point45.org/ As more of our modules become fit for public consumption we'll be adding them to this package. -Mark -- Mark Leonard - mwl@point45.com Technical Director Point 45 Entertainment (Pty) Ltd. office: +27(0)21 4876900 mobile: +27(0)83 7373776
participants (5)
-
Christopher Armstrong
-
Itamar Shtull-Trauring
-
James Y Knight
-
Mark Leonard
-
Matt Goodall