MySQL

BASIC RDBMS CONCEPTS:

ACID CONCEPT IN MYSQL

Atomicity: ( ALL or Nothing) - Transaction is either full or fully rollback. Transaction is either committed or rollback.

Consistency: Innodb Crash Recovery. Transaction from one valid state to another. Rollbacks to old state before transaction was started. A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started.

Isolation: A transaction in process and not yet committed must remain isolated from any other transaction. Default Repeatable Reads.

Durability: Committed data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.

ISOLATION LEVELS:

Read Committed: We can read only committed data

Read Uncommitted: Data can be read from uncommitted data i.e from dirty buffer

Repeatable Reads: (DEFAULT in MySQL): It guarantees the same read within the same transaction

Serializable:Locks each row after reading. Creates timeout and locks. Rarely used as per application needs such kind of action.

Architecture of MySQL:

MySQL Features:

1. Good for Web application, data warehouse, OLTP transaction.

Clients: Network based clients, tools.

MySQL Server: (Connection Pool) - Query Parsing - Analysis - Caching - Optimization

                          (Storage Engine) - MyiASM, InnoDB, Archive, Merge, Memory

File System: ( Logs and File System) - Binary Logs, Relay logs

Client - Server Connection:

1. Each client connections gets its own thread within the server process.
2. The connection queries execute within the same thread which resides on same core.
3. Server caches thread, not created or destroyed each time.
4. Client connects to the server, server authenticates username, originating host and pass
5. Once client connects, server verifies whether client have privileges to run queries on particular table or database.
6. MySQL parses queries to create a internal structure(the parse tree) and applies variesty of optimization. These can be rewriting queries, determining the order how it will read table.
7. Optimizer don't care about the storage engine.
8. Before parsing, server consults the query cache for SELECT Statement.

Locks:

1. Share Locks or Read Locks: Don't block any read users
2. Exclusive Locks or Write Locks: Blocks user for writing and reading. Write locks advances to read lock but opposite can not happen.
3. MyiSAM does table level locking.
4. InnoDB & XtraDB does row level locking.
5. Deadlock: A deadlock is when two or more transactions are mutually holding and requesting locks on the same resources

Transaction:

1. Start Transaction; - should be begining of script
2. commit ; - At the end of script
3. InnoDB and NDB ( Network DB)
4. Autocommit is ON by default. 1 - ON       0 - OFF
5. Only DML can be run in transaction

MVCC:

1. Multi version concurrency control.
2. InnoDB implements MVCC by storing with each row two additional, hidden values that record when the row was created and when it was expired (or deleted). Rather than storing the actual times at which these events occurred, the row stores the system version number at the time each event occurred. This is a number that increments each time a transaction begins. Each transaction keeps its own record of the current system version, as of the time it began. Each query has to check each row’s version numbers against the transaction’s version. Let’s see how this applies to particular operations when the transaction isolation level is set to REPEATABLE READ.
3. SELECT

InnoDB must examine each row to ensure that it meets two criteria:

1. InnoDB must find a version of the row that is at least as old as the transaction (i.e., its version must be less than or equal to the transaction’s version). This ensures that either the row existed before the transaction began, or the transaction created or altered the row.
2. The row’s deletion version must be undefined or greater than the transaction’s version. This ensures that the row wasn’t deleted before the transaction began.

Rows that pass both tests may be returned as the query’s result.

4. INSERT

InnoDB records the current system version number with the new row.

5. DELETE

InnoDB records the current system version number as the row’s deletion ID.

6. UPDATE

    InnoDB writes a new copy of the row, using the system version number for the new row’s version. It also writes the system version number as the old row’s deletion version.