The table definition & the stored procedure inside, please let me know if there's design problem, I would appreciate any suggestion

atomy(浮尘)

- Table -
ID int
AccountNumber int
ReceivedDate smalldatetime
Credit decimal(9,2)
Debit decimal(9,2)
Balance decimal(9,2)

- Stored Procedure -

CREATE PROCEDURE DoTransaction @AccountNumber INT, @Amount DECIMAL(9, 2)
AS

DECLARE @Credit DECIMAL(9, 2)
DECLARE @Debit DECIMAL(9, 2)
DECLARE @Balance DECIMAL(9, 2)

SET @Balance = 0

IF @Amount > 0
BEGIN
SET @Debit = 0
SET @Credit = @Amount
END
ELSE
BEGIN
SET @Debit = ABS(@Amount)
SET @Credit = 0
END

SELECT TOP 1 @Balance=Balance FROM BrokerTransactions WHERE AccountNumber=@AccountNumber ORDER BY ID DESC
INSERT INTO BrokerTransactions VALUES(@AccountNumber, GETDATE(), @Debit, @Credit, @Balance + @Amount)

(#3787121@0)
Last Updated: 2007-7-6
This post has been archived. It cannot be replied.

You can add these code to a transaction -piglet(迪斯尼fan); 2007-7-6 (#3786829@0)

Thanks for your reply, I'm not good at the database, can transaction guarantee the operation is atomic? Is it good if the statement is "insert into TableA (amount) values( (select top 1 amount from TableA order by xxxx) + 100)" -atomy(浮尘); 2007-7-6 (#3786911@0)

Transaction has to be A(atomic)CID. Whatever your statement is, how many they are, they'll be executed atomically along as they're in one transaction. -piglet(迪斯尼fan); 2007-7-6 (#3787235@0)

order by xxxx 跟 amount 是什么关系？amount 是全表唯一的吗？ -newkid(newkid); 2007-7-6 (#3786895@0)

order by ReceivedDate, the table structure: -ID-ReceivedDate-Amount- Like the bank transaction, you deposit money, then your account balance got updated, the amount is the balance -atomy(浮尘); 2007-7-6 (#3786922@0)

typical design for this case: maintain the balance in accounts table; keep a snapshot of balance in the transaction record. -newkid(newkid); 2007-7-6 {397} (#3786969@0)
So what you do in this procedure is: update the account balance with balance + amount returning the current balance into variable; insert into transactions table with amount and current balance. Concurrent transactions will wait for the lock on accounts table (but the lock is on one row only). If SQL SERVER supports row level lock, this is much better than setting the isolation to SERIALIZABLE.

Do you mean I can use the TABLOCKX when I do the search, then release the lock after transaction? Thank you. -atomy(浮尘); 2007-7-6 (#3787001@0)

you don't need to use locks explicitly. Update will create a lock for you until you commit or rollback the transaction -newkid(newkid); 2007-7-6 (#3787019@0)

既然数据能算出来，那么他本身就是冗余数据，把这个列删了你的问题就解决了。 -maplew2008(call me at 9.1.1); 2007-7-6 (#3786948@0)

I can not delete the history which is needed for later report, below is the scenario. Thanks 2007/07/01 200$ (Deposit) 2007/07/02 200$ + 100$ = 300$ (Deposit) 2007/07/04 300$ - 50$=250$ (Withdraw) 2007/07/05 250$ + 400$ = 650$ (Deposit) -atomy(浮尘); 2007-7-6 (#3786981@0)

明白了。第一个checkin的时候先lock表，完事后release，如果并发，即发现表锁了，就等头一个完事重新来。看看CVS或VSS的机制 -maplew2008(call me at 9.1.1); 2007-7-6 (#3787000@0)

By using TABLOCKX right? thanks. I never used TABLOCKX before, hopefully that won't have dead lock in the procdure -atomy(浮尘); 2007-7-6 (#3787009@0)

you will suffer from low performance if you lock the whole table while only row level lock is needed. -newkid(newkid); 2007-7-6 (#3787029@0)

So you have one amount column or two? In this row: 2007/07/04 300$ - 50$=250$ do you store -50 in another column? If you are getting balance in this way then there's a problem with your design -newkid(newkid); 2007-7-6 (#3787011@0)

Yes, the full table structure is -ID-ReceivedDate-Credit-Debit-Balance -atomy(浮尘); 2007-7-6 (#3787102@0)

what's the indexes on this table? How many records in this table? -ca010406(shasha); 2007-7-6 (#3787253@0)

Index on AccountNumber ReceivedDate. ID is the primary key. Thanks. -atomy(浮尘); 2007-7-6 (#3787270@0)

I read your statement below, for a small table, it is ok, if this table has millions of data, you will have deadlock issue, nobody can have other operations when this one is running. If it is taking too long a time, clients will complain. -ca010406(shasha); 2007-7-6 (#3787295@0)

This table could have millions of records for sure, I don't want to use any lock if possible. But don't know, the table itself is pretty simple, just it will be used by lots of users at the same time. -atomy(浮尘); 2007-7-6 (#3787305@0)

In that case, I am afraid you'd better following the newkid's suggestion as #3787229@0 -ca010406(shasha); 2007-7-6 (#3787313@0)

What about the last try I posted, use one SQL sentence, I'm not sure how the SQL server handles "insert xxx select from'... -atomy(浮尘); 2007-7-6 (#3787315@0)

no difference. -ca010406(shasha); 2007-7-6 (#3787321@0)

Any suggestion is appreciated! -atomy(浮尘); 2007-7-6 (#3787106@0)
The table definition & the stored procedure inside, please let me know if there's design problem, I would appreciate any suggestion -atomy(浮尘); 2007-7-6 {695} (#3787121@0)
- Table -
ID int
AccountNumber int
ReceivedDate smalldatetime
Credit decimal(9,2)
Debit decimal(9,2)
Balance decimal(9,2)

- Stored Procedure -

CREATE PROCEDURE DoTransaction @AccountNumber INT, @Amount DECIMAL(9, 2)
AS

DECLARE @Credit DECIMAL(9, 2)
DECLARE @Debit DECIMAL(9, 2)
DECLARE @Balance DECIMAL(9, 2)

SET @Balance = 0

IF @Amount > 0
BEGIN
SET @Debit = 0
SET @Credit = @Amount
END
ELSE
BEGIN
SET @Debit = ABS(@Amount)
SET @Credit = 0
END

SELECT TOP 1 @Balance=Balance FROM BrokerTransactions WHERE AccountNumber=@AccountNumber ORDER BY ID DESC
INSERT INTO BrokerTransactions VALUES(@AccountNumber, GETDATE(), @Debit, @Credit, @Balance + @Amount)

my suggestion is to keep the current balance in another table: accounts. So you get balance from that table by account id, not from the transactions table. -newkid(newkid); 2007-7-6 {299} (#3787146@0)
This will save you from the overhead of select...top 1...
By updating the balance first, you get your transaction serialized for the same account id (concurrent transactions by other account ids are still parallelized) and no need to worry about locks because database will hold the lock for you.

Thanks but I'm not good at the database, not quite sure how to do it, can you please give me a bit details. -atomy(浮尘); 2007-7-6 (#3787186@0)

you need to create another table: accounts with account id as primary key and current balance and other properties of an account. I believe you already have one, talk to your database guys -newkid(newkid); 2007-7-6 (#3787229@0)

Thanks a lot for your help, just last try "INSERT INTO Transactions VALUES(@AccountNumber, GETDATE(), @Debit, @Credit, (SELECT TOP 1 @Balance=Balance FROM Transactions WHERE AccountNumber=xxx ORDER BY ID) + xxx)", If this won't help, I'll ask them. -atomy(浮尘); 2007-7-6 (#3787263@0)

it probably won't work. You can test in this way: -newkid(newkid); 2007-7-6 {307} (#3787431@0)
run your procedure but don't commit;
open another session, run the procedure with the same account id.
commit both transactions and check the data that you insert.

in oracle the second select won't be blocked so you get the same balance as the first one. I don't know what will happen in sql server.

Specially thank you newkid, your suggestion solves another issue I have which is archiving the transactions table because the actual balance is stored in another table, I don't have the headache to keep the top1 for all the accounts. -atomy(浮尘); 2007-7-6 (#3787672@0)

thank you guys, I'll probably go with the way newkid suggested. Thanks again have a good weekend. -atomy(浮尘); 2007-7-6 (#3787552@0)

@Calgary

The table definition & the stored procedure inside, please let me know if there's design problem, I would appreciate any suggestion

Replies, comments and Discussions:

More Topics