?
Solved

Lost Update problem in SQL Server

Posted on 2005-03-31
11
Medium Priority
?
457 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:navinkaus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13678887
Use

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Before starting the transaction
0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13678904
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
 

Author Comment

by:navinkaus
ID: 13678941
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13678985
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
 
LVL 8

Accepted Solution

by:
anthonywjones66 earned 600 total points
ID: 13679000
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
 

Author Comment

by:navinkaus
ID: 13679165
There is no need to reset the isolation level to default after commiting ??
0
 

Author Comment

by:navinkaus
ID: 13679174
I am using recordset in vc++
0
 
LVL 9

Expert Comment

by:solution46
ID: 13679220
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
 

Author Comment

by:navinkaus
ID: 13680543
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
 

Author Comment

by:navinkaus
ID: 13680615
             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
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13680861
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question