what is difference between atomicity and isolation in ACID properties?

Question

the difference between atomicity and isolation of DBMS is somewhat vague so i am asking for a clear difference between the two ?

Atomicity and isolation, are ensured in classical database transactions by using a commit protocol. This protocol is used to turn temporary storage into permanent storage - that is, the updates to the transaction's data are not visible until the commit protocol validates the stored data. Note that it is the presence of a commit record in the database log which effectively validates the transaction's data.

some tutorial shows the above definition but i'm not cleared with that can any help me — tamil, Oct 06 '17 at 06:28
Possible duplicate of [ACID and database transactions?](https://stackoverflow.com/questions/3740280/acid-and-database-transactions) — yesemsanthoshkumar, Oct 06 '17 at 08:22
Seems like Isolation is a necessary consequence of Atomicity. Feels kind of redundant. — B T, Mar 20 '23 at 15:43

score 9 · Answer 1 · edited Jan 29 '20 at 02:37

9

Atomicity describes the behavior within an individual transaction, and Isolation describes the behavior among one or more transactions.

Atomicity: Either all of the database operations in a transaction are executed or none are.

Isolation: Each transaction appears to execute in isolation from other transactions.

edited Jan 29 '20 at 02:37

jyapx

2,009
2
15
18

answered Jun 28 '18 at 20:28

hussam alhunaiti

229
3
5

1

Amazing answer!! – LookIntoEast Jul 25 '21 at 02:24

Palindromer · Answer 2 · 2022-08-03T14:27:14.360

2

Atomicity: all operations in a transaction are executed as a single unit.

Isolation: transactions don't see intermediate changes of other transactions.

According to these definitions, Isolation is just a part of Atomicity. Because when other transactions can see intermediate impact of our transaction, operations of our transaction cannot be considered as a single atomic unit.

edited Aug 03 '22 at 14:27

answered Aug 03 '22 at 14:21

Palindromer

854
1
10
29

score 0 · Answer 3 · answered Feb 13 '23 at 06:17

Atomicity means if ONE transaction contains multiple operations, either ALL of them or NONE are performed.

The counterintuitive point is, this definition still allows for interleaving the operations with other transactions. As a result, atomicity itself does not guarantee isolation.

Example: Transaction A and B both contain two operations:

Transaction A:
A1: Read Alice's account balance
A2: Add 100 and write it back to Alice's balance

Transaction B:
B1: Read Alice's account balance
B2: Add 50 and write it back to Alice's balance

Suppose now Alice's balance is 0, and transactions A and B happen concurrently in the order of A1-B1-B2-A2, what is the final balance? It's 100.

Both A and B are atomically committed, in the sense that all their operations occurred. But without proper isolation, the result is probably not what you want.

what is difference between atomicity and isolation in ACID properties?

3 Answers3