database atomicity consistency

Question

What is difference between Atomicity and consistency ? it looks to me as both are saying same thing in different word.

Atomicity

All tasks of a transaction are performed or none of them are. There are no partial transactions. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows.

Consistency

The transaction takes the database from one consistent state to another consistent state. For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data.

and looks like atomicity is subset of consistency, then it shoud be cid(conistency, isolation, duribility) ,no atomicity

Non atomic, consistent: Partial transaction may leave the system in a consistent state. Atomic, non-consistent: 2 parts of the system may uphold atomicity without being consistent (transaction is recorded in one and not in the other part). — nhahtdh, Sep 30 '12 at 20:31

score 20 · Accepted Answer · edited Sep 17 '14 at 18:11

20

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.

edited Sep 17 '14 at 18:11

Mitja

1,969
30
35

answered Sep 30 '12 at 20:35

Adi

707
6
12

looks like atomicity is subset of consistency, then it shoud be cid(conistency, isolation, duribility) ,no atomicity – Ram Sharan Mittal Jul 28 '20 at 05:00
Just reading high load apps book and it’s said there, that consistency is not about databases but application business logic - it depends on how business data is consistent in general after each update. Therefore it’s C redundant in the abbreviation - should be AID. – Dedyshka Jan 18 '22 at 19:44

M.Namjo · Answer 2 · 2014-11-01T09:54:38.190

6

simple explain For consistency : if a field-type in database is Integer, it should accept only Integer value's and not some kind of other.If you want to store other types in this field, consistency are violated. At this condition transaction will rollback.

edited Nov 01 '14 at 09:54

answered Oct 22 '14 at 14:44

M.Namjo

374
2
13

score 0 · Answer 3 · edited Jun 23 '21 at 23:54

Atomicity :
Bunch of statement just take an example of 100 statements which can be insert statement also , if any of the statement failed while processing should revert back remaining statement , which means database should go back original state.

autocommit = false

try{
   statement one ;
   statement two ;
   statement three;
}
catch (){rollback;}
finally(){commit;}

Consistency : If your trying to insert date into database which need to be satisfy the constraints, cascades, triggers like while your trying to insert the data into database but the table has primary key constraints so the data your planning to insert should be satisfy with primary key constraint.

Isolation : if two process are running on database assume one is reading and other is writing the data into database . the reading thread should read only committed data , should not be in-memory data

Durability : once transaction data committed into the database should be same stage , it should not affect the from power failure or system crash any other

database atomicity consistency

3 Answers3

Linked