Ensuring sql atomicity

Question

I just read about RDBMS, and one feature of DBMS is atomicity. So, if money is withdrawn from the account and transferred to another, or the transaction will occur completely or not at all. No partial deal. But how is it really secured?

Sql queries for the above scenario might look like (i) UPDATE accounts set balance = balance - amount WHERE ac_num = 101 (ii) UPDATE accounts set balance = balance + amount WHERE ac_num = 102

This does not provide atomicity at all .. So, how does this happen?

Answer 1

If you do

 BEGIN TRANSACTION UPDATE accounts set balance = balance - amount WHERE ac_num = 101 UPDATE accounts set balance = balance + amount WHERE ac_num = 102 COMMIT TRANSACTION 

The database system will write notes about what has been done to change account 101. And then, if work on account 102 fails, the RDBMS uses these notes to cancel work on 101.

In addition, when he started working on account 101, a database lock occurs, so no one can come in and read the updated but not fixed data in account 101. (The lock here is basically just a note somewhere "I work here don't touch. ")

Answer 2

To be atomic, transactions must:

• Prevent other transactions from interfering with the lines they write or read
• Make sure that either all or any changes that the transaction makes will be in the database when the transaction is completed.

The first of these is achieved by locking the rows that the transaction reads or writes at runtime.

The second operation is performed so that the transactions record their actions in the transaction log . This allows you to restore the database even when the server loses power during the transaction. In this case, the recovery process will read the log, make sure that the active (inactive) transactions are interrupted, and the changes made by them are discarded.