Introduction to Mysql-InnoDB transaction learning

mysql video tutorialcolumn introduces InnoDB things learning

Recommended (free):mysql video tutorial

Basic concepts of things

Characteristics of things (ACID )

• atomicity

• consistency

• isolation

• Durability

Types of things

• Flat things

• Flat things with save points

• Chain things Nested transactions

• Distributed transactions

Implementation of things

redo and undo redo
redo: ensure the atomicity and persistence of things, physical logs, basically Is sequential writing
undo: ensures the consistency of things, logical logs, undo will generate redo, used for things rollback and mvcc, random reading and writing

redo

redo log buffer
redo log file

force log at commit mechanism to achieve persistence of things

redo log buffer -> File system cache-> (fsync) disk file

innodb_flush_log_at_trx_commit
0: master thread completes writing to disk
1: By default, fsync is required for each transaction submission
2: Writes to file system cache, does not execute fsync

log block

512 bytes is consistent with the size of the disk mountain, ensuring that log writing is atomic and does not require doublewrite? Technology

## is divided into 512 - 12 -8 = 492

Redo log header: 12 bytes
Redo log content:
Redo log Tail: 8 bytes

LOG_BLOCK_HAR_NO [4 bytes]: The position of the log block in the log buffer

LOG_BLOCK_HAR_DATA_LEN [2 bytes]: The size occupied by the log block
LOG_BLOCK_FIRST_REC_GROUP [2 bytes]: No. The offset of a log
LOG_BLOCK_CHECKPOINT_NO [4 bytes]: The value of the 4th byte of the checkpoint when it was last written? ?

log grooup

Multiple redo log files are composed

redo log file

Redo log format

redo_log_type: Type of redo log

space: ID of table space
page_no: page offset

LSN (log sequence number)

8 bytes, monotonically increasing

Meaning:
Redo Total amount of log writes
Checkpoint location
Page version

undo

undo segment: shared table In the space

(128) rollback segment > (1024) undo segment, the concurrency of things is limited to 128*1024

undo log format

insert undo log

update undo log

purge

##group commit

Isolation level of things

READ UNCOMMIT: Using query statements will not lock, and uncommitted rows may be read (Dirty Read)

READ COMMIT: Only records Add record locks without adding gap locks between records, so new records are allowed to be inserted near the locked records, so when the query statement is used multiple times, different results may be obtained (Non-Repeatable Read) EPEATABLE READ : Reading the same range of data multiple times will return a snapshot of the first query. Different data rows will not be returned, but phantom reads (Phantom Read) may occur;

SERIALIZABLE: InnoDB implicitly replaces all query statements Adding shared locks solves the problem of phantom reading;

Dirty reading: In one transaction, uncommitted data of other transactions is read

Non-repeatable reading: In one transaction, the same row is recorded Was visited twice and got different results.

Phantom reading: In a transaction, when records in the same range are read, other transactions add new records to this range.

The reason for non-repeatable reading is that under the isolation level of READ COMMITED, the storage engine will not add row locks when querying records and lock the record with id = 3.

Distributed things

XA

Resource Managers: Provides methods to access transaction resources

Transaction Maneger: Coordinates various parties participating in global things Things

Application Program: Convenient to define things and specify operations in global things

The above is the detailed content of Introduction to Mysql-InnoDB transaction learning. For more information, please follow other related articles on the PHP Chinese website!