March 19, 2008 at 3:16 am
I am storing the balance of all teams in a sql table named TeamBalance. Table structure is
TeamId Balance
1 1000
2 2000
Now if a user belonging to Team 1 makes a purchase of 100 then the Balance of Team 1 would be 1000-100=900. so 900
would be updated in the Balance column for Team 1
Now what happens if for Team 2 if 2 or more users of team 2 at a same time make a purchase of 700 and 1500 respectively.
How can I handle this concurrency issue.
Thanks.
March 19, 2008 at 3:55 am
znkin (3/19/2008)
I am storing the balance of all teams in a sql table named TeamBalance. Table structure isTeamId Balance
1 1000
2 2000
Now if a user belonging to Team 1 makes a purchase of 100 then the Balance of Team 1 would be 1000-100=900. so 900
would be updated in the Balance column for Team 1
Now what happens if for Team 2 if 2 or more users of team 2 at a same time make a purchase of 700 and 1500 respectively.
How can I handle this concurrency issue.
Thanks.
Assuming you have the default isolation level of read committed you'll be fine and you shouldn't need to do anything.
I'm assuming that the update statement is something like this:
update TeamBalance
set Balance = Balance - @Purchase
where TeamId = @TeamId
So, when your first user runs the above update statement the resource will be locked, blocking the second user from running their update statement. Only when the first transaction commits will the second user be allowed to proceed with their update statement.
March 19, 2008 at 6:10 am
Thanks for your reply
Before I do the update query I am checking if the Balance is greater or equal to the Purchase amount
select @Balance = Balance from TeamBalance where TeamId = @TeamId
if @Balance >= @Purchase
begin
update TeamBalance
set Balance = Balance - @Purchase
where TeamId = @TeamId
end
So what I was just wondering that if two or more users do a purchase at a same time(with user1 doing 1500 and user2 doing 700) then the select query for all of them would be true(assuming the balance is 2000) and so the balance would then be updated and set to a negative value(which should not happen).
Would this be taken care of by SQL itself.
March 19, 2008 at 6:21 am
In this case you'd need to ensure that you maintain the lock on TeamBalance when you do the select statement.
The updlock tells SQL Server to take out an update lock on the TeamBalance table rather than a shared lock and the holdlock tells SQL Server to hold the lock until the end of the transaction.
In that way, the second user will be blocked from running its select statement on TeamBalance until the first user commits its transaction.
Don't forget to add error handling! 🙂
begin tran
select @Balance = Balance from TeamBalance with (updlock, holdlock) where TeamId = @TeamId
if @Balance >= @Purchase
begin
update TeamBalance
set Balance = Balance - @Purchase
where TeamId = @TeamId
end
commit tran
znkin (3/19/2008)
Thanks for your replyBefore I do the update query I am checking if the Balance is greater or equal to the Purchase amount
select @Balance = Balance from TeamBalance where TeamId = @TeamId
if @Balance >= @Purchase
begin
update TeamBalance
set Balance = Balance - @Purchase
where TeamId = @TeamId
end
So what I was just wondering that if two or more users do a purchase at a same time(with user1 doing 1500 and user2 doing 700) then the select query for all of them would be true(assuming the balance is 2000) and so the balance would then be updated and set to a negative value(which should not happen).
Would this be taken care of by SQL itself.
March 21, 2008 at 2:29 am
Thanks for the reply...
Instead of doing an Update query if I write a insert query will the same work. And also do I need to ensure some kind of isolation level for the above.
Secondly I was wondering if I did not have any locks will sql take care of it on its own.
March 21, 2008 at 3:25 am
znkin (3/21/2008)
Thanks for the reply...Instead of doing an Update query if I write a insert query will the same work. And also do I need to ensure some kind of isolation level for the above.
Secondly I was wondering if I did not have any locks will sql take care of it on its own.
I'm not sure how you intend to replace the update with an insert statement but essentially the same rules apply. The important statement is the select statement because SQL Server will ordinarily only apply shared locks on the select statement for the duration of that statement (even though it's in a transaction). You could change the isolation level but in essence, that's what you're doing by applying your own locking hints to the select statement.
This answers your second question. Without applying your own locking hints SQL Server won't take care of things because the default isolation level (read committed) will allow other processes to run the select statement once the first process has finished with it.
So your options are to either change the isolation level or apply your own locking hints to the select statement.
Hope that helps.
March 21, 2008 at 4:10 am
Thanks for your help.
What I meant was instead of storing the balance in a table if I am just inserting the purchase transaction in a table. So everytime before I insert the purchase I calculate the balance on the fly before inserting the purchase.
example
begin tran
select @Balance = sum(Amount) from PurchaseMaster with (updlock, holdlock) where TeamId = @TeamId
if @Balance >= @Purchase
begin
Insert into PurchaseMaster values(.........)
end
commit tran
I think you meant that the above would also work. I had another doubt was that if an error occured after the select query do I need to remove the lock, if yes then how.. I mean
If @@error <> 0
begin
end
March 21, 2008 at 6:24 am
I see. The same still applies, so yes it would work fine as you've described in the example.
Regarding the error handling, to release the locks you'd have to make sure that the transaction is rolled back. The locks remain in place until the transaction is committed or rolled back.
So you'd do something like:
if @Balance >= @Purchase
begin
Insert into PurchaseMaster values(.........)
if @@error > 0
begin
if @@trancount > 0
begin
rollback tran
end
return
end
end
It's always worth checking the @@trancount value to make sure that there's something to rollback.
If you're running this inside a stored procedure you need to exit from the stored procedure by using the return statement. If you don't do this then any T-SQL commands after the rollback tran statement will still be processed. In your example the commit tran statement would be processed but that would raise an error, seeing as the transaction has been rolled back.
Of course there might be instances where you'd want to keep on going despite rolling back the transaction. In those instances you'd need to build some more logic into the code.
znkin (3/21/2008)
Thanks for your help.What I meant was instead of storing the balance in a table if I am just inserting the purchase transaction in a table. So everytime before I insert the purchase I calculate the balance on the fly before inserting the purchase.
example
begin tran
select @Balance = sum(Amount) from PurchaseMaster with (updlock, holdlock) where TeamId = @TeamId
if @Balance >= @Purchase
begin
Insert into PurchaseMaster values(.........)
end
commit tran
I think you meant that the above would also work. I had another doubt was that if an error occured after the select query do I need to remove the lock, if yes then how.. I mean
If @@error <> 0
begin
end
March 21, 2008 at 7:36 am
Just curious, but what is wrong with the following code:
update dbo.TeamBalance set
Balance = Balance - @Purchase
where
TeamId = @TeamId
and Balance >= @Purchase
😎
March 21, 2008 at 7:48 am
Lynn Pettis (3/21/2008)
Just curious, but what is wrong with the following code:
update dbo.TeamBalance set
Balance = Balance - @Purchase
where
TeamId = @TeamId
and Balance >= @Purchase
😎
Good point Lynn! On reflection, unless there's need for other logic in between the select and the update statement this is much neater - and you wouldn't need to worry about locking hints.:)
March 21, 2008 at 9:32 am
I like Lynn's approach, but you do need some type of response to the client to let them know if the update took place. Or at least logic in the app to recheck the balance.
March 25, 2008 at 3:59 am
Thanks for your replies. I have changed my initial logic plan in the last 2 days and now I need to store the data rather than update it. My aim is to do something like what I have explained below and I was planning on building this logic at the Database level only rather than on the frontend code.
There are certain allocations(transactions) that happen on a periodic basis and I am storing these transactions in the PurchaseTranMaster and PurchaseTranDetail table. These transactions are categorized as 'Main' type and the amount could be allocated for one or many categories in a single transaction. Below is how it will be saved in the 2 tables
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
217-March-2008 Main
319-March-2008 Main
PurchaseTranDetail
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
Now what happens is users of my application can make purchases under all these categories only until the Balance under these categories is > than purchase amount. The Balance is calculated as sum of all transactions. It means that w.r.t the above data the balances for each category is(this is not stored in the database)
A 1300
B 3000
C 400
So lets say a user does make a purchase(Trantype is 'SUB') of 300 under A and 400 under B in a single transaction. The data would then be stored in the tables as
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
217-March-2008 Main
319-March-2008 Main
420-March-2008 SUB
PurchaseTranDetail
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
In the PurchaseTranDetail the Debit_TranId value means that the amount has been marked against the TranID 1. This TranId is not handpicked by the user and the system should allocate it accordingly based on the amount available for a particualar category for a Main Transaction. It means that before TranId 4 was saved in the database then the system would first check whether the Total available balance for A >=300 and B>=400 (in our example above it is 1300 and 3000 resp)
Then if the Balance is > than the puchase amount then the allocation would be done by the system and this would be done against the TranID whose TranDate was the earliest, so thats why the Debit_TranId column has 1 as TranId 1 was the earliest.
so logically now the balance for the categories would be (this is not saved in the database)
A 1000
B 2600
C 400
So next time again when a user would make a purchase(transaction) under A for 800 and under B for 1000 then if the balance is greater than the purchase amount(which in this case it is) the allocation would happen according to the earliest TranId and this time amount would be partly marked against TranId 1 , TranID 2 and TranID 3. The data would look like this
PurchaseTranMaster
TranID TranDate TranType
1 14-March-2008 Main
217-March-2008 Main
319-March-2008 Main
420-March-2008 SUB
521-March-2008 SUB
TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
5 700 A 1
5 100 A 3
5 600 B 1
5 400 B 2
I need to do the above taking into consideration that there could be multiple users making purchases(concurrency).
Also I was building my logic on doing the above whether to use cursors or loops. I just need to know how do I write my stored
procedure and what would be the most efficeint way of doing the above.
March 25, 2008 at 7:17 am
If you would please provide the DDL for your tables, sample data in the form of inserts with unioned selects, this would help greatly. You have already provided the expected output.
Read the article, it provides excellent tips on how to ask for help effectively.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
😎
March 26, 2008 at 12:14 am
Thanks for reading. After reading I know my post looks messy. I hope I was able to explain what I intent to do. I have the table DDL below
March 26, 2008 at 12:19 am
/*CREATE TABLE PurchaseTranMaster
(
TranID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TranDate Datetime,
TranType varchar(30)
)
CREATE TABLE PurchaseTranDetail
(
TranID int,
Amount int,
Category Varchar(20),
Debit_TranId int
)
insert into PurchaseTranMaster values(convert(datetime,' 14-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 17-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 19-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 20-March-2008',103),'SUB')
insert into PurchaseTranMaster values(convert(datetime,' 21-March-2008',103),'SUB')
insert into PurchaseTranDetail values(1,1000,'A',0)
insert into PurchaseTranDetail values(1,1000,'B',0)
insert into PurchaseTranDetail values(2,2000,'B',0)
insert into PurchaseTranDetail values(3,300,'A',0)
insert into PurchaseTranDetail values(3,400,'C',0)
insert into PurchaseTranDetail values(4,300,'A',1)
insert into PurchaseTranDetail values(4,400,'B',1)
insert into PurchaseTranDetail values(5,700,'A',1)
insert into PurchaseTranDetail values(5,100,'A',3)
insert into PurchaseTranDetail values(5,600,'B',1)
insert into PurchaseTranDetail values(5,400,'B',2)
*/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply