Attribute level competition - SQL

Question

If I have 2 clients accessing the same attribute, like this:

 c1   UPDATE ACCOUNT set balance = balance + 3000
      where NUM_ACCOUNT = 12390

 c2   UPDATE ACCOUNT set balance = balance - 1500
      where NUM_ACCOUNT = 12390

Assuming that the balance was initially € 10,000, what would be the final value?

• 10000 + 3000 - 1500 = 11500
• 10000 + 3000 = 13000
• 10000 - 1500 = 8500

Answer 1

After the two transactions commits it will be € 11.500,00 (unless someting very nasty happens).

Since you don't tagged a specific DBMS I will link to Oracle and MS-SQL documentation and a reference in wikipedia. Dude you have a lot to read about it if you want to go deep in how modern relational database systems handles this.

Atomicit

Transactions

transactions - MSDN

The good news is the DBMS (any decent one) will make things transparent to you and you don't need to bother with it and must avoid messing with any transaction option (read uncommited, snapshot, etc) unless you know very well what you are doing here.

Basicaly the system will receive both transactions and commit one by one trying to avoid deadlocks and other concurrency issues and respecting locks.

Answer 2

Relational databases implement something called ACID properties. Basically, this ensures that each user sees a consistent view of the data, as if the users transactions were run independently. This consistency (well, "consistency" is technically the second of the four properties) is a key reason why relational databases are used for applications that change data.

Hence, if your settings are set up for ACID-compliance (which they should be, but they could be overridden), then the result would be +1500.