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 is one of the most crucial properties of ACID. When DB executes simultaneous and parallel transactions, it operates as if it is the only one running in the system. During the execution, none of it should affect any other transactions. There are two ways to accomplish this: optimistic locking and pessimistic locking.

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

	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