What Is Atomicity in Dbms

What is atomicity in dbms

"Every column should be atomic."

Chris Date says, "Please note very carefully that it is not just simple things like the integer 3 that are legitimate values. On the contrary, values can be arbitrarily complex; for example, a value might be a geometric point, or a polygon, or an X ray, or an XML document, or a fingerprint, or an array, or a stack, or a list, or a relation (and so on)."[1]

He also says, "A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute."[2]

He generally discourages the use of the word atomic, because it has confusing connotations. Single value is probably a better term to use.

For example, a date like '2014-01-01' is a single value. It's not indivisible; on the contrary, it quite clearly is divisible. But the dbms does one of two things with single values that have parts. The dbms either returns those values as a whole, or the dbms provides functions to manipulate the parts. (Clients don't have to write code to manipulate the parts.)[3]

In the case of dates, SQL can

• return dates as a whole (SELECT CURRENT_DATE),
• return one or more parts of a date (EXTRACT(YEAR FROM CURRENT_DATE)),
• add and subtract intervals (CURRENT_DATE + INTERVAL '1' DAY),
• subtract one date from another (CURRENT_DATE - DATE '2014-01-01'),

and so on. In this (narrow) respect, SQL is quite relational.

---

1. An Introduction to Database Systems, 8th ed, p 113. Emphasis in the original.
2. Ibid, p 358.
3. In the case of a "user-defined" type, the "user" is presumed to be a database programmer, not a client of the database.

database atomicity consistency

Atomicity is indeed saying that each transaction is either all or nothing, meaning that either all or none of its actions are executed and that there are no partial operations.

However, consistency talks about ensuring that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof
(taken from Wikipedia).
That basically means that only valid states are written to the database, and that a transaction will either be executed if it doesn't violate the data consistency or rolled back if it does.

Hope it clears things out for you.

what is the difference between atomicity and durability in DBMS?

Those are two of the four ACID properties, which allow for reliability of database transactions.

Simplistically, atomicity means that something either happens or doesn't happen - there is no halfway state.

For example, when I transfer money between accounts at my bank, there's no possibility the money will leave one account without showing up in the other. For example, you may find something like:

start transaction
  update working_account set balance = balance - 100
  update savings_account set balance = balance + 100
commit transaction

At the commit point, atomicity guarantees that either both update statements happen or neither of them do (if there's an error of some sort, or if you roll back the transaction rather than committing it).

---

On the other hand, durability means that, once it's happened, it stays happened.

That means the bank won't forget that I transferred the money.

In the context of the above statements, the commit remembers what has been done (on non-volatile memory, on disk, or otherwise) so that catastrophic failure will not affect the transaction.

How do ACID and database transactions work?

ACID is a set of properties that you would like to apply when modifying a database.

• Atomicity
• Consistency
• Isolation
• Durability

A transaction is a set of related changes which is used to achieve some of the ACID properties. Transactions are tools to achieve the ACID properties.

Atomicity means that you can guarantee that all of a transaction happens, or none of it does; you can do complex operations as one single unit, all or nothing, and a crash, power failure, error, or anything else won't allow you to be in a state in which only some of the related changes have happened.

Consistency means that you guarantee that your data will be consistent; none of the constraints you have on related data will ever be violated.

Isolation means that one transaction cannot read data from another transaction that is not yet completed. If two transactions are executing concurrently, each one will see the world as if they were executing sequentially, and if one needs to read data that is written by another, it will have to wait until the other is finished.

Durability means that once a transaction is complete, it is guaranteed that all of the changes have been recorded to a durable medium (such as a hard disk), and the fact that the transaction has been completed is likewise recorded.

So, transactions are a mechanism for guaranteeing these properties; they are a way of grouping related actions together such that as a whole, a group of operations can be atomic, produce consistent results, be isolated from other operations, and be durably recorded.

atomic operations and atomic transactions

In a statement:
an atomic transaction is the smallest set of operations to perform the required steps.
Either all of those required operations happen(successfully) or the atomic transaction fails.

An atomic operation usually has nothing in common with transactions. To my knowledge this comes from hardware programming, where an set of operations (or one) happen to get solved instantly.

Can a database support Atomicity but not Consistency or vice-versa?

They are somewhat related but there's a subtle difference.

Atomicity means that your transaction either happens or doesn't happen.

Consistency means that things like referential integrity are enforced.

Let's say you start a transaction to add two rows (a credit and debit which forms a single bank transaction). The atomicity of this has nothing to do with the consistency of the database. All it means it that either both rows or neither row will be added.

On the consistency front, let's say you have a foreign key constraint from orders to products. If you try to add an order that refers to a non-existent product, that's when consistency kicks in to prevent you from doing it.

Both are about maintaining the database in a workable state, hence their similarity. The former example will ensure the bank doesn't lose money (or steal it from you), the latter will ensure your application doesn't get surprised by orders for products you know nothing about.

Ensuring Atomicity sql

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.")

---

Related Topics