Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gmuller1111
Starting Member
4 Posts |
 Posted - 2010-02-24 : 10:19:01
|
| Hello guys,do you have any idea on how to design tables for this issue?Requirements are this:- one numeric value which is used for multiple users- at the beginning of transaction shared value is read and decremented for certain value- another user has another transaction which checks for the same value- shared value must fall under value 0I suppose this shared value could be my bottleneck in case of larger number of users. Is there any common practise on how to distribute this load? That shared value represents group's credit limit. In that group can be a large number of users and they are all reading and decreasing the same bucket :(Thanks in advance!Kind regards,Muller |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-24 : 10:35:19
|
| does that mean they all do the decreasing simultaneously?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
gmuller1111
Starting Member
4 Posts |
Posted - 2010-02-24 : 11:07:39
|
Yes, if they request the transaction at the same time. Imagine this scenario:CreditLimit = 100User 1 = Read CreditLimitUser 1 = Decrease by 50User 2 = Read CreditLimitUser 2 = Decrease by 50 CreditLimit = 0But, what happens when they try to read it at the same time:CreditLimit = 100User 1 = Read CreditLimitUser 2 = Read CreditLimitUser 1 = Decrease by 50 (Write 100 - 50 = 50)User 2 = Decrease by 50 (Write 100 - 50 = 50)CreditLimit = 50I know that little number of concurrent users are not the problem, but what happens when 100 000 users reads CreditLimit. I dont want this to be bottleneck. quote:
Originally posted by visakh16
does that mean they all do the decreasing simultaneously?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
gmuller1111
Starting Member
4 Posts |
Posted - 2010-02-25 : 09:10:08
|
Any idea?quote:
Originally posted by gmuller1111
Yes, if they request the transaction at the same time. Imagine this scenario:CreditLimit = 100User 1 = Read CreditLimitUser 1 = Decrease by 50User 2 = Read CreditLimitUser 2 = Decrease by 50 CreditLimit = 0But, what happens when they try to read it at the same time:CreditLimit = 100User 1 = Read CreditLimitUser 2 = Read CreditLimitUser 1 = Decrease by 50 (Write 100 - 50 = 50)User 2 = Decrease by 50 (Write 100 - 50 = 50)CreditLimit = 50I know that little number of concurrent users are not the problem, but what happens when 100 000 users reads CreditLimit. I dont want this to be bottleneck. quote:
Originally posted by visakh16
does that mean they all do the decreasing simultaneously?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
gmuller1111
Starting Member
4 Posts |
Posted - 2010-02-25 : 09:11:31
|
Perhaps I should somehow distribute this credit limit into multiple values?quote:
Originally posted by gmuller1111
Any idea?
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-25 : 09:20:13
|
| then why dont you wrap it inside an explicit transaction?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Shared value - concurrent users