Chapter 1 - Architecture Flashcards
Storage engine architecture
Server tasks and query processing is separated from data storage and retrieval
Architecture logical view
Connection, authentication, security
Query parsing, optimization, caching, procedures, triggers, views
Storage engine API
Threads per connection
One
Threads creation
Threads are cached, also thread pooling plugins are allowed
Authentication
Username, host, password or SSL
Query execution
Creating parse tree, then applying optimisations like rewriting query, determining table reading order, choosing indexes.
Query cache
Depends on hash comparison. Is called before pursuing the query
Locks
Read and write. Write blocks both reading and writing. Read are mutually non blocking. Write has higher priority, they can advance over read locks.
Lock performance
They have overhead. Storage engine have their own locking policies
Table lock
When writing whole table is locked. Used for alter table.
Row locks
Provides best concurrency and biggest overhead. Implemented on the level of storage engines.
ACID
Atomicity
Consistency
Isolation
Durability
Atomicity
Indivisible unit of work. All or nothing.
Consistency
Moving from one consistent state to another
Isolation
Invisible to other transactions
Durability
Committed changes are permanent
Read uncommitted
Transactions can view results of uncommitted transactions.
Dirty read
Reading uncommitted data
Read committed
Transactions see only committed data.
Non repeatable read
Running same statement twice with different results
Repeatable read
Guarantees that rows look the same every time they are read in single transaction.
Phantom reads
Selecting range of data when another transaction just added something in that range. Two reads can result in different contents.
Serializable
Transactions are forced to be ordered
How we can replace transaction with lock table
?
Deadlocks
Two or more transactions are holding and requesting lock on the same resource. They create cycle of dependencies.
InnoDB deadlock handling
In case of circular dependencies it returns error.
In other cases rollbacks transaction with fewest exclusive locks.
Transaction logging
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.
Autocommit
Each query is executed in transaction by default. DDL usually commits the transaction.
MVCC
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.
InnoDB data storing
In one or more files known as table space. It is black box managed by engine
Next key locking
Prevents phantom reads. It not only locks only the needed rows but also the gaps in index structure.
