Transactions (ACID vs BASE)

ACID Model: Model often used in SQL

Atomicity: Transactions are made fully or nothing

This property blocks partially executing transactions; thus, there are only states before and after execution. In other words, when a transaction fails during the execution, it must abort the execution. A transaction can fail in many ways, such as hardware failure (network issue, storage issue), software failure (syntax error, heavy traffic), and human error (syntax error).

Consistency: It must maintain data integrity else abort the transaction

This principle is set more toward application rather than a database. It restricts the value of DB to follow the rules set previously. (ex. If the transaction violates the data integrity and the summation of deposit and withdrawal does not add up, it must roll back to its state before the transaction.) Usually, the application handles most of these operations; however, the database can manipulate it by enforcing constraints. (link)

Isolation: All transactions are independent to each other and can not affect other transactions

Isolation rules determine what concurrent transactions can observe. Serializable execution has the same effect as running transactions one at a time in some order; weaker levels permit specified anomalies. PostgreSQL uses both locking and multiversion concurrency control (MVCC), so optimistic and pessimistic application-level locking are not the only mechanisms involved.

Optimistic Locking - a strategy that checks the version number during read and write operations to ensure that it hasn’t been updated between the read and write operations. When it hits a different version, it aborts the process.

Pessimistic Locking - a strategy that locks the resource during its exclusive use. It ensures better integrity; however, it can cause Deadlocks.

Durability: Successful commits must survive permanently

This principle ensures that changes made to the database will survive permanently. It ensures that data will remain against any cases of failure, such as hardware and software failures. Using change logs can make sure this is achievable.

BASE Model: Model often used in NoSQL

Basically Available: Replicate & spread data to ensure its availability

This property values data availability on a system that uses a distributed database. It achieves this goal by replicating and spreading its data to the storage system instead of using one enormous storage.

Soft State: Data consistency is not strictly required

This property shows the main difference between the BASE and the ACID model. It shifts the data consistency responsibility to the developers as it does not strictly require data consistency. As it helps to scale DB horizontally, it makes it highly scalable.

Eventually Consistent: Possible to show old values, but eventually become consistent

This property is one of the most abstract concepts of the BASE model. Although it does not define when the data should become a consistent state, it will eventually become consistent at one point in the future.

Comparisons

Scope of this comparison: SQL and NoSQL are data-model categories, not fixed guarantees of consistency, transaction support, or CAP behavior. The broad labels in the table should not be used to choose a product without checking its actual configuration.

For example, MongoDB supports ACID transactions with documented read and write concern settings. Relational systems can also replicate and distribute data. ACID does not imply a CA system under network partition, and horizontal scaling is not unlimited. Check the transaction boundary, isolation level, replication mode, and failure behavior required by the application.

	ACID	BASE
Main Use	RDBMS	NoSQL
Consistency	Strong	Weak
Consistency Managed By	DBMS	Developers
Data Process	Guaranteed Odered processing	Allow Similar Response
Strength	Integrity, Consistency	Partition-tolerant
CAP	C+A	C+P, A+P
Scaling	Scale-up (limited)	Scale-out (unlimited)
Sharding	Shared	Nothing Shared
Design Focus	Table Design	Query Design
Robustness	Robust DB	Simple DB