Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Ensuring Atomicity sql

Tags:

sql

atomic

I was just reading about RDBMS, and one property of an RDBMS is atomicity. So, if money is withdrawn from an account and transferred to another, either the transaction will happen completely or not at all. There are no partial transactions. But how is actually ensured?

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

Which by no means ensures atomicity.. So how does it actually happen?

like image 470
simplfuzz Avatar asked Mar 28 '09 10:03

simplfuzz


People also ask

Which system ensures atomicity in SQL?

Let's discuss the properties of ACID. ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the properties of a transaction.

What is atomicity in SQL?

Atomicity. All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.

How do you enforce atomicity?

The simplest way to enforce atomicity is for the DBMS to refuse to start any transaction until the previous one has committed.

Which of the following options ensures atomicity?

Explanation: The recovery system is responsible for the maintenance of durability. In addition, it also ensures atomicity. 7. Which of the following systems is responsible for ensuring isolation?


2 Answers

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 to what is has done for changes on account 101. And then if the work on account 102 would fail, the RDBMS uses those notes to undo the work on 101.

Furthermore, when it has started work on account 101 is takes a lock on the database, so that no-one else can come and read the updated, but not committed data in account 101. (A lock here is basically just a note somewhere "I am working here, do not touch.")

like image 125
leiflundgren Avatar answered Sep 25 '22 22:09

leiflundgren


To be atomic, transactions need to:

  • Prevent other transactions from interfering with the rows they are writing or reading
  • Make sure that either all or none of the changes that the transaction makes, will be in the database when the transaction commits.

First one is achieved by locking rows that the transaction reads or writes during it's execution.

Second one is done so that transactions write their actions into a transaction log. This makes the database able to recover even when the server loses power during a transaction. In this case the recovery process will read the log, make sure that active (uncommited) transactions get aborted and changes made by them are canceled.

like image 30
John Smith Avatar answered Sep 22 '22 22:09

John Smith