3k views

Consider the following transaction involving two bank accounts $x$ and $y$.

  read(x); x:=x-50; write (x); read(y); y:=y+50; write(y)

The constraint that the sum of the accounts $x$ and $y$ should remain constant is that of

1. Atomicity
2. Consistency
3. Isolation
4. Durability

edited | 3k views
+1
Option B is correct.

Atomicity does not satisfies any business logic constraints.

Atomicity just ensures that a transaction either completes successfully or in case of failure takes the database  state to a state which existed before executing the transaction.
+5

Why atomicity is not correct .Consider below Txn

read(x); x:=x-50; write (x); read(y); y:=y+20; write(y)

1.Initially X = $1000, Y=$2000  .SUM(X+Y) = $3000 2.Execute above Txn COMPLETELY(Atomically).Deduct 50 from X and Add 20 to Y . 3.Final X=$950  Y=$2020 . SUM(X+Y)=$2970

So even though we executed above TXN Atomically our SUM(X+Y) is inconsistent.

That's why Atomicity does't ensure that sum is unchanged after txn. But "CONSISTENCY" property explicitly says that if $50 is deducted from X then$50 must be added to Y.

REFERENCE: page no. 566, chapter 15,Database System concepts by Sudarshan-Korth ,Third line from bottom of page.

+29
@Proton What are u doing here ?
U r deducting 50 from x and adding only 20, and then showing then sum is inconsistent ?
Sum is not inconsistent, U r inconsistent :P
+2
As,ensuring consistency for an individual txn is the responsibility of the application programmer.He may put faulty business logic.
+1

Constraint that the sum of the accounts x and y should remain constant.

In this question  $Atomicity ---> Consistency$. I think consistency means your transaction should satisfy what is defined as consistent with respect to your DB.

@Sachin Mittal 1 ji's comment

In general, isolation preserves consistency.
But here it so happens that if we ensure Atomicity then also consistency is guaranteed. BUT the constraint that "the sum of the accounts x and y should remain constant", is still the constraint of Consistency.
Automaticity has only one constraint: "Full or None".

In general, isolation preserves consistency. I think this is not correct.

B. Consistency

In the given transaction Atomicity guarantees that the said constraint is satisfied. But this constraint is not part of Atomicity property. It is just that Atomicity implies Consistency here.
by Veteran (431k points)
selected
0
Sir I doubt. Its part of atomicity, as in if it fails in between then the whole transaction needs to be rolled back for keeping the sum constant.

So either both completes or None. no?
+2
@Gate-Keeda

Can you give an example for a transaction which violates Consistency but satisfies Atomicity?

That example should make it clear.
+4
+5
@Gate-Keeda So, any constraint not satisfied by Atomicity and is needed for database, comes under Consistency? That makes sense. And then answer would be Atomicity.
+13
In general, isolation preserves consistency.
But here it so happens that if we ensure Atomicity then also consistency is guaranteed. BUT the constraint that "the sum of the accounts x and y should remain constant", is still the constraint of Consistency.
Automaticity has only one constraint: "Full or None".
0
@Arjun Sir and @Bikram Sir

Here only condition not satisfies is durability, as there is no commit in transaction.

But all other Atomicity, Consistency and Isolation satisfies

Now we have to check better option among these three.

If we can say atomicity implies consistency, then can we not say , atomicity also implies isolation,

Now how can we tell consistency more powerful than isolation or isolation more powerful than consistency?
+9
@srestha While solving GATE questions you should give twice the time just to read the question. Only after understanding properly what is asked, start solving it. Doing like this will certainly fetch you more marks. Can you do this for this question?
+3

"sum of the accounts x and y should remain constant"

i.e.sum of accounts before x transfer money to y and x transfer money to y is same. and that is why consistency is answer. rt?

Sometime gate question is too tough to solve in 2 min and some times it is so easy we also r in little bit doubt of it

+12
Yes, exactly. A calm mind gets a lot of mark in GATE :)
+1
@Arjun sir. Suppose the answer is Isolation to the modified version of this problem. Can you please help what can be the modified version?This will clear some doubt.
Kindly check out PPT for Transaction chapter in below link.( Part 5: Transaction Management Ch. 15)

http://www.cse.iitb.ac.in/~sudarsha/db-book/slide-dir/

Question is directly taken from Korth. And wording almost completely matches to Consistency requirement.
by (163 points)
0

## Explaination:

Transaction to transfer $50 from account A to account B: 1. read(A); 2. A:=A-50; 3. write(A); 4. read(B); 5. B:=B+50; 6. write(B); • Atomicity requirement • if the transaction fails after step 3 and before step 6, money will be “lost” leading to an inconsistent database state. • Failure could be due to software or hardware. • the system should ensure that updates of a partially executed transaction are not reflected in the database. • Durability requirement • once the user has been notified that the transaction has completed (i.e., the transfer of the$50 has taken place), the updates to the database by the transaction must persist even if there are software or hardware failures.

• Consistency requirement

• (in above example) the sum of A and B is unchanged by the execution of the transaction.

• In general, consistency requirements include

• Explicitly specified integrity constraints such as primary keys and foreign keys.

• Implicit integrity constraints- e.g. sum of balances of all accounts, minus sum of loan amounts must equal value of cash­ in­ hand.

• A transaction must see a consistent database.

• During transaction execution the database may be temporarily inconsistent.

• When the transaction completes successfully the database must be consistent.

• Erroneous transaction logic can lead to inconsistency

• Isolation requirement

• if between steps 3 and 6, another transaction T2 is allowed to access the partially updated database, it will see an inconsistent database (the sum A + B will be less than it should be).

• Isolation can be ensured trivially by running transactions serially.

by Active (2k points)
0
This should be the marked answer
+1 vote
Consistency so option b
by Boss (11.6k points)