Shulou(Shulou.com)05/31 Report--

This article is to share with you the content of an example analysis of Mysql-InnoDB things. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Basic concepts of things

The characteristics of things (ACID)

Atomic atomicity

Consistent consistency

Isolated isolation

Persistent durability

The type of things

Flat things

A flat thing with a save point.

Chain transaction nested transaction

Distributed transaction

The realization of things

Redo and undo redo

Redo: to ensure the atomicity and persistence of things, physical logs are basically written sequentially

Undo: ensure the consistency of things, logical log, undo will generate redo, used for thing rollback and mvcc, random read and write

Redo

Redo log buffer

Redo log file

Force log at commit mechanism to realize the persistence of things

Redo log buffer-> File system cache-> (fsync) disk files

Innodb_flush_log_at_trx_commit

0:master thread completes writing to disk

1: by default, fsync is required every time a transaction is submitted

2: write to the file system cache and do not perform fsync

Log block

512 bytes are the same as the disk mountain size, ensuring that log writes are atomic and do not require doublewrite? Technical

Divided into 512-12-8 = 492

Redo log header: 12 bytes

Redo log contents:

Redo log tail: 8 bytes

LOG_BLOCK_HAR_NO [4 bytes]: location of log block in log buffer

LOG_BLOCK_HAR_DATA_LEN [2 bytes]: log block occupancy size

LOG_BLOCK_FIRST_REC_GROUP [2 bytes]: offset of the first log

LOG_BLOCK_CHECKPOINT_NO [4 bytes]: the value of the fourth byte of the checkpoint when it was last written.

Log grooup

Composed of multiple redo log files

Redo log file

Redo log format

Redo_log_type: type of redo log

Space: the ID of the tablespace

Page_no: offset of the page

LSN (log sequence number)

8 bytes, monotonously increasing

Meaning:

Total number of redo log writes

Location of checkpoint

The version of the page

Undo

Undo segment: within a shared tablespace

(128c) rollback segment > (1024) undo segment. Concurrency of things is limited to 128-1024.

Undo log format

Insert undo log

Update undo log

Purge

Group commit

The level of isolation of things

READ UNCOMMIT: use query statements without locking and may read uncommitted rows (Dirty Read)

READ COMMIT: only record locks are added to records, not gap locks between records, so new records are allowed to be inserted near locked records, so when you use query statements many times, you may get different results (Non-Repeatable Read) EPEATABLE READ: reading the same range of data multiple times will return a snapshot of the first query, no different data rows will be returned, but Phantom Read may occur.

SERIALIZABLE:InnoDB implicitly adds a shared lock to all the query statements, which solves the problem of phantom reading.

Dirty read: in one transaction, data that is not committed by another transaction is read

Unrepeatable: in a transaction, the same row of records are accessed twice and get different results.

Illusion: in one transaction, when records in the same range are read, other transactions add new records to that scope.

The reason why it cannot be read repeatedly is that under the isolation level of READ COMMITED, the storage engine will not add a row lock when querying the record and lock the record with id = 3.

Distributed things

XA

Resource Managers: provides a way to access the resources of things

Transaction Maneger: coordinate and participate in all things in the overall situation.

Application Program: define the convenience of things and specify the operations in global things

Thank you for reading! This is the end of this article on "sample Analysis of Mysql-InnoDB things". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.