[Twisted-Python] Newbie - how to modify my db-intensive app to use Twisted

[Frank Millman]

Hi all

I sent a post recently which explains my situation. I will recap briefly.

I am developing a multi-user accounting/business app, which is database
intensive. My current version runs on each client, and makes a direct
connection to the database. I want to change it to a server-based solution,
where the server connects to the database, and each client connects to the
server. Twisted lends itself to handling communication between the server
and client, and to queuing all the database commands.

I have spent some time trying to figure out how to change my program to work
in a Twisted fashion. I have made some progress, but now I have hit a
stumbling block. I am sure that the problem is more to do with my approach
than with Twisted, but it feels serious enough that I am leaning towards
abandoning this approach and reverting to a multi-threaded server. I don't
really want to do this, so I will explain my problem in the hope that
someone can suggest an easy solution, which does not involve a total rethink
of what I have come up with so far.

As mentioned above, my program is database intensive. I have built in the
ability to handle multiple table relationships, such as 'a customer record
must have a valid branch code, currency code, salesperson code, etc'. If the
user attempts to add or alter any of these fields, the program automatically
checks that the new value exists as a foreign key on the relevant table. For
this reason, and for a variety of other reasons, the program executes many
SQL commands 'behind the scenes', without the application layer being aware
of it. Each of these 'blocks' the program, but as the program runs on each
client, it does not matter, as the user has to wait for a response before
they can continue anyway.

Moving this concept onto Twisted does not seem to work, as it can be running
sessions for multiple users, and each 'block' will block the main loop,
resulting in a major performance hit. The correct solution, as I understand
it, is to change each SQL command to a deferred with a callback. I can do
this on the inner layers, where the actual SQL command is executed. But I
would also have to do it on each outer layer that calls a function which
'may' trigger a SQL command. There are many of these, and it goes to the
heart of my approach of hiding the underlying database complexity from the
application layer. Changing this feels like a daunting task, and frankly I
would not know where to start.

The alternative is to use a multi-threaded approach. In this case, I can
keep my existing approach largely unchanged, as it does not matter if one
thread blocks, the others will continue without being affected.

I don't know if I have explained myself very clearly, but if anyone has any
words of advice, I will be very grateful.

Thanks

Frank Millman