Is Your Database Meeting Your Needs?

Introduction

    Data is new oil this is heard everywhere and also said to be heart of the organization. Well I totally agree to this, every organization makes decisions either long term or short term based on the available data.    

    Database is where data resides, this makes choosing right database very much important and challenging as well. There are different types of database SQL vs NoSQL, centralised vs distributed, in memory vs persistence storage, etc and each has its own merits and demerits in various terms including how reliable it is. 

    Today we will see three concepts ACID, BASE and CAP each which defines the various dimensions such as consistency, guaranteed transaction, stable distributed system, and so on.  There is no best concept, each carries its own merits and challenges. Let's get deep dive into how each concept helps in assuring data consistency or data availability or transaction reliability and how each concept is different from another.

ACID

Credits: https://morpheusdata.com/cloud-blog/when-do-you-need-acid-compliance/

    For any database, let say SQL/NoSQL/GraphDB ACID is an important first principles when it comes to handling a transaction.

    Before we get further, let's know about Transaction. It is collection of queries, which is considered as one task. So we need to run all or none at all. If something in between fails either intentional or non-intentional, everything needs to be rolled back to the start just before the transaction begin.

E.g. XYZ Transferring 100rs to ABC

• Transaction Begin
• Read Balance from XYZ
• Proceed only if XYZ > 100rs
• XYZ Debit 100rs
• ABC Credit 100rs
• Transaction Commit 

    Here in the above example we have 100rs transaction happening if anything fails in between, XYZ should continue holding back the 100rs, rather its debit from him but not credited to ABC. That is not good right.

    There is another flavour of transaction where it would be read only but still require to be in transaction. We call it Read-only transaction. Well one might think why we ever need it, let's see with an e.g. its requirement:

• Without Transaction:
• Count of Number of Deals booked -> Job 1
• 1 Deal got cancelled -> Job 2
• Count of Dealers along with count of Deals booked -> Continuation of Job 1
• Here you see there would conflict in the answers produced by Job 1
• With Transaction
• Transaction Begin -> Job 1
• Count of Number of Deals booked -> Job 1
• Count of Dealers along with Deal booked -> Continuation of Job 1
• Transaction Commit

    In this case, all the reads within this transaction gets consistent data from the database even during interruption from another jobs, the current transaction would not be affected. Usually used while building huge report or taking snapshot.

Atomic

    Atomicity represents as single unit, it should not break. All the queries in a transaction must succeed, else nothing occurs - all should be rollback. There are two kinds of issue causing failure, during a transaction.

• Query failure, such as duplicate primary key insert or update invalid data and so on
• Database failure, such as hardware crash, power issue and so on

    In case of query failure, we still have the control and performing the rollback. But in case of database failure such as hardware crash or crash while performing commit. These are the serious issues which needs to be handled sensitively.

    Some databases write in the disk directly and in the end mark the transaction as success, here the commit would be faster but involves bad data risk. To avoid the bad data, some databases copy the old data before applying changes we call them read-copy-update, well here hard disk space grows rapidly though. 

    While some databases maintain changes in the RAM and only during success write in the database. Here the rollback is faster but holds issues such as RAM overload and RAM crash. As you know there is no free tea, we need to sacrifice on fast write or fast rollback here in these two scenarios.

    Typically several systems implement atomicity by journaling. The system synchronises the logs as necessary after changes have successfully taken place. After crash recovery, ignores incomplete entries. Although different databases implementations vary depending on many factors such as

• Concurrency issues, allowing the threads of users working on same data using lock mechanism. Having time gaps to avoid dead locks.
• Control the number of operations included in a single transaction. Here both the throughput and the possibility of deadlock is controlled. The more the operations included in a transaction, that more likely it is that a transaction will block other operations and deadlock will occur.
• Start databases only when stale transactions are clear, well in some databases they start immediately and meanwhile crashed changes will be fixed in parallel.

    Although the principle of atomicity - i.e. complete success or complete failure - remains.

Consistency

    Consistency of the data in persistent disk, as well consistency means data is in a consistent state when a transaction starts and when it ends. The progress made from one valid state to another, taking care of auto trigger changes, ensuring constraints and so on. For example:

• When the data transfers from A to B, it should verify B exists and values after transaction completes should be valid without any corruption
• Another example from Hussien's course, where he explains having two tables
• Picture: Picture ID, Number of Likes to the Picture
• User_Liked_Picture: UserID, Liked Picture ID
• Here there should be consistency across both the tables where count of likes of the picture should match with users who like it. If there is a mismatch we can call this scenario as inconsistence referential integrity.

    Consistency in reads in also equally important, it would be little simple in single db where you write and read from the same. But having distributed databases makes it complicated, if a write is committed and we tend to read from it, well if its same database it would be prompt but in distributed the write needs to be reached from where it is asked for read. But if we get at times stale information, this is where we have concept called Eventual Consistency. It says it's not consistent but will provide required information after sync. Early results of eventual consistency data queries may not have the most recent updates because it takes time for updates to reach replicas across a database cluster.

    

    At times one would need strong consistency, well we need to have synchronous synchronisation though it would slower but strong vs asynchronous synchronization which would be eventual but faster.

Isolation

    Usually many transaction run in parallel. Isolation guarantees the intermediate state of a single transaction should be invisible to other transactions, leaving the database in the same state that would have been obtained if these transactions were executed sequentially. As a result, these transactions that run concurrently appears to be executed in the serialized or sequential order.

    For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction see the transferred funds in one account to the other, without leaving both the accounts with credit, as well nor debits in both.

    There is problem which isolation property faces due to concurrency, called as read phenomena.

Read Phenomena(Problems): When a transaction reads data that another transaction might have changed. Below are few phenomenas:

• Dirty reads: Reading the data which is not fully committed
• Non repeatable reads: Reading twice the same data. Not necessary using the same query, could be different as well but reading the same data. For example read the sum of picture likes and users liked the picture -- here the values would have changed due to other transaction, resulting the mismatch between sum of likes from user table vs total number of likes from pictures table. 
• Phantom reads:  Re-reading the non-existence data, such as reading the sum of likes but then new record got inserted by another transaction and you have not read it yet.
• Lost updates / Serialization anamoly: Read the written data, but unable to find it due to other transaction deleted it or modified.

Isolation levels define the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system. A transaction isolation level is defined by the above phenomena.

• Read Uncommitted Isolation: Allowing to read uncommitted changes across transactions, this will be fast as nothing is needed to be maintained. But down fall there would be several dirty reads happening.
• Read Committed Isolation: Most popular and default isolation level, while running a transaction can and only see the committed changes by other transaction.
• Repeatable Read Isolation: Transaction when reads a row will remember it and keep it unchanged which would be been affected by other transactions. This remembered value remains the same as read before hence when re-read happens it would not be affected. The caveat here is this is expensive, but at the cost of valid reads.
• Serializable Isolation: All the transaction run here serially, losing the concept of concurrency. This would be the slowest of all.
• Snapshot Isolation: Each transaction takes the snapshot of the committed changes before the start of the transaction and here the new inserted rows causing Phantom reads will not occur as it gets read from the snapshot. If the number of concurrent transactions increases it would costly in terms of space holding the N number of snapshots.

Credits: https://alxibra.medium.com/isolation-level-in-rails-847edc9e347d

Durability

    All the transaction successfully information needs to be stored in persistent storage (nonvolatile system storage), even in the event of a system failure such as hardware crash or power failure, we can still recover the committed changes it should not be lost.

    Some databases to make write faster they tend to write in to RAM and in backend batch wise snapshot to the disk, well yes it would be faster but less durable.

    A realtime example ensuring durability is, in an application that transfers fund from one account to another, the durability property ensures that the changes made to each account has made persistent changes and irreversible.

Durability Techniques:

    Below are few techniques ensuring high durability:

• WAL - Write ahead log
• Write in the direct database are expensive and also risky when performing uncommitted changes, during failure need to revert everything.
• WAL records the changes in the log in stable storage and later transferred to the db.
• Well here there is always an issue with OS Cache, which would write in RAM and transfer in the hard disk batch wise, in DB we need to record every item in persistent database. Hence calling fsync OS command resolves this, as it forces writes to always to the disk.
• Checkpoint is a point where the program writes all the changes specified in the WAL to the db, and clears the logs.
• Snapshot
• Generate a complete snapshot of the data set in the memory within the specified time interval, and then save it in the hard disk.
• The save operation could be synchronous or asynchronous.
• But it has primary two issues with this technique:
• The last in memory data may be lost. Because there is a time interval for generating snapshots, it will cause the last data to fail to generate a snapshot.
• Time-consuming and performance-consuming, because the data in the memory needs to be completely snapshotted and written to the hard disk.
• AOF - Append Only File
• Write any write operations to the log file, only append files but not rewrite files
• AOF file is essentially a redo log, through which database state can be restored
• When the data is restored, the file is read to rebuild the data, that is, if database is restarted. The write command will be executed from front to back according to the content of the log file to complete the data recovery work.
• As the number of commands executed increases, the size of AOF file increases, which can lead to several problems, such as increased disk usage and slow restart loading. But there are several techniques to control this issue.

So far we have seen ACID which has an impact while selecting a database where loads to transactions are involved. Mostly we have transactions running in the relational databases, which ensures the consistency.

BASE

    In distributed environment in general BASE concept is applied, as here the ground rule is availability over consistency. BASE model provides high availability as the primary principle.    

    NoSQL in general follows BASE.  NoSQL, the database which stores data in documents such as key-value pair, wide-column, pure documents, graph rather the traditional relational model easy to query. NoSQL enables rapid changes, believes in zero downtime, supporting large users from globally distributed.

    

    As said, NoSQL databases usually it adheres to BASE. But we have few NoSQL databases that holds certain degrees of ACID compliance.

    Below are the three properties of BASE.

Basic Availability

    The system is guaranteed to be available for all the users all the time, even in the event of failure. Ideally the system should always available, should reach the replica in case of failure, should not lock up and the data should be available while the operation is in progress. Immediate consistency is not the priority, the key is availability assurance.

Soft-state

    As we are not consistent, the stored values may change due to the eventual consistency model. Here we could get stale information which was before sync. It becomes developer's responsibility to make sure of the consistency.

Eventual consistency

    The fact currently the data might be stale, but the system state is gradually replicated to all the nodes. Data reads are still possible, but it might not reflect the reality.

From the above principles it's quite clear, BASE favours availability over consistency.

CAP

Credit: https://medium.com/@sumitsethia94/consistency-or-availability-of-databases-do-you-really-understand-cap-8ecf2b3bb099

    Now let's look into CAP theorem, it is key to understand when your database needs to be distributed. It can be applied to both NoSQL and SQL databases.

    Some systems needs to be live all-time 24*7*365, well it is hard to have them running in single server and single data centre, as there could be hardware crash, power failure or any natural disaster in the city. As a result we always need to anticipate and plan for system failures and design our system running from different servers and different locations all the time to users better. Additionally data should be reliable along with highly available.

    The CAP theorem is also called Brewer's Theorem, because it was first advanced by Professor Eric A. Brewer during a talk he gave on distributed computing in 2000. Two years later, MIT professors Seth Gilbert and Nancy Lynch published a proof of "Brewer's Conjecture".

Consistency

    This consistency is different from ACID property's consistency. Here a consistent view of data at each node means all the nodes always have the same and most recent view of the data. If not yet the most recent or if it is not consistent, error must be returned. Any write to the database should be immediately replicated to the replicas. Though this will be most challenging to achieve, syncing globally at same time is hard. There are several techniques such as change log capturing, querying based on the timestamps, acknowledge from maximum nodes and so on to overcome and provide the fastest replication across the nodes (no here I didn't want to call this eventual consistency).     

    Also at times consistency goes on toss when we read from the non-updated cache. Example of consistency would be maintaining all the changes done to your bank balance should be same all the times in every system where it is distributed and saved. And every retrieval should give the same balance from wherever it is checked.

Availability

    Availability of data at each node means the system always responds to requests. Here we would need to get the data but it may not be the most recent. Getting the response is most important, it should not give error as the response. Caching can be used here, to respond back even though it might be stale. Well yes this might sound contradicting with consistency, but ideally in availability getting response is very important. Taking YouTube as an example, we would need videos may not have all the videos or comments count up to date.      

Partition Tolerance

    Tolerance to network partitions means systems remain online if network problems occur. During power shutdowns, or natural disaster, the partial system might be down, the system is still tolerance to accept and processes the user's requests. Ideally here replication across nodes and network helps. For example, if I hold account in Chennai bank branch but there is power shutdown in the city, it should not stop me from accessing the money from other or same location.

CAP Gaurantees

    In CAP theorem, guarantees only two properties at a time, we cannot achieve all three together. We need to tradeoff based on our requirement. From few examples let's see why it could be only 2 not all 3. Here it talks that either the system can be consistent or available not both for the distributed design.

AP - Availability and Partition Tolerance

Here the application needs to be provide high levels of availability. Eg:

• You have 1 master node and 2 replica nodes
• Write to the master, get the success immediately after write access
• Asynchronous replication takes place in the backend
• 1 of the replica lost it connection with the master for time being
• Now the disconnected replica is contacted it gives the response but its stale
• As per our requirement it should be available we are good here, but not consistent
• YouTube, here needs to be highly available

CP - Consistency and Partition Tolerance

Here the application needs to be all time consistent. Eg:

• You have 1 master node and 2 replica nodes
• Write to the master, get the success only if all the nodes or maximum nodes get the replica applied successfully
• Here the write is blocked until it gets success message
• The blocking hence is making the system slow and not available
• The replica set nodes in the system send a heartbeat (ping) to every other node to keep track if other replicas or primary nodes are alive or dead. If no heartbeat is received within N seconds, then that node is marked as inaccessible
• Yes we can call it partition tolerance, if one replica goes down the other can still serve the purpose
• As the commit happens in the end, the consistency is maintained
• Each node also maintains the operations logs, for the fallback server coming up and getting synced like other nodes
• Bank systems, needs to be highly consistent

CA - Consistency and Availability

    CA database enable consistency and availability across all nodes. RDBMS databases are CA guarantee. As these systems are usually in single system hence we can have both Consistent and Available.

    CA database can't deliver fault tolerance. Hence these cannot be used as distributed systems. 

Conclusion

    Today we saw  needs to be ACID properties which helps in determining how to select database based on each of its parameter when performing transaction. As ACID is mostly used in RDMS - SQL because of its nature, it needs to be highly consistent. For NoSQL which is distributed nature, uses BASE which needs to be highly available. ACID is usually used in the banking applications and BASE in general used in social network applications.

    In the end we have CAP Theorem states that is impossible to have both the worlds together which is high availability or high consistency. There are many database build each specialises in either of the parameters, all that we need to understand our requirement and see which best matches with the available databases.

    What is your requirement? Dose the database meets your database meets your needs?

References

https://alxibra.medium.com/isolation-level-in-rails-847edc9e347d

https://www.udemy.com/course/database-engines-crash-course/learn/lecture/28927902?start=0#overview

https://phoenixnap.com/kb/acid-vs-base