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

This article mainly explains "detailed explanation of Mysql transaction and locking mechanism". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "Mysql transaction and locking mechanism detailed explanation".

The characteristic ACID of a transaction is atomicity, consistency, isolation, and persistence.

Atomicity ensures that a transaction is a smallest unit and cannot be separated internally.

Consistency ensures that each thread of operation in a transaction cannot be committed separately, if it succeeds, it commits together, and if it is not successful, the transaction is rolled back.

Isolation ensures that the data views seen between different transactions are independent and isolated from each other (isolation level can be set)

Persistence ensures that the data will be persisted after the transaction is committed

The isolation level of transactions defined by the sql specification:

1.READ UNCOMMITTED (read uncommitted)

All transactions can see the execution results of uncommitted transactions, and this isolation level is rarely used in practical applications to read uncommitted data, also known as "dirty read".

2.READ COMMITTED (read submission)

Most of the default isolation levels are this level, but not the default. At the beginning of a transaction, you can only see the changes made by the submitted firm. Any changes made by a transaction from the beginning to the commit are not visible unless committed. This isolation level is also known as non-repeatable reading.

3.REPEATABLE READ (repeatable)

This isolation level is designed to solve the problem caused by the repeatable read isolation level, that is, when multiple instances of a transaction read data concurrently, they will see different results. This isolation level does not see the results of other transactions committed, that is, I cannot see the transaction even if it is committed. This level is also called "phantom reading".

4.SERIALIZABLE (serializable)

Serializability is the highest isolation level, which solves the problem of phantom reading by forcing the ordering of transactions to make them unreadable. This isolation level adds a shared lock on each read data row, and the use of this isolation level results in a large number of timeouts, which are not commonly used in actual development.

Mysql locking mechanism:

According to the type, it can be divided into shared lock (SHARED LOCK) and exclusive lock (EXCLUSIVE LOCK) or read lock (READ LOCK) and write lock (WRITE LOCK).

According to the granularity, it is divided into table lock and row lock. The table lock is implemented by the database server and the row lock is implemented by the storage engine.

Mysql provides three transactional storage engines, InnDB, NDB Cluster, and Falcon.

Locks can be acquired during any process that a transaction executes, but they are released only when the transaction commits or rolls back. These are implicit locks or explicit locks, which are supported by InnoDB, for example:

SELECT.... LOCK IN SHARE MODE (plus shared lock)

SELECT .for UPDATE (plus exclusive lock)

Thank you for your reading. the above is the content of "detailed explanation of Mysql transaction and locking mechanism". After the study of this article, I believe you have a deeper understanding of Mysql transaction and locking mechanism, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.