JDBC ACID Properties – Interactive Visualizer

📘 JDBC ACID Properties — Complete Guide

Content from Day 6 & Day 7 class notes — Trainer: Malikarjun Vanatar, Tap Academy

⚙️ The 5 Stages of JDBC FROM PDF

Every JDBC program follows these 5 stages in order to communicate with a database.

Load Driver

Loads MySQL driver into JVM. "C" in Class and "D" in Driver must be capital.

Class.forName("com.mysql.cj.jdbc.Driver")

Establish Connection

One connection object for the entire transaction.

DriverManager.getConnection(url,u,p)

Create SQL Query

PreparedStatement with ? placeholders set via setInt()/setString().

con.prepareStatement(query)

Execute Query

executeUpdate() returns rows affected; executeQuery() returns ResultSet.

pstm.executeUpdate()

Process Results

Check rows affected, or iterate ResultSet with rs.next().

rs.next() / rowsAffected

💡 Why ACID Is Needed FROM PDF — DAY 6

"Without knowing this concept you are not able to maintain your data properly in your database."

Real-world trigger — Google Pay / PhonePe: You transfer ₹2,000 to your friend. You click yes. ₹2,000 is debited from your account. Suddenly some problem happens. ₹2,000 is NOT credited to your friend's account. What happens? The bank handles this automatically. Within a fraction of a second, the amount is refunded back to your account. How does the bank achieve this? Using a concept called ACID properties. Same with ATM — if withdrawal errors midway, the amount is immediately refunded.

⚠️ If you do not follow all four properties, the data in your database will be treated as garbage value.

AAtomicity

Definition: A transaction must be 100% success or 100% failure. There is no in-between. If it is 50% complete, do not update the database.

Case 1 — 100% Success: ₹2,000 debited from Friend 1 → ₹8,000. ₹2,000 credited to Friend 2 → ₹9,000. Both completed → COMMIT ✅
Case 2 — 50% Problem: Debit succeeds. Credit FAILS. Atomicity says: DO NOT update the database. Rollback everything. The ₹2,000 must go back.
Case 3 — 100% Failure: Payment fails immediately. Nothing debited, nothing credited. This is also acceptable — do not update database.

Atomicity = commit ONLY IF 100% success OR 100% failure. Never for partial completion.

CConsistency

Definition: Data must travel from one consistent state to another consistent state. If data moves from consistent to inconsistent, do not update the database.

Valid: Friend 1=₹10,000 + Friend 2=₹7,000 = ₹17,000. After transfer: ₹8,000 + ₹9,000 = ₹17,000. Same total → COMMIT ✅
Invalid: After broken transfer: ₹8,000 + ₹7,000 = ₹15,000. ₹2,000 has vanished. Do NOT update. ROLLBACK ❌

Consistency = verify combined value before and after remains the same. If not, rollback.

IIsolation

Definition: Each transaction must be isolated from all other transactions. Transactions should not interfere with or clash against each other.

Joint Account Problem: Anita and Ashwini share ₹5,000 account. Both go to different ATMs at exact same time. Bank shows ₹5,000 to both → both approved → ₹9,000 withdrawn from ₹5,000 account! Disaster!
QR Code Example: Multiple people scan same QR code simultaneously. Each transaction is isolated — processed in its own bubble. All correct.

Isolation = each transaction runs independently. No two transactions clash.

DDurability

Definition: Once data is saved to the database, it is permanently saved. No failure — hardware crash, power cut, missile (trainer's example) — can remove it.

If your mobile phone is destroyed, your bank balance is still safe on the bank's server.
UPI app data may be gone, but bank balance remains on server permanently.
"Even if missiles fall and destroy everything, the committed data is still in the bank's server."

Durability = once committed, data remains permanently regardless of any system failure.

📋 ACID Summary Table FROM PDF

Property	Promise	Condition to Update DB	Example
Atomicity	All or nothing	Only if 100% success or 100% failure	Debit + Credit must BOTH succeed or BOTH fail
Consistency	Valid state to valid state	Only if total value preserved before and after	₹17,000 before = ₹17,000 after
Isolation	No interference	Each transaction operates independently	Multiple QR scans at same time, all correct
Durability	Permanently saved	Once committed, always present	Mobile destroyed, bank balance still intact

🔑 TCL + JDBC Methods FROM PDF — DAY 7

TCL = Transaction Control Language. By default autoCommit is TRUE — every statement saves immediately. We set it FALSE so nothing saves until commit().

Method	What it does	ACID Property
`setAutoCommit(false)`	Step 1: disable auto commit — data NOT directly saved to DB.	Atomicity, Consistency
`commit()`	Both updates successful → commit. Permanently saves changes.	Durability
`rollback()`	Not 100% → rollback. Undoes all uncommitted changes.	Atomicity
`executeUpdate()`	Executes UPDATE/INSERT/DELETE. Returns int = rows affected.	Used in isConfirm check
`executeQuery()`	Executes SELECT. Returns ResultSet. Use rs.next() to iterate.	Process Results stage

🗄️ isConfirm() — The ACID Check FROM PDF — DAY 7 DEMO

Checks: if (rs1 == 1 && rs2 == 1) — Exactly ONE row updated for sender AND receiver = transaction 100% correct = COMMIT. Otherwise ROLLBACK.

Live demo problem (Day 7): Trainer added a duplicate employee named "Moto". When credit ran: rs2 = 2 (two rows matched). Since rs2 ≠ 1, isConfirm() triggered rollback. Moto balances stayed unchanged — ACID prevented 200% double-credit corruption.

SQL Query	Method	Returns
`UPDATE employee SET salary = salary - ? WHERE name = ?`	executeUpdate()	rs1 (rows affected)
`UPDATE employee SET salary = salary + ? WHERE name = ?`	executeUpdate()	rs2 (rows affected)
`SELECT SUM(salary) FROM employee WHERE emp_id IN (?,?)`	executeQuery()	ResultSet — consistency check

🎤 Interview Q&A FROM PDF

Question	Answer
Full form of TCL?	Transaction Control Language. Includes commit and rollback.
Default value of autoCommit?	TRUE — every statement immediately committed. Set false for manual control.
What does commit() do?	Permanently saves all changes since last commit. Implements Durability.
What does rollback() do?	Undoes all uncommitted changes. Restores DB to previous consistent state.
NEFT vs RTGS — which uses batch processing?	NEFT uses batch processing (30 min slots, no minimum). RTGS is real-time (minimum ₹2 lakh).
Why use executeUpdate() not executeQuery()?	executeUpdate() for INSERT/UPDATE/DELETE → returns int rows affected. executeQuery() for SELECT → returns ResultSet.
What happens without ACID?	Data becomes garbage value — partial updates, inconsistent totals, concurrent conflicts, data loss.
How is Isolation achieved in Java?	Through Serializable interface and synchronization in multi-threading — sequential execution, slower but secure.
Why turn off auto-commit?	If first UPDATE auto-commits and second fails, you have a permanent partial update with no way to undo it.

ACID Properties Interactive Visualizer

Ready to start