notes on mysql while reading ...

High Performance MySQL

(hm there is a risk already to copy and paste the whole book)

Storage engine - the 3rd level of MySQL architecture is responsible for... among others:
- storage engines can implement there policies and lock granularities (although alter table issues a table lock regardless of storage engine)
- row locks are implemented by storage engines e.g. by InnoDB, Falcon

(
REPEATABLE READ is MySQL’s default transaction isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
InnoDB supports all four ANSI standard isolation levels

)

- InnoDB and Falcon solve phantom reads with multi-version concurrency control (MVCC)
- the underlying storage engines implement transactions: MySQL AB provides three transactional storage engines: InnoDB, NDB Cluster, and
Falcon. Several third-party engines are also available; the best-known engines right
now are solidDB and PBXT.

(
MySQL operates in AUTOCOMMIT mode by default -> SHOW VARIABLES LIKE 'AUTOCOMMIT'; SET AUTOCOMMIT = 1;
Changing the value of AUTOCOMMIT
has no effect on nontransactional tables, such as MyISAM or Memory tables
commit is forced by DDL, LOCK
MySQL will not usually warn you or raise errors if you do transactional operations on a nontransactional table
InnoDB uses a two-phase locking protocol; SELECT ... LOCK IN SHARE MODE ; SELECT ... FOR UPDATE; MySQL server! (not storage engine) also supports the LOCK TABLES and UNLOCK TABLES commands;
actually, InnoDB and others use row-locking mechanism with MVCC, 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); 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
)

MySql storage engines:
SHOW TABLE STATUS LIKE 'user' \G
MyISAM tables created in MySQL 5.0 with variable-length rows are configured by default to handle 256 TB of data, using 6-byte pointers to the data records:
CREATE TABLE mytable (
a INTEGER NOT NULL PRIMARY KEY,
b CHAR(18) NOT NULL
) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;
ALTER TABLE mytable ENGINE = Falcon;