Chapter 1 - Architecture

Question 1

Storage engine architecture

Answer 1

Server tasks and query processing is separated from data storage and retrieval

Question 2

Architecture logical view

Answer 2

Connection, authentication, security
Query parsing, optimization, caching, procedures, triggers, views
Storage engine API

Question 3

Threads per connection

Answer 3

One

Question 4

Threads creation

Answer 4

Threads are cached, also thread pooling plugins are allowed

Question 5

Authentication

Answer 5

Username, host, password or SSL

Question 6

Query execution

Answer 6

Creating parse tree, then applying optimisations like rewriting query, determining table reading order, choosing indexes.

Question 7

Query cache

Answer 7

Depends on hash comparison. Is called before pursuing the query

Question 8

Locks

Answer 8

Read and write. Write blocks both reading and writing. Read are mutually non blocking. Write has higher priority, they can advance over read locks.

Question 9

Lock performance

Answer 9

They have overhead. Storage engine have their own locking policies

Question 10

Table lock

Answer 10

When writing whole table is locked. Used for alter table.

Question 11

Row locks

Answer 11

Provides best concurrency and biggest overhead. Implemented on the level of storage engines.

Question 12

ACID

Answer 12

Atomicity
Consistency
Isolation
Durability

Question 13

Atomicity

Answer 13

Indivisible unit of work. All or nothing.

Question 14

Consistency

Answer 14

Moving from one consistent state to another

Question 15

Isolation

Answer 15

Invisible to other transactions

Question 16

Durability

Answer 16

Committed changes are permanent

Question 17

Read uncommitted

Answer 17

Transactions can view results of uncommitted transactions.

Question 18

Dirty read

Answer 18

Reading uncommitted data

Question 19

Read committed

Answer 19

Transactions see only committed data.

Question 20

Non repeatable read

Answer 20

Running same statement twice with different results

Question 21

Repeatable read

Answer 21

Guarantees that rows look the same every time they are read in single transaction.

Question 22

Phantom reads

Answer 22

Selecting range of data when another transaction just added something in that range. Two reads can result in different contents.

Question 23

Serializable

Answer 23

Transactions are forced to be ordered

Question 24

How we can replace transaction with lock table

Answer 24

?

Question 25

Deadlocks

Answer 25

Two or more transactions are holding and requesting lock on the same resource. They create cycle of dependencies.

Question 26

InnoDB deadlock handling

Answer 26

In case of circular dependencies it returns error.

In other cases rollbacks transaction with fewest exclusive locks.

Question 27

Transaction logging

Answer 27

Storage engine first changes in memory representation of data. Then wires it to transaction log in HDD. The last one is fast because it is sequential I/O operation. Later disk is updated according to that log.

Question 28

Autocommit

Answer 28

Each query is executed in transaction by default. DDL usually commits the transaction.

Question 29

MVCC

Answer 29

Allow skipping locks and has lower overhead then normal locking strategies.
It keeps snapshot of data as it was at some point in time.
InnoDB for this purpose stores data in two additional columns. One of them is version number which is incremented on every transaction.

Question 30

InnoDB data storing

Answer 30

In one or more files known as table space. It is black box managed by engine

Question 31

Next key locking

Answer 31

Prevents phantom reads. It not only locks only the needed rows but also the gaps in index structure.