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

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

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