Row locking bottleneck

Apologies if this question isn't terribly theoretical, but it must be a common database problem, and I'd appreciate some ideas on this one. And besides, it makes a change from 'how do I do X in SQL'...

I have an application which, amongst other things, transfers money between several user accounts and a handful of 'broker' accounts.

The account table looks something like:

table ACCOUNT
acct_id serial
balance numeric(12,2)
owner char(1) -- either User or Broker

Naturally, all payments in and out of this account are done transactionally, and are also journalled in another table. This means that a broker row will be locked whenever money is paid in/out of it to/from a user. Also, the user cannot be given his purchase until the money has been transferred, so the row may be locked for around 0.l to 1 seconds per payment. I doubt that this can be improved significantly.

This design worked fine when the number of concurrent payments per broker was no more than a few a second.

However, once volumes rose (up to 10-20 per second), this became something of a bottleneck. The solution suggested by Informix was to give each broker multiple accounts so that only one would be locked at a time.

This worked fine, but it means that the broker's balance is now the sum of his balances across all his accounts.

I now need to enforce the constraint that the broker's balance must always be greater than or equal to zero. Presumably, if I try and do this across multiple accounts then they will all need locking, and so I'm back to square one, with a single account per broker.

How else can I enforce this constraint, ensure transational integrity and handle the required number of payments a second?

Thanks in advance,
Kieran Elby Received on Mon Apr 22 2002 - 18:46:17 CEST