race condition UPDATE modification of credit column - what happens on rollback?

ok, I tried searching and have not found an answer to this - I am curious how the ROLLBACK handles race conditions. For example:

If I have a table (CompanyAccount) which keeps track of how many credits an company has available for purchase (there is only one开发者_如何学Python row in a database table per company) and there are potentially multiple users from the same company who can decrement the credits from the single company account, what happens in case of an error when a ROLLBACK occurs?

Example:

Assumptions: I have written the update properly to calculate the "Credit" new balance instead of guessing what the new credit balance is (i.e. we don't try to tell the UPDATE statement what the new Credit balance/value is, we say take whatever is in the credit column and subtract my decrement value in the UPDATE statement)...

here is an example of how the update statement is written:

UPDATE dbo.CompanyAccount SET Credit = Credit - @DecrementAmount WHERE CompanyAccountId = @CompanyAccountId

If the "Credit" column has 10,000 credits. User A causes a decrement of 4,000 credits and User B causes a decrement of 1000 credits. For some reason a rollback is triggered during User A's decrement (there are about a 1/2 dozen more tables with rows getting INSERTED during the TRANSACTION). If User A wins the race condition and the new balance is 6,000 (but not yet COMMIT'ed) what happens if User B's decrement occurs before the rollback is applied? does the balance column go from 6,000 to 5,000 and then gets ROLLBACK to 10,000?

I am not too clear on how the ROLLBACK will handle this. Perhaps I am over-simplifying. Can someone please tell me if I misunderstand how ROLLBACK will work or if there are other risks I need to worry about for this style.

Thanks for your input.

In the example you have given there will be no problem.

The first transaction will have an exclusive lock meaning the second one can not modify that row until after the first one has committed or rolled back. It will just have to wait (blocked) until the lock is released.

It gets a bit more complicated if you have multiple statements. You should probably read up on different isolation levels and how they can allow or prevent such phenomena as "lost updates".

Rollback is part of the transaction and locks will be maintained during the rollback. The *A*tomic in ACID. User B will not start until all locks are released.

What happens:

• User A locks rows
• User B won't see the rows until locks are released
• User A rolls back, release locks, changes never happened.
• User B sees the rows. -1000 will result in 9000

However, if User B has already read the balance then it my be inconsistent when it comes to UPDATE. It depends on what you're actually doing and in what order, hence the need to understand isolation levels (and the issues with phantom and non-repeatable reads)

An alternative to SERIALIZABLE or REPEATABLE READ may to use sp_getapplock in transaction mode to semaphore parts of the transaction.