MySQL's business

Describe the concept of a transaction:

Why do you introduce transactions?

When we sometimes need to execute a lot of statements, for some reason, only the first statement is executed, the result is very diverse.

The nature of the transaction isACID

atomicity (atomicity):

A transaction is a logical unit of work for a database, and the operations contained in the transaction are either executed or not executed.

Consistency (consistent):

The result of a transaction execution must be a migration from one consistent state to another. (a phrase found on the Internet: consistency is the data state of the database conforms to the business logic and rules described in the database ). A Account Increase B The account will be reduced . - This is the consistency, to ensure that his operation is two updates at the same time to execute the success, or failed together! If an execution fails, then there will be inconsistencies, without any more or less than $, the database state is in an inconsistent state.

Isolation (isolation):

The execution of one transaction cannot be disturbed by other transactions, and the operations inside a transaction and the data used are isolated from other transactions that are concurrently executed, that is, the transactions performed concurrently cannot interfere with each other.

Persistence (durable):

Once a transaction is committed, changes to the data in the database are permanent, and any remaining failures can remain

Problems with concurrent transactions (disruptive isolation):

Update lost:

Each transaction does not know the existence of other transactions, and when transaction A commits the transaction after the original data has been modified, B is also modifying the original data because A is more than b committed, so a committed transaction overwrites the original data, and B commits a transaction that overrides a committed transaction.

Dirty read:

After a transaction modifies a data, and then another transaction reads to that value, the first transaction then revokes the previously modified data for some reason, and the value read by the second transaction is invalid, which is dirty data.

Non-repeatable READ:

After a transaction reads the data, it reads the data again, discovering the inconsistency between the second read and the first read.

Phantom read:

A transaction re-reads the previously read data according to the same retrieval criteria, and finds that other transactions have inserted some new data that satisfies its query criteria. Here is easy and non-repeatable reading confusion, as long as remember that non-repeatable reading refers to the modification, focusing on inconsistencies, while the Magic reading focuses on new data, more than before.

Transaction ISOLATION Level:

There are two ways that a database can achieve isolation:

Pessimistic Lock:

Locks the data before it is read, preventing other transactions from modifying the data.

Traditional relational database in the use of a lot of this locking mechanism, such as row locks, table locks, read locks, write locks, etc., are in operation before the lock.

Optimistic Lock:

No lock is added until the data is read. However, a consistent data snapshot file is generated by a mechanism to generate a data request point-in-time, using this snapshot to improve a certain level of consistent reads.

From the user's point of view, it seems that the database provides multiple versions of the same data, so this technique is also called MVCC(Multi-version concurrency control of data). optimistic locking is suitable for multi-read application types, which can improve throughput

InnoDBinMVCCimplementation of:

This version of the data is implemented by adding a version number field to the database table, and when reading the data, the version number isread altogether, and when the data is updated, this version number is added to 1. The version number of the submitted data is compared to the version number in the original data table, which is greater than can be updated, otherwise it is considered to be outdated data.

Four Isolation level comparisons:

isolation level	Dirty Read (dirty read "	non-repeatable read (nonrepeatable read	Phantom Read ( Phantom read
uncommitted read (read  uncommitted	may	possible	possible
Read Committed (read committed	impossible	possible	possible
repeatable read ( Repeatable read	impossible	impossible	possible
Serializable (Serializable )	No way	No way	No way

·  UNCOMMITTED read (READ UNCOMMITTED): Allows dirty reads, which may read data that has not been committed in other sessions for transaction modification

·  Read Committed: Read Only the data that has been committed. Most databases, such as Oracle, are at this level by default ( not repeating )

·  Repeatable Read (repeated read): Repeatable read. Queries within the same transaction are all consistentat the beginning of the transaction,InnoDB the default level. In the SQL Standard, this isolation level eliminates non-repeatable reads, but there are also phantom reads

·  serial read (Serializable): Fully serialized read, each read requires a table-level shared lock, read and write each other will block

Distributed transactions:

Mysql supports distributed transactions from 5.0.3 , and the current distribution supports only the InnoDB storage engine.

Let's take a general look at the principles of distributed Transactions (a bit like the master-slave Architecture of Hadoop):

In Mysql , applications that use distributed transactions involve one or more resource managers and a transaction manager.

Resource Manager (RM): Used to provide access to transactional resources. A database server is a resource manager that must be able to commit or roll back transactions managed by the RM.

Transaction manager (TM): Used to reconcile a transaction as part of a distributed transaction,the TM communicates with the RMs that manages each transaction .

There are two phases of performing distributed transactions:

1. TM informs all branches to prepare for submission, and branches indicate whether they can do so.

2. The TM informs RMs whether to commit or rollback. If in the preliminary phase all branches indicate that a commit can be committed, all branches are told to commit. If there are any branch instructions that cannot be committed at the time of preparation, all branches will be told to roll back.

Reference:

A minute teaches you to know the difference between optimistic and pessimistic locks

The relationship between the transaction isolation level and the lock in InnoDBMySQL database development, optimization and management maintenance. Second Edition

Copyright NOTICE: This article is the original blogger articles, reproduced please indicate the source.

MySQL's business