ACID

Atomicity
Summary: If multiple data updates occur during a transaction in databases/tables, all of them will either succeed or fail together. It views the transaction process as a whole.

Consistency
Summary: Consistency means that until a transaction is fully completed (constraints, cascades, triggers), the values of data affected by the transaction revert to their previous valid values. It involves transitioning the current state of data in the database to the next valid state.

Isolation
Summary: To prevent transactions from affecting each other, transactions need to be executed serially for isolation. Relevant and affected data sets are locked during the transaction until the transaction succeeds or fails.

Durability
Summary: Durability is the ability of the system to revert to the previous valid data state in the event of a physical or operational error during a transaction.

Question 1: What does ACID stand for, and why are ACID properties important in database transactions?

ACID stands for Atomicity, Consistency, Isolation, and Durability. ACID properties are crucial for ensuring the integrity and reliability of database transactions.

Question 2: How does Two-Phase Commit (2PC) contribute to achieving ACID properties in distributed transactions?

Two-Phase Commit ensures Atomicity and Consistency in distributed transactions by coordinating multiple participants. It involves a preparation phase and a commit/abort phase based on participants' votes.

Question 3: In which scenarios is eventual consistency recommended over strong consistency, and why?

Eventual consistency is recommended in distributed systems prioritizing high availability and performance. Examples include social media platforms and e-commerce websites, where temporary inconsistencies eventually converge to a consistent state.

Question 4: What is the difference between optimistic and pessimistic concurrency control, and when should each be used?

Optimistic concurrency control assumes minimal conflicts and is suitable for low contention scenarios. Pessimistic concurrency control locks resources and is ideal for high contention situations. Choose based on the likelihood and level of conflicts.

Question 5: How do you manage locks in an ACID-compliant system, and what strategies can be applied?

Strategies include using timeouts, lock detection, lock ordering, fine-grained control, optimistic concurrency control, and retrying aborted transactions.

Question 6: How does database logging contribute to achieving ACID properties?

Database logging, through techniques like write-ahead logging, ensures Atomicity, Consistency, Isolation, and Durability by recording transactions and their effects on the database. It aids recovery and maintains integrity.

Question 7: What is the impact of long-running transactions on database performance, and how can you mitigate this issue?

Long-running transactions negatively impact performance. Mitigation involves optimizing transaction logic, using appropriate isolation levels, implementing timeouts, and considering alternative architectures.

Question 8: How does timestamp-based concurrency control contribute to maintaining Atomicity and Consistency in database transactions?

Timestamps prevent conflicts by establishing the order of transaction execution. They help prevent updates from being lost and maintain consistency by prioritizing older transactions.

Question 9: Explain the role of Multi-Version Concurrency Control (MVCC) in balancing concurrency and ACID properties.

MVCC allows concurrent access by creating separate data versions for transactions, enhancing performance in multi-user environments while encouraging isolation and consistency.

Question 10: How can stored procedures and triggers contribute to preserving ACID properties, and what challenges may arise from their implementation?

Stored procedures and triggers may challenge atomicity, isolation, and durability. Challenges include performance issues, locking problems, and the need for careful design and maintenance.

SOLID

• Ensuring that our developed software can easily adapt to future requirements.
• The ability to seamlessly add new features to the code without requiring significant changes.
• Minimizing changes to the codebase even in the face of new requirements.
• Reducing the time loss associated with continuous fixing or even rewriting of the code.

S - Single-responsibility Principle:
The reason for modifying a class (object) should focus solely on the specific responsibility assigned to it. In other words, a class (or function) should have a single purpose and should only fulfill that purpose. The fewer responsibilities a class undertakes, the less it will be subject to changes.

O - Open-closed Principle:
A class or function should preserve existing features and be closed to modifications. This means it should not alter its existing behavior but should be easily extensible to accommodate new features required for future needs. Our application should adopt a model that can quickly adapt and evolve to future requirements.

L - Liskov Substitution Principle:
Without making any changes to the code, we should be able to use subclasses in place of their (super)classes. The derived class should include its own new features, allowing for seamless substitution to achieve a flexible and extensible design.

I - Interface Segregation Principle:
Instead of consolidating responsibilities under a single interface, we should create multiple interfaces that are more specific and customized. This principle encourages the creation of multiple interfaces and ensures a logical separation of responsibilities.

D - Dependency Inversion Principle:
Dependencies between classes should be minimized, particularly where higher-level classes should not be dependent on lower-level classes. When there is a behavioral change in lower-level classes, it should not result in any disruption to the behavior of higher-level classes. Reducing dependencies between classes is crucial for a robust and maintainable software design.