Properties of a DBMS transaction

 Assume a university database has the following two records:

  • Student X has an outstanding tuition fee of BDT 20,000.

  • The University Revenue Account currently has a balance of BDT 1,00,000.

A transaction is started to transfer BDT 20,000 from Student X’s account to the University Revenue Account.

During execution, the system successfully deducts BDT 20,000 from Student X’s record (so the new due becomes BDT 0). However, before the University Revenue Account is updated to BDT 1,20,000, the system crashes.

Now answer the following:

  1. Write the four properties of a DBMS transaction.

  2. Identify which ACID property is violated in this scenario.

  3. Clearly justify your answer.

  4. Write appropriate SQL transaction statements to prevent this type of violation.

DBMS Transaction Problem – Detailed Answer (ACID Properties)

Question Scenario:

Suppose a university database contains two records:

  • Student X has a tuition due of BDT 20,000.
  • The University Revenue Account shows BDT 1,00,000.

A transaction is initiated to transfer BDT 20,000 from Student X’s account to the University Revenue Account. During execution:

  • The system successfully deducts BDT 20,000 from Student X (new due = BDT 0).
  • Before updating the University Revenue Account (which should become BDT 1,20,000), the system crashes.

1. Four Properties of a DBMS Transaction (ACID Properties)

1) Atomicity

Atomicity means "All or Nothing". A transaction must complete fully or not execute at all. If any part of the transaction fails, the entire transaction must be rolled back.

2) Consistency

Consistency ensures the database moves from one valid state to another valid state. All integrity constraints and rules must be maintained after the transaction.

3) Isolation

Isolation ensures that multiple transactions running at the same time do not interfere with each other. Intermediate results of a transaction are not visible to other transactions.

4) Durability

Durability guarantees that once a transaction is committed, its changes are permanent even if the system crashes afterward.

2. Which ACID Property is Violated?

The violated property is: Atomicity.

3. Justification

In this scenario:

  • BDT 20,000 was deducted from Student X → Student due = BDT 0 ✅
  • System crashed before updating University Revenue Account ❌

Now the database is in an inconsistent state:

Record Amount
Student X Due BDT 0
University Revenue BDT 1,00,000

The correct revenue should be BDT 1,20,000. Since only half of the transaction was executed, the All-or-Nothing rule is broken. Therefore, Atomicity is violated.

4. SQL Transaction Statements to Prevent This Violation

Proper Transaction Control Using BEGIN, COMMIT, and ROLLBACK

BEGIN;

-- Step 1: Deduct amount from Student X
UPDATE students
SET tuition_due = tuition_due - 20000
WHERE student_id = 'X';

-- Step 2: Add amount to University Revenue Account
UPDATE accounts
SET balance = balance + 20000
WHERE account_name = 'University Revenue';

COMMIT;

How It Prevents the Problem

  • If both updates succeed → COMMIT saves changes permanently.
  • If system crashes before COMMIT → DBMS automatically performs ROLLBACK.
  • This ensures the transaction is either fully completed or fully cancelled.

Final Summary

Topic Answer
Four Properties Atomicity, Consistency, Isolation, Durability
Violated Property Atomicity
Reason Transaction partially executed (only deduction done)
Prevention Use BEGIN, COMMIT, ROLLBACK

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)