MySql

MySQL is the most popular Open Source SQL database management system. It's distributed by Oracle.

Introduction

MySQL is a database management system. A database in MySQL is a structured collection of data. To access, add, update data, it need MySQL Server.

MySQL is relational database. It stores data in separate tables. User can set up relation between several tables such as : one-to-one, one-to-many, and many-to-many relationship. Base on these relationship, user can organize their database in well structures.

MySQL use SQL - Structured Query Language. It's the most common standard used to access database.

MySQL can work in client-server or embedded system. It means MySQL flex enough to use in small application or even in large application using client-server model.

MySQL become more and more reliable, fast, scalable, and easy to use. It has well support by Oracle so feel free to use it in most your software.

Architecture

Basically, MySQL logical architecture contains 3 elements : Connection handling, Query parser/optimize, and Storage Engine.

Connection handling : manage the access to mysql server such as : authenticate, create thread to handle connection. After create connection, it will check whether client has permission for queries it issues. Example : client have permission to SELECT , UPDATE in database A or not.

Query Parser/Optimize: MySQL parses query into internal structure and applies several optimization such as : index uses, rewrite query, ordering,.... If query is already in cache, MySQL will return the data set in cache for certain query. If not, it will start execute query to storage via storage engine.

Storage Engine: MySQL store database (also known with name schema) as sub-directory of it's data directory underlying filesystem. Each table created , MySQL will store table definition in file with extension frm.

There are several storage engines, each storage engine has different index , structure,....

InnoDB Engine: It's transactional engine, means it support transaction. It can recover database back to previous state if query execution incomplete. It's the most common database engine use in real application because it can measure data consistence.

InnoDB Engine provide high performance lookup with primary key , beside it provide term secondary key index.

InnoDB Engine provide row locking, table locking.

MyISAM Engine: it's none transactional engine. It's very good to store read-only database.

MyISAM only support table locking so it's reason why it's very good to store read-only database.

These two engine above is the most common engine used in MySQL, it also contains several engines. Get more detail about these engine in MySQL offical site.

Locking

Situation: multiple query want to change the data at the same time. It's concurrency problem that MySQL has to handle. MySQL handle concurrency problem with two level : server level(cpu process level) and storage engine level (logic level).

This section is about logic level log : shared lock and exclusive lock (Red lock, Write lock).

Shared Lock: it allow other transaction read a locked row but cannot write on that row.

Read/Write Lock: it disallow read/write on locked row from any other transaction.