MySQL article notes

MySQL noun explanation/meaning mvcc (multiversion concurrency control)

The MySQL InnoDB storage engine, which implements the Concurrency Control Protocol--MVCC (Multi-version Concurrency Control) based on multiple versions (note: Compared to MVCC, is lock-based concurrency control, lock-based Concurrency Control). MVCC The greatest benefits, I believe it is also familiar: Read no lock, read and write no conflict. In an OLTP application that reads and writes less, read-write conflicts are important, greatly increasing the concurrency of the system, which is why at this stage, almost all RDBMS support the MVCC.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read. Snapshot reads, read the visible version of the record (possibly a historical version), without locking. The current read, read is the latest version of the record, and the current read returned records, will be added to the lock, to ensure that other transactions will no longer concurrently modify this record.

Snapshot read

A simple select operation, which belongs to the snapshot read, does not lock. (Of course, there are exceptions, which are analyzed below)
SELECT * from table where?;

Current Read

Current read: Special read operation, insert/update/delete operation, belongs to the current read, need to lock.
select * from table where ? lock in share mode;s Lock
SELECT * FROM table where? for update; X lock
Insert into table values (...); X lock
Update table set? Where?; X lock
Delete from table where?; X lock

Clustered index

Official documents:
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the Clustered index to optimize the most common lookup and DML operations for each table.

• If you define a on PRIMARY KEY your table, InnoDB uses it as the clustered index. ( If the primary key is defined, then InnoDB selects the primary key as the clustered index. )

• If you don't define a PRIMARY KEY for your table, MySQL picks the first index that have only columns as the UNIQUE NOT NULL primary Key and InnoDB uses it as the clustered index. ( If there is no primary key, then MySQL chooses a unique build and the unique column is not NULL as the InnoDB index. )

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic Colu MN containing row ID values. The rows is ordered by the this assigns to the ID InnoDB rows in such a table. The row is ID a 6-byte field, increases monotonically as new rows is inserted. Thus, the rows ordered by the row is ID physically in insertion order. ( If the table primary key and unique principal do not exist, then InnoDB internally generates a hidden column number value and acts as a clustered index. )

2pl-two-phase Locking: Two stage lock

In database and session programs, 2PL is used to ensure thread safety, that is, to acquire locks and release locks. 2PL has two primitive words:

-expanding Phase:locks is acquired and no locks are released;
-shrinking Phase:locks is released and no locks are acquired;
The 2PL is determined to differentiate between the two locks, the Shared locks and the Exclusive locks.

See also: Two-phase locking

Mysql Transaction Isolation Levels: Isolation level

In database systems, Isolations determines the visibility of transactions for other users and systems, PS:ACID (atomicty,consistency, isolation, durability).

Some noun phantom readings appearing in transactional operations

In a transaction of two times the number of data pens inconsistent, for example, one transaction queried a few rows of data, while another transaction inserted a new row of data at this time, the previous transaction in the next query, you will find a few rows of data that it did not previously.

Dirty Read

A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the data read by the latter is incorrect, because the transaction can see the uncommitted results of other transactions.

Non-REPEATABLE READ

The data is inconsistent among the two queries in a transaction, which is probably the original data that was inserted in the middle of a transaction update during the two query process because the transaction can see the results of other transaction submissions.

Four-medium isolation level serializable

This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

Repeatable reads

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

Read committed

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.

Read UNCOMMITTED

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).

See documentation

Dengfa into the article: http://hedengcheng.com/?p=771
MySQL Document: http://dev.mysql.com/doc/

MySQL article notes