Shulou(Shulou.com)06/01 Report--

Database MYSQL Learning Series 3

MySQL transaction and storage engine

3.1-Database transaction

What is a transaction?

a series of orderly database operations:

O either all succeed

O or all fall back to the state before the operation

O Intermediate state is not visible to other connections

Basic operation of transaction: | basic operation | description | |:-|: | start transaction | start transaction | | commit | commit (all complete) | | rollback | rollback (back to original state) |

-- Open a transaction start transaction;-- or use (non-standard sql) begin;insert into t values (1, 1, 1);-- transaction ends, insert successful commit

Begin;insert into t values (2, 1, 1); insert into t values (3, 1, 1); insert into t values (4, 1, 1);-transaction ends, no data rollback is inserted

Begin

Insert into t values (1,1,1)

Savepoint A1 insert into t values (2, 1, 1);-- rollback to the specified SavePoint rollback to A1 commit

Automatic submission

autocommit can be set at the session level

every DML operation is automatically submitted

DDL is always auto-commit and cannot be rolled back through rollback

Four basic attributes of a transaction (ACID)

atomicity (Atomicity)

consistency (Consistency)

isolation (Isolation)

persistence (Durability)

Atomicity of transaction

All or none of the operations contained in the transaction by are performed.

If an exception occurs in the database or application during , all uncommitted transactions should be rolled back.

Consistency of transactions

Correctness, rationality and completeness of data

data consistency should conform to the rules of application needs:

O the balance cannot be negative.

O the trading partner must have an account number first.

O user account cannot be duplicated.

The result of transaction needs to satisfy the consistency constraint of data.

The persistence of things

The impact of transactions committed by on the database must be permanent

O Database exceptions do not lose transaction updates

O it is generally considered that the data successfully written to disk is persisted successfully.

The realization of transaction persistence

data file persistence

O Random synchronous refresh (slow)

transaction log persistence and instance recovery

O Sequential synchronous refresh (fast)-> transaction log

O random asynchronous refresh-> disk

O transaction log-> disk (instance recovery)

Isolation of transactions

Any data changes in the middle of a database transaction are invisible to other transactions until the commit is completed.

Database isolation phenomenon

Description of isolation phenomenon

Dirty Read transaction B reads data changes that have not been committed by transaction A.

Non-repeatable read (NonRepeatable Read) transaction B reads a record before and after reading that record is modified and committed by transaction A, so transaction B reads a different result

Phantom Read transaction B conditionally matches several rows of records and modifies them. However, because transaction An inserts new qualified records in the process of modification, it is found that there are still eligible records that have not been updated after the completion of B update.

Database isolation level

Isolation level dirty reading can not be repeated phantom reading

Unsubmitted reading may be

Submitted reading is impossible, possible.

Is it possible to read repeatedly?

Serializable reading impossible

Transaction isolation level of MySQL

InnoDB is marked as repeatable by default

InnoDB is not a standard definition of course repetition.

By default, InnoDB avoids phantom reading on a repeatable basis.

MySQL transaction isolation level setting

can be set separately at the next transaction and level of global/session/

recommends using Read committed (same as Oracle)

or the default Repeatable read is recommended

Set tx_isolation =''--sets the isolation level

Transaction and concurrent writing

A record being updated cannot be updated by other transactions at the same time before it is resubmitted or rolled back

Implementation of transaction rollback

rollback segment (rollback segment) and data front image

3.2-Storage engine overview

MySQL program hierarchical architecture

MySQL storage engine

has a variety of options, pluggable and modifiable storage engine

chooses which storage engine to use based on the table

Main storage engine

Storage engine common usage supports transactions

InnoDB is the main, the recommendation is

MyISAM is old and occasionally useful, system table no

MEMORY occasional temporary table is useful, pure memory is not

BLACKHOLE is not used to store data, is it for individual special uses?

TokuDB is novel, and some special scenes have wonderful effects.

Cluster novel, distributed, memory, online do not use is

InnoDB storage engine

index organization table

supports transactions

supports row-level locks

block cache

log persistence

is stable, reliable and has good performance. Try to use InnoDB online.

MyISAM storage engine

stack table

does not support transactions

maintains only the index cache pool, and the table data cache is handed over to the operating system

lock granularity is larger.

data files can be copied directly and may be used occasionally

does not recommend online business data use

MWMORY storage engine

data is stored in full memory and cannot be persisted.

has high performance

does not support transactions

is suitable for occasional use as a temporary watch.

create temporary table tmp (id int) engine = memory

BLACKHOLE storage engine

data is not stored at all

uses MySQL Replicate to act as a log server

acts as an agent master in a MySQL Replicate environment

TokuDB

Fractal Tree Storage structure

supports transactions

row lock

has high compression efficiency.

is suitable for high-volume insert scenarios.

MySQL Cluster

multi-master distributed cluster

data nodes are redundant and highly available

supports transactions

is easy to expand in design.

is oriented to the future and should be used with caution online.

Change the storage engine of the table

Alter table m ENGINE=innodb

3.3-InnoDB storage engine

InnoDB storage engine architecture

InnoDB related disk files

File name quantity location

System tablespace ibdata1 one instance one innodb_data_home_dir

Log file ib_logfile0/1 one instance two (configurable) innodb_log_group_home_dir

Table definition file table name .frm one Schema directory per table

Table data file table name .ibd if innodb_file_per_table = 1, then each table under a Schema directory

InnoDB system tablespace file

What is stored in ibdata1:

O rollback segment

O all InnoDB metadata information

ODouble Write, Insert buffer dump and so on.

automatic extension mechanism

InnoDB parameters related to disk files

Remarks on sample values of parameters

Innodb_data_home_dir / data/mysql/node_1 data home directory

Innodb_log_group_home_dir / data/mysql/node_1 is generally the same as above.

Innodb_data_file_path ibdata1:512M:autoextned, please open autoextned.

Innodb_autoextend_increment 128 MB, not too big or too small

Innodb_file_per_table 1 is strongly recommended to enable

Innodb_log_file_size 100MB performance related

Innodb_log_files_in_group 2 performance dependent

InnoDB data file storage structure

index organization table (cluster table)

sorts by table logical primary key

data node 16K per page

is fast to address according to the primary key

The insertion efficiency of insert with increasing primary key value of is better.

primary key value random insert insertion efficiency is poor

therefore, the InnoDB table must specify a primary key, and it is recommended to use self-incrementing numbers

InnoDB Block Cache Pool

The read and write of data need to be cached

data is read into the cache in full page (16K).

The data in cache is swapped out with LRU policy.

IO has high efficiency and good performance.

InnoDB Buffer Pool related parameters

Remarks on sample values of parameters

Innodb_buffer_pool_size 10g based on total physical memory settings

InnoDB data persistence and transaction Log

Real-time persistence of transaction log

memory change data (dirty data) increment is asynchronously brushed out to disk

instance failure is recovered by replaying log

has good performance, reliability and fast recovery.

Parameters related to InnoDB log persistence

Remarks on sample values of parameters

Innodb_flush_log_at_trx_commit 1 optional: 0: log is written and persisted every 1s. 1: each time commit writes and persists the log. 2: every time the log is submitted to memory, it is persisted every 1s.

InnoDB row-level lock

writes do not block reads

Writes between different lines of do not block each other.

has good concurrency performance.

InnoDB and transaction ACID

Full support for transaction ACID feature

O rollback segment failed rollback

O support primary foreign key constraints

O transaction version + rollback segment = MVCC

O transaction log persistence

can read the isolation level repeatedly by default, which can be adjusted.

3.4-InnoDB transaction lock

What is a computer program lock?

computer program lock

O Control concurrent access to shared resources

O protect data integrity and consistency

Locks in the database

is divided into two broad categories.

Lock latch/mutex

Object transaction thread

Protect the in-memory data structure of database logical content

Scramble for critical resources in the process of duration transaction

, our main concern is transaction locks.

Database transaction concurrency

modifications to the same row of records must be serialized

Transaction locking granularity

row lock

OInnoDB, Oracle

page lock

OSQL Server

table lock

OMyISAM, Memory

lock upgrade

Lock Mode and granularity in InnoDB Storage engine

Four basic locking modes of 

O shared lock (S)-read lock-row lock

O exclusive lock (X)-write lock-row lock

O intention shared lock (IS)-table level

O intention exclusive lock (IX)-table level

intention lock

O the intention lock is always added first, and the intention lock is automatically added and released automatically.

O intention lock prompts the database what kind of lock the session will impose next

O the level of the intention lock is different from that of the Xamp S lock, except for blocking the full table level of the Xmax S lock.

InnoDB lock mode mutual exclusion

Database locking operation

general select statements do not add any locks and will not be blocked by any transaction locks.

O isolation of reads is ensured by MVCC

S lock

O Manual: select from tb_test lock in share mode

O automatic: before insert

X lock

O Manual: select from tb_test lock for update

O automatic: before update,delete

Implementation of InnoDB Row Lock

is implemented by locking index items.

O Row-level locking can only be achieved by conditional walking the index.

O there are duplicate values on the index, which may lock multiple records

O query has multiple indexes to go, and different indexes can be locked.

O whether the index is locked or not actually depends on the MySQL execution plan

self-increasing primary key for conditional updates with the best performance

Without an index, the whole table will be locked.

Gap lock of InnoDB

, what is Phantom Reading?

gap lock eliminates illusion

OInnoDB eliminates illusion only to ensure the master-slave consistency of statement mode replicate.

, be careful, gap lock.

self-increasing primary key for conditional updates with the best performance

Deadlock



What is a deadlock?



In the two transactions of oA and B, A updates T1 first, while B updates T2 Magi A, then T2 Magi B, then T1, then deadlock occurs.



Automatic resolution of deadlock database



O Database selects transactions with less rollback cost in conflicting transactions



Deadlock prevention



O single table deadlocks can be sorted according to update conditions in batch updates

O avoid concurrency of cross-table transactions that may conflict

O minimize the length of the transaction

Business logic locking



Pessimistic locks in business processes



O any concurrent modification may result in the final error of our business logic, locking at the beginning of the transaction process and finally releasing it.



How to shorten the lock time



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.