Features of the innodb Storage engine and innodb Engine

Features of the innodb Storage engine and innodb Engine

After using an independent tablespace, what is stored in the system tablespace?

 

1. innodb data dictionary Information

Related to the storage engine.

Frm is the data dictionary of the server and has nothing to do with the storage engine.

2. undo rollback segments.

It can be stored separately.

 

Features of the INNODB Storage Engine

1. Transaction storage engine.

2. Supports ACID features

Redo log and undo log

The redo log implements transaction persistence.

It consists of two parts:

1. redo log buffer in the memory.

2. ib_logfilex of the file system.

Show variables like 'innodb _ log_buffer_size 'in bytes.

The default memory size is 16 Mb.

The quantity is determined by the innodb_log_files_in_group parameter.

 

Redo log stores committed transactions, while undolog stores uncommitted transactions.

Undo log function:

1. Used for rollback

2. Multi-Version Concurrency Control)

When the data in the table is modified, not only the redolog is generated, but also the undo log is generated.

Undolog is used when rollback is used.

Redo logs are written in sequence and do not need to be read during database operation.

Undo logs must be read and written randomly. It can be stored on SSD to improve performance.

2. INNODB supports row-level locks.

Row-level locks support maximum concurrency.

Row-level locks are implemented at the storage engine layer.

What is a lock?

The lock is used to manage concurrent access to shared resources and implement transaction isolation.

Lock type

Shared lock (read lock)

Read locks are not blocked.

Exclusive lock (write lock)

Other Read and Write locks are blocked. Implement transaction isolation.

Lock Granularity

1. Table-Level Lock

Lock table myinnodb write;

This will block other reads.

Unlock tables;

2. Row-Level Lock

The storage engine is not implemented at the server layer.

Blocking and deadlock

What is blocking?

The lock in one transaction needs to wait for the resource of another transaction lock to be released.

What is a deadlock?

Wait resources of the other party are used by the system automatically.

1. Use resources in the same order.

2. Add related indexes.

3. innodb status check.

Show engine innodb status; it takes at least 30 seconds to separate the two times.

4. Applicable scenarios

Innodb is suitable for most OLTP applications and supports full-text indexing and spatial functions.