Durability in Insertion/Deletion:

● Insert: The durability property ensures that once an insertion is committed to the
database, it is permanent and will survive system failures .This is typically achieved by
writing the data to a persistent storage medium.
● Delete: Similarly, once a deletion is committed, it is durable, and the deleted data will not
reappear after a system crash. The database uses logging mechanisms to ensure that
committed transactions are preserved.

Ex:-
Example: If an order is deleted from the system and the transaction is committed, even in the
event of a system crash immediately afterward, the order will remain deleted upon recovery.
meaning to understand

ChatGPT said:
ChatGPT

The example means that when you delete an order from the system as part of a database
transaction, the deletion becomes permanent once the transaction is committed (finalized).
Even if the system crashes or shuts down right after the deletion, the order will still remain
deleted when the system comes back online. This is because the database has saved (or
persisted) the change, making it durable and ensuring the deletion is not lost due to the crash.

Atomicity in Insertion/Deletion:

● Insert: When adding a new record to the database, the atomicity property ensures that
either the entire row is inserted or no changes are made at all. For example, if you're
inserting multiple related records the system will guarantee that either all records are
inserted or none.
● Delete: Similarly, atomicity ensures that either the targeted rows are entirely deleted, or
the database remains unchanged. If a failure occurs during deletion, the system rolls
back to the original state, ensuring no partial deletions occur.

Example: In a bank database, if an insert operation for a new account partially fails atomicity
will ensure a rollback.

Consistency in Insertion/Deletion:

● Insert: The consistency property ensures that all database rules are respected during
insert operations. If an insertion violates a constraint the entire transaction will be rolled
back to maintain database integrity.
 ● Delete: Consistency ensures that the deletion does not violate database rules. For
example, if you delete a record that is referenced by a foreign key in another table,
(where the related records in other tables are also deleted) may be defined to ensure
consistency.
● Example of Consistency in Insertion/Deletion:

Imagine a database where every order must be linked to an existing customer. The system
has a foreign key constraint to ensure that no order can exist without a valid customer.

● Scenario: You try to delete a customer who has active orders in the system.
● Consistency: The system will reject the deletion because removing the customer
would leave the orders without a valid customer reference, violating the foreign key
constraint. This ensures the database remains consistent, preventing broken
relationships between data tables.

In this case, consistency ensures that database rules are followed, keeping the data in a valid
state.

Isolation

● Insertion: During insertion, locks (e.g., row-level, table-level) are used to

prevent other transactions from accessing the same data. This prevents
dirty reads, non-repeatable reads, and phantom reads.
● Deletion: Isolation during deletion operations ensures that other
transactions cannot access or modify the data being deleted until the
operation is complete. This is managed through appropriate locking
mechanisms

—---------------------------------------------------------------------------------------------------------------

—-------------------------------------------------------------------------------------------------------------

CID Properties in Database Transactions:

The ACID properties ensure reliable processing of database transactions. Each property
guarantees certain conditions to maintain database integrity. Let's break them down:

1. Atomicity:
○ Definition: It ensures that a transaction is "all or nothing." This means either the
entire transaction is completed, or none of it is.
 ○ Example: Suppose a bank transfer involves withdrawing ₹5,000 from Account A
and depositing it into Account B. If the withdrawal succeeds but the deposit fails,
the transaction will be rolled back, ensuring that ₹5,000 is not lost. Thus, either
both operations succeed or neither does.
2. Consistency:
○ Definition: It ensures that a transaction brings the database from one valid state
to another valid state, adhering to all predefined rules (constraints, triggers, etc.).
○ Example: In the same bank transfer example, the total balance of both accounts
before and after the transaction should remain consistent (e.g., ₹50,000 across
both accounts before the transaction should still be ₹50,000 after). No money
should appear or disappear due to faulty logic.
3. Isolation:
○ Definition: It ensures that transactions are executed independently of one
another. Intermediate states of a transaction are not visible to other transactions.
○ Example: If two people are transferring money from and to the same accounts
simultaneously, the isolation property ensures that each transaction occurs as if
the other is not happening, avoiding interference and data corruption.
4. Durability:
○ Definition: It ensures that once a transaction has been committed, the changes
are permanent, even in the case of a system failure (e.g., power loss or crash).
○ Example: After the bank transfer is completed and confirmed, the database
guarantees that the transfer will remain intact even if there’s a system crash
immediately after the transaction. The database will have saved the changes
permanently

1. Atomicity

Challenge:

● Distributed Transactions: Achieving atomicity across multiple nodes is

complex because a transaction must either commit entirely on all nodes or
roll back completely. This requires coordination among all nodes to ensure
a unanimous commit or rollback decision.

Example:

● In a global financial system, transferring funds between accounts in

different databases located in different regions must either succeed
completely or fail entirely. If the transaction succeeds on one node but fails
on another, it could lead to inconsistencies and potential financial
discrepancies.
2. Consistency

Challenge:

● Data Replication and Synchronization: Keeping data consistent across

replicated nodes is challenging, especially during network partitions or
failures. Consistency must be enforced to ensure that all nodes reflect the
same data state after each transaction.

Example:

● In a content delivery network (CDN), ensuring that a file uploaded by a

user is consistently available across all servers globally, even if some
servers are temporarily unreachable or experiencing high latency.

3. Isolation

Challenge:

● Concurrency Control: Maintaining isolation in a distributed system requires

sophisticated concurrency control mechanisms to prevent transactions
from interfering with each other. This is especially challenging when
transactions are processed on different nodes.

Example:

● In a distributed online booking system, two users attempting to book the

last available seat simultaneously must be isolated to prevent double
booking. The system must ensure that only one transaction can succeed
while the other fails gracefully.

4. Durability

Challenge:

● Data Persistence and Recovery: Ensuring durability across multiple

nodes involves making sure that committed transactions persist even after
node or network failures. This requires robust data replication and logging
mechanisms.

Example:
● In an e-commerce platform, ensuring that a customer's order is
permanently recorded even if the database server handling the transaction
crashes immediately after the order is placed. The system must be able to
recover the transaction from logs and ensure the order is fulfilled.