Lost Update problem in SQL Server

Hi
I am suffering from lost update problem.
Like if multiple transactions are running concurrently they all want to modify a record only after checking the status of a field in the record but it may happen that 2 or more transaction read the same status and update the record and only update of last transaction will persist.

What I want if record is reading by someone that should be locked until it reads and updates and them commit the transaction.


How it can be achieved in SQL Server
navinkausAsked:
Who is Participating?
 
anthonywjones66Connect With a Mentor Commented:
CREATE PROCEDURE spMoveMoney
  @debitAccountID int,
  @creditAccountID int,
  @value int
AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

DECLARE @debitBalance int
DECLARE @creditBalance int

IF @creditAccountID > @debitAccountID BEGIN
  SELECT @debitBalance = balance FROM tblAccount WHERE AccountID = @debitAccountID
  SELECT @creditBalance = balance FROM tblAccount WHERE AccountID = @creditAccountID
  END
ELSE BEGIN
  SELECT @creditBalance = balance FROM tblAccount WHERE AccountID = @creditAccountID
  SELECT @debitBalance = balance FROM tblAccount WHERE AccountID = @debitAccountID
END --IF

SET @debitBalance = @debitBalance - @value
SET @credtBalance = @creditBalance + @value

UPDATE Account SET balance = @debitBalance WHERE AccountID = @debitAccountID
UPDATE Account SET balance = @creditBalance WHERE AccountID = @creditAccountID

COMMIT TRANSACTION
0
 
anthonywjones66Commented:
Use

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Before starting the transaction
0
 
anthonywjones66Commented:
Note that this has performance implications as it generates more locks that are held for longer.

It also can lead to more deadlocks if different operations lock the same things but in a different order.

Use with care.

Anthony.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
navinkausAuthor Commented:
You told me the drawbacks of using Serialization but you did't give me the solution of avoiding that . also I try to tell my problem by giving following example


 Yes it is a problem I try to give you a example

Transaction 1 Transfer $100 from A to C

Tansaction 2 Transfer $200 from A to B

In case of concurrent scenario say both transactions read the balance $500 same time.

Transaction 1 will put $400 in account

Transaction 2 will put $300 in account ( overwrite previous one )


And actually it should be $200 in account


So problem was both read the same balance

I guess you got my point .

What I want another transaction should wait until transaction 1 read and finishes update.
0
 
anthonywjones66Commented:
Well the solution to the first problem (performance) is don't use it.  Of course if you need it then you simply need it and take the performance hit on the chin.  If you find things are now to slow (very doubtful with modern technology) then upgrade your hardware.

The solution to the second problem is to ensure that transacted operations that lock the same things lock those things in the same order.

In your specific example make sure that within the transaction you read both balances before doing anything and do this in an ordered way by reading the record with the lowest ID before the highest ID.  This may help eliminate some deadlocks.  But deadlocks are part and parcel of database activitiy they will on occasion happen despite carefully coding to reduce them they often are unavoidable.  When the DB detects a deadlock it will victimise one of the transactions and rollback any changes it may have made then return an error to the calling code indicating a deadlock occured.  It is the responsibility of the calling code to handle deadlocks.

Anthony.
0
 
navinkausAuthor Commented:
There is no need to reset the isolation level to default after commiting ??
0
 
navinkausAuthor Commented:
I am using recordset in vc++
0
 
solution46Commented:
How about changing the UPDATE statement slightly...

CREATE PROC spTransferMoney
    @AccountFromID int,
    @AccountToID int,
    @Amount money

AS

BEGIN TRAN

UPDATE [Account] SET [Balance] = [Balance] - @Amount WHERE [AccountID] = @AccountFromID
IF (@@Error <> 0)
BEGIN
    ROLLBACK TRAN
    RETURN
END

UPDATE [Account] SET [Balance] = [Balance] + @Amount WHERE [AccountID] = @AcctountToID
IF (@@Error <> 0)
BEGIN
    ROLLBACK TRAN
    RETURN
END
ELSE
    COMMIT TRAN



This will always use the current value of [Balance] to work out the new value.
Of course, in a financial situation, you should NEVER, EVER do it this way - your auditors will have a fit at the lack of audit trail. You should really use a double entry system, with a [Transaction] table having at least the fields...

[Transaction]
[TransactionID] int (identity)
[AccountID] int
[TransactionValue] money
[ThirdPartyAccountID] int

In the case of the above example, you would use a sproc like this...

CREATE PROC spTransferMoney
    @AccountFromID int,
    @AccountToID int,
    @Amount money

AS

BEGIN TRAN

INSERT INTO [Transaction] (
    [AccountID],
    [TransactionValue],
    [ThirdPartyAccountID])
VALUES (
    @AccountToID,
    @Amount,
    @AccountFromID)

IF ((@@Error <> 0) OR (@@RowCount <> 1))
BEGIN
    ROLLBACK TRAN
    RETURN
END

INSERT INTO [Transaction] (
    [AccountID],
    [TransactionValue],
    [ThirdPartyAccountID])
VALUES (
    @AccountFromID,
    0 - @Amount,
    @AccountToID)

IF ((@@Error <> 0) OR (@@RowCount <> 1))
BEGIN
    ROLLBACK TRAN
    RETURN
END
ELSE
    COMMIT TRAN




You would then use a separate sproc to get the current balance of each account as required, by SUMming the [TransactionValue] field from the [Transaction] table.


Hope this helps,

s46.
0
 
navinkausAuthor Commented:
I still could't implement isolation level= serializable as still it gives error in case of concurrent transaction for last one day I have tried with many ways like alockpesstimistic etc but till now could't get anything which provides me what I want. I am using recordset in VC++.

In case of isolation level=serializable one of transaction gives error when it goes to update the record.

When I used pesstimistic two concurrent transactions do everything right but next time when they come back one of then throws error on begin "Cannot create new transaction on this session".

Still I am very much confused doing all R & D stuff.
0
 
navinkausAuthor Commented:
             First of all I would like to let you know that I am working on Recordset in VC++ through binding method. Since all data encrypts and decrypts before adding,updating , displaying etc It is sure that I can't use queries.


 Now question arises How can I achieve

 Basically what I want is 2 concurrent transaction should be linear means in case of multi-user - client -server scenario only one transaction should be active that way I can achieve my motive

Situation 1. I tried after setting isolation level = serializable but it does't wait and one transaction gives error .

Situation 2: I did't use isolation level but I used lock type pesstimistic both transaction runned as I was expecting but when they both come back in round 2 one of them throws error in BeginTrans() "Cannot create new transaction in this session" also I used table lock so that one transaction will wait .

I look forward for your feedback on whole situation.

0
 
anthonywjones66Commented:
In situation 1 what is the error?  The first transaction is successful and the second does nothing right?  What prevents you from backing off for say 250ms and trying again on the second transaction?

Anthony.
0
All Courses

From novice to tech pro — start learning today.