| Author |
Topic |
|
wbb1975
Starting Member
23 Posts |
 Posted - 2003-08-13 : 15:28:16
|
| HI,in a new application (web application based on asp.net & MSSQL 2000) I have the following problem and I am not quite sure how to mangage the following problem:I have a tableUserID intUserMoney moneywhere I should to the following tasks1.) Read if the user has enough money2.) do some other operations if the user has enough money3.) decrease the money based on what I have done in 2.)But I have to ensure that while doing 2.) no other users are decreasing the money, so I thought I should somehow lock the row of this user while performing 2.)Am I thinking correct and how is this best done??Thanks in advanceGreetingsStefan |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-13 : 19:33:57
|
| Do you actually need to lock the record, or just do it all in one transaction?That is, are the steps in part 2 interactive? You could add a lock column to each record in the table, write to it in step 1, clear it after step3, but this is a pain if you don't need to go there...more info...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-13 : 19:39:05
|
| You could also do step 3 before step 2 .... That is, take the money out first (and fail immediately if the balance isn't enough) and then perform the other actions.If for some reason the "doing the action" fails, you can put the money back.That's probably not the best way, but is certainly a simple approach that should work.- Jeff |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-13 : 19:57:26
|
| see - he's not just cross, he's also very clever!--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-08-13 : 20:08:21
|
| Use your Isolation levels and transaction management. That is exactly what they are for.Don't try and code something that is already implemented and has been coded/tested by rocket scientist! DavidM"SQL-3 is an abomination.." |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-13 : 20:20:47
|
quote:
Isolation levels
OK Dave, more info for us dummies...--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-08-13 : 21:54:42
|
| BOL... "SET TRANSACTION ISOLATION LEVEL"DavidM"SQL-3 is an abomination.." |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-08-13 : 21:56:10
|
| Ta--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-08-14 : 02:54:07
|
Transaction Isolation levels are good, but they need to be used with caution. You'd be tempted to use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (that's the highest level of isolation) all the time, but remember this has a direct impact on concurrency. Most of the time you wont have a problem as long as you use Transactions in the first place and follow some basic rules such as keeping transactions as short as possible. SQL Server is smart enough to know what to lock, and the extent of the locking (most of the time  )Owais |
|
|
|
wbb1975
Starting Member
23 Posts |
Posted - 2003-08-14 : 06:43:15
|
quote:
Originally posted by rrb
Do you actually need to lock the record, or just do it all in one transaction?That is, are the steps in part 2 interactive? You could add a lock column to each record in the table, write to it in step 1, clear it after step3, but this is a pain if you don't need to go there...more info...--I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Thanks for your answer.What I have to do is to ensure that the money read in step 1 will not be withdrawn during the transactions in step 2, so in step 1 I will check if the user has enough money and in step 2 the user will buy something for the money but it must not occur that the user buys something else during I am making all the stuff in step 2 because doing so the user would use more money than available ...!!! |
|
|
|
wbb1975
Starting Member
23 Posts |
Posted - 2003-08-14 : 06:44:57
|
quote:
Originally posted by jsmith8858
You could also do step 3 before step 2 .... That is, take the money out first (and fail immediately if the balance isn't enough) and then perform the other actions.If for some reason the "doing the action" fails, you can put the money back.That's probably not the best way, but is certainly a simple approach that should work.- Jeff
Thanks for your answer.Yes this approach would help, I have already thought about it; but the problem is that I do not know the amount which will be withdrawn in step 1; only after step 2 I will know the exact amount ... ;-)GreetingsStefan |
|
|
|
wbb1975
Starting Member
23 Posts |
Posted - 2003-08-14 : 06:49:17
|
quote:
Originally posted by mohdowais
Transaction Isolation levels are good, but they need to be used with caution. You'd be tempted to use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (that's the highest level of isolation) all the time, but remember this has a direct impact on concurrency. Most of the time you wont have a problem as long as you use Transactions in the first place and follow some basic rules such as keeping transactions as short as possible. SQL Server is smart enough to know what to lock, and the extent of the locking (most of the time )Owais
Thanks for your answer!Could you give me some more information please!!! Thanks, thanks, thanks, thanks, thanks!!!!GreetingsStefan |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-08-14 : 09:22:45
|
Read up in the Books Online that come with any SQL Server installation. There is a wealth of information on dealing with transactions, locking and isolation levels. Once you know what you are after, do some testing on your own, and whenever you get stuck, yell for help right here. We're always around to help  Owais |
|
|
|
|
Transaction & Locking