Some SQL Theory| DB Engines + ACID

We are very much worried about CRUD concept of SQL but we forget to show our concern towards how database is actually stored generally?

Such Behind the scenes is must to know!

DataBase Engines:

In mysql> if you do , show engines;

Everything is just a file! One of the fundamental block of storing data.

So you write all your queries in mysql and save that file as the <filename.mysql >. So once you save this , which engine is going to process is the question we need an answer for.

So we have different engines to process your files.

So the way the file is going to be processed is known as engine!

File with constraints restricts us!

Engines decide which constraint or feature they want to support for.

Point time recovery , foreign Key constraints , encryption, index caches , T-Tree caching . We need such features /constraints for user and engines decide for that.

One of the popular engines are INNODB , MYISAM! And many others.

You don’t need to go in detail of it , just go through it once in documentation and don’t memorize at all.

So Database Engines are the ones which process es your files and which features they support totally relies on which engine you are working on. And majority of time we love and work on InnoDB.

ACID:~

Any database you pick up must show these four properties that resides in ACID, up to what scale and degree is dependent on what DB Engine you are choosing along with system and hardware requirements.

A- Atomicity denotes that every single transaction should be atomic in nature that means whatever you query should be entirely correct or straight away roll out. Either transaction should be happen completely or get aborted.

C-Consistency means database must be consistent , if this syntax gives you error , it should always give error.

I-Isolation happening everywhere , a bundle of transactions happen at every moment of time but all of them are completely isolated from each other.

D- Durability , how durable your DB is? Even if something happens to S/W or H/W DB must have something to recover back the files and data.

This is all about ACID you should know!