December 10, 2008 at 10:18 am
Lets say there are $20000 in a deposit which has to be shared by two people. Now, person A logs into a system wants to transfer $5000 to some third person. At the same time, person B logs in and wants to transfer $8000 to fourth person. Both of them starts the transaction at same time.
Also both of them ends the transaction at the same time.
How should this case be handled programmatically, if we want to process the requests from two people A and B, as both requests are valid.
-TNV
December 10, 2008 at 10:34 am
This is kind of a hard question to answer without a bit more detail,
What does your table look like?
how are you accessing the database?
What are the business rules surrounding transactions occuring at the same time? this will help you define what to do programaticly.
An easy anwser would be to let SQL locking handle this situation. So that transaction A finishes before transaction B.
December 10, 2008 at 11:03 am
Initially I issue a select statement to read the balance from a table.
Next, I subtract the amount to be transfered
and finally, I update the balance to the same table.
The above three steps happens from two different terminals.
Actually, I am a student and I created the requirement by myself. There are no other business rules for this transaction.
December 10, 2008 at 11:17 am
in one of our accounting tables, we had a similar requirement where no update should be completed if someone updated the row prior toy you committing...
so if there was 20K in a field in your example, you'd want to avoid two people withdrawing 20K each at the same near instantaneous moment.
to resolve that, we use a timestamp field, and explicitly use the field in a WHERE statement for the update.
here's an example:
create table Example(
EID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
AMT DECIMAL(10,2),
tmpstmp timestamp )
--a couple sample rows
insert into example(AMT)
select 20000
union select 4444
select * from Example
EID AMT tmpstmp
1 20000.00 0x0000000000777FE1
2 4444.00 0x0000000000777FE2
update Example set amt = amt - 5000 where EID = 1 and tmpstmp = 0x0000000000777FE1
it might not be obvious, but if you read the data, then waited, and then updated, potentially somoene could update in your wait time...but if they update, the timestamp would be different.
so your update would NOT fail, but would instead affect zero rows if the timestamp changed while you were fiddling with records or something.
it requires one additional item in the WHERE clause, but prevents the kind of issue you seem to want to avoid.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply