Re: Row locking bottleneck

CREATE TABLE Accounts -- you have more than one account (acct_id INTEGER NOT NULL PRIMARY KEY, -- avoid non-relational proprietary code
 balance NUMERIC (16,4) NOT NULL

          CHECK (balance > 0.0000), -- isn't 4 decimals required?  owner CHAR(1) NOT NULL -- User or Broker

       CHECK (owner IN ('U', 'B')));

>> 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 ... This design worked fine when the number of concurrent payments per broker was no more than a few a second. <<

I woudl tend to look at a different SQL engine; optimistic concurrency control like InterBase does not have this problem.

>> 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. <<

Can we come up with a "hash code" based on something in the transaction that will push each row of the account onto a different physical page?

CREATE TABLE Accounts -- you have more than one account (acct_id INTEGER NOT NULL,
 acct_id_hash INTEGER NOT NULL,
 balance NUMERIC (16,4) NOT NULL,
 owner CHAR(1) NOT NULL -- User or Broker

       CHECK (owner IN ('U', 'B')),
 PRIMARY KEY (acct_id, acct_id_hash),
 CHECK ((SELECT SUM(balance FROM Accounts AS A1

          WHERE A1.acct_id = Accounts.acct_id) > 0.0000) );

That last CHECK() constraint is legal SQL-92,m but I do not know if Informix will support it. Received on Mon Apr 22 2002 - 17:24:45 CDT