sql table cell modified by multiple threads at the same time

Question

if you have table BankAccount that has a column Amount and the value for this column for a specific row can be modified by multiple threads at the same time so it could happen so that the last one to set the value will win.

how do you usually handle this kind of situations ?

UPDATE: I heard that in MSSQL there is update lock UPDLOCK that locks the table or the row that is being updated, could I this here somehow ?

Answer 1

You ussually use transactions to overcome this.

Have a look at Database transaction

Answer 2

You cannot have several threads modifying the same data at exactly the same time : it will always be the last one which set the value that'll "win".

If the problem is that several threads read and set the value at almost the same time, and reads and writes don't arrive on the right order, the solution is to use Transactions :

• start a transaction
• read the value
• set the new value
• commit the transaction

This ensures the read and the write will be done consistently, and no other thread will be able to modify the data during the same transaction.

Quoting the wikipedia page about Database Transactions :

A database transaction comprises a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. Transactions in a database environment have two main purposes:

1. To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.

2. To provide isolation between programs accessing a database concurrently. Without isolation the programs' outcomes are typically erroneous.

Answer 3

An update statement which references the current value would prevent overwriting. So, instead of doing something like

SELECT Amount FROM BankAccount WHERE account_id = 1

(it comes back as 350 and you want to subtract 50)...

UPDATE BankAccount SET Amount = 300 WHERE account_id = 1

do

UPDATE BankAccount SET Amount = Amount - 50 WHERE account_id = 1

Answer 4

You should have a database function/procedure which makes operations with the "Amount". This function/procedure should return if the operation was succeeded or failed (for example, you want take $1000, but current AMount is only $550, so operation can not be proceede).

Expamle in T-SQL:

UPDATE BankAccount SET Amount = Amount - 1000 WHERE BankAcountID = 12345 AND Amount >= 1000
RETURN @@ROWCOUNT

If the amaount was changed, the return value will be 1, otherwise 0.

Know, you can safely run this functions/procedures (in several threads too):

DECLARE @Result_01 int, Result_02 int, Result_03 int
EXEC @Result_01 = ChangeBankAccountAmount @BankAcountID = 12345, @Amount = 1000
EXEC @Result_02 = ChangeBankAccountAmount @BankAcountID = 12345, @Amount = 15
EXEC @Result_03 = ChangeBankAccountAmount @BankAcountID = 12345, @Amount = 600, @Amount = -2000

EDIT: Whole procedure in T-SQL:

CRATE PROC ChangeBankAccountAmount
@BankAccountID int,
@ChangeAmount int,
@MMinAmount int = 0
AS BEGIN
  IF @ChangeAmount >= 0
    UPDATE BankAccount SET Amount = Amount + @ChangeAmount WHERE BankAcountID = 12345
  ELSE
    UPDATE BankAccount SET Amount = Amount + @ChangeAmount WHERE BankAcountID = 12345 AND Amount >= @MMinAmount

  RETURN @@ROWCOUNT
END

Of course - the "int" datatype is not good for money, you should change it to datatype used in your table.