Chapter One architecture and history of MySQL

In the process of reading the first chapter, some important concepts have been sorted out.

Lock particle size

Table lock (server implementation, ignoring storage engine).

Row locks (storage engine implementation, server not implemented).

The acid concept of transactions

Atomicity (either all succeeds, or all rolls back).

Consistency (transitions from one consistent state to another).

Isolation (changes made by an office are not visible to other transactions until they are submitted).

Persistence (Once a transaction commits, all modifications are persisted to the database).

Four levels of isolation

READ UNCOMMITTED (unread): The modification is visible to other transactions, even if the transaction is not committed. Also called dirty read.

Read COMMITTED: Before a transaction is committed, any modifications made are not visible to other transactions. This is the default isolation level for most databases, also called non-repeatable read.

REPEATABLE READ (Repeatable Read): When a transaction reads a range of records, another transaction inserts a new record in that range and commits successfully, and the previous transaction reads the record in that range, creating a magic line.

Serialiizable (serializable): By forcing the transaction to execute serially, avoiding the problem of phantom reading, that is, locking on each recorded row of records, which can result in a large number of timeouts and lock contention, which is rarely used in practice.

SET SESSION TRANSACTION Isolation level READ COMMITTED;

Set the isolation level to take effect on the next transaction.

Dead lock

Multiple transactions occupy each other in the same resource and request a lock on the resource occupied by the other, leading to a vicious cycle. Deadlocks can occur when multiple transactions attempt to lock resources in different order. A deadlock can also occur when multiple transactions lock the same resource at the same time.

Transaction #1START TRANSACTION; UPDATE StockPrice SET Close=45.50WHERE stock_id =4and date ='2002-05-01'; UPDATE StockPrice SET Close=19.80WHERE stock_id =3and date ='2002-05-02'; COMMIT; Transaction #2START TRANSACTION; UPDATE StockPrice SET High=20.12WHERE stock_id =3and date ='2002-05-02'; UPDATE StockPrice SET High=47.20WHERE stock_id =4and date ='2002-05-01'; COMMIT

The above two transactions execute the first statement at the same time, update a record, both lock the record, and then the transaction executes the second record, the discovery is locked, so all wait for the other party to release the lock then into a dead loop.

Solve:

The lock request is discarded when the query waits longer than the lock wait time-out setting.

InnoDB processing: Rollback with a minimum of row-level exclusive locks.

Auto Commit

The default is auto-commit (autocommit), and if you do not show the start of a transaction, each query is executed as a transaction commit

' autocommit '

1 or on means enable, 0 or off means disable SET autocommit = 1; Turn on autocommit modification autocommit has no effect on non-transactional tables, such as MyISAM or memory tables.

The use of multiple storage engines (InnoDB and MyISAM) in the same transaction is unreliable, and normally there is no problem, but if the transaction needs to be rolled back, the operation on the non-transactional table cannot be undone.

Multi-version concurrency control

MVVC is a variant of a row-level lock, but in many cases it avoids locking operations and therefore costs less.

InnoDB simplified version of the MVCC: by saving two hidden columns in each row, a column to save the creation time of the row, a column to save the row's expiration time (delete time) of course not the actual time, but the system version number. Each time a transaction starts, the system version number is incremented, and the system version number at the start of the transaction is the transaction version number.

SELECT

A. InnoDB only finds rows of data that are earlier than the current transaction version number (that is, the version number of the line is less than or equal to the system version number of the transaction), so that the rows read by the transaction are either already present at the beginning of the transaction or are either inserted or modified by the transaction itself.

B. The deleted version of the row is either undefined or greater than the transaction version number, which ensures that the rows read by the transaction are not deleted before the transaction.

Only records that meet these two criteria can be used as the result of the query.

INSERT

InnoDB saves the current version number as the row version number for each newly inserted row of records.

DELETE

InnoDB saves the current system version number as the deletion identity for each row that is deleted.

UPDATE

InnoDB to insert a new record, save the current system version number as the line version number, and save the current system version number to the original line as the row delete identity.

Only if the repeatable read and read commited two isolation levels work, and the other isolation levels are incompatible with MVCC, the read uncommitted should always read the most recent row of records and not conform to the current transaction version of the data row. The serialiizable will lock the rows that are read.

Chapter One architecture and history of MySQL