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

This article introduces the knowledge of "comparison of multi-document transaction support functions in MySQL and MongoDB". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MongoDB 4.0 has released the GA version, and there are many new features and improvements. In this article, we will focus on the main features, which are undoubtedly support for multi-document ACID transactions. We will compare it with MySQL through experiments around the main functions such as isolation level, repeatable reading, phantom reading, transaction conflict detection and so on.

First, I will briefly introduce the ACID transaction isolation level, and then step into the test topic body.

Atomicity: all operations contained in a transaction either succeed or fail, and there is no concept of half success. A typical example of "Western binary opposition": in the binary logic system, there are only two kinds of logical values, namely, right and wrong, positive and negative, and there are no other states that are both right and wrong or non-positive or negative.

Consistency: a transaction must be in a consistent state before and after execution. Typical example: "Jinlong, lend me 5000 yuan and give it to you next month." No matter how Jinlong transferred money to me, it was transferred several times. After borrowing money, the balance in my bank card increased by 5000 yuan. Jinlong card lost 5000 yuan.

Isolation: the database uses a lock mechanism to achieve transaction isolation. When multiple transactions update the same data in the database at the same time, only the transaction holding the lock is allowed to update the data, and other transactions must wait until the previous transaction releases the lock before other transactions have a chance to update the data. Typical example: "when you go to the hospital to see a doctor, you have to go to the nurse first. If multiple patients come in at the same time, the doctor will get mad and go out in line and can only watch one by one."

Durability: after a transaction is successfully committed, the changes it makes to the database are saved, and even if the database crashes, the data can be restored to the state after the transaction was successfully committed. Typical example: "you go to the ATM to withdraw money, as a result, the ATM malfunctions, you withdraw the money, the balance in the card cannot be reduced."

I. limitations and restrictions

1. Multi-document transactions apply only to replica sets.

Note: if it is a stand-alone machine, you need to switch to copy set mode.

2. Only applicable to WiredTiger storage engine.

3. If your architecture is in fragmented Sharding mode, transactions are not supported. Distributed transaction planning is supported in version 4.2.

4. Transactions only support CRUD operations, but not DDL and DCL operations.

Note: CRUD is the DML of MySQL. It means the same thing and is called differently.

5. Transactions cannot be read or written in config, admin, and local system databases.

6. Transactions cannot be written in system.* (system set).

7. No large transaction can be written, and the write set cannot exceed 16MB (similar to the wsrep_max_ws_size limit of MariaDB Galera Cluster write set), otherwise the client will report an error directly.

Note: if you have large transactions, you should consider splitting these large transactions into several smaller transactions. For example, if you change the status value greater than 2018 to 1, you should consider circulating 10, 000 updates in batches, which is the same as MySQL.

Second, we have * affairs.

Before you start a transaction, you must create a session. Transactions cannot be run outside the session.

Var session1 = db.getMongo () .startSession () var session2 = db.getMongo () .startSession ()

Three new commands have been introduced in Mongo Shell to create, commit, and terminate transactions:

Session.startTransaction ()

Start a transaction in the current session

Session.commitTransaction ()

Persist operational changes in a transaction

Session.abortTransaction ()

Terminating changes made by a transaction operation

1. Create a T1 table in the test library and insert 4 pieces of data.

Demonstration one

Demonstration two

Idle transactions are affected by the transactionLifetimeLimitSeconds parameter, which defaults to 60 seconds.

You can view it with the following command:

Db.adminCommand ({getParameter: 1, transactionLifetimeLimitSeconds: 1})

If you want to change it online, you can set it with the following command:

Db.adminCommand ({setParameter: 1, transactionLifetimeLimitSeconds: 30})

You can also write it in the / etc/mongod.cnf configuration file * to take effect, in the following format:

SetParameter = transactionLifetimeLimitSeconds=30

Note: idle transaction means that when a transaction has not been committed for a long time, the connection cannot be closed, memory will not be released, and the number of DB connections will increase, which will affect performance. The default is 60 seconds, and you can set the threshold according to your situation. Beyond this threshold, the server automatically kills uncommitted idle transactions.

III. Demonstration of transaction isolation

Demonstration 1: transaction conflict detection

Conflicts occur when two (or more) concurrent transactions modify the same document. MongoDB can detect conflicts immediately even when the transaction has not yet been committed.

This is somewhat different from MySQL. MySQL can automatically terminate the rollback time after a transaction conflict is detected by setting the parameter innodb_lock_wait_timeout, which is not provided by MongoDB.

When the index creation is performed, the {background:1}) background creation is not added.

At this point, the new transaction will not be able to acquire the required locks, and the transaction terminates the rollback after waiting for the parameter maxTransactionLockRequestTimeoutMillis, with a default value of 5 milliseconds.

If you want to adjust the amount of time a transaction waits for a lock to be acquired online, you can set it with the following command:

Db.adminCommand ({setParameter: 1, maxTransactionLockRequestTimeoutMillis: 15})

You can also write it in the / etc/mongod.cnf configuration file * to take effect, in the following format:

SetParameter = maxTransactionLockRequestTimeoutMillis=15

Demo 2: repeatable

Repeatable Read (repeatable readable) can avoid dirty reading and unrepeatable reading.

Non-repeatable readings focus on updating modified data, that is, the data results of two queries are inconsistent in the same transaction. The difference between dirty reading and dirty reading is that one transaction reads dirty data uncommitted by another transaction.

Demo 3: fantasy reading

Under the default isolation level Repeatable Read of MySQL, the operation just now will inexplicably see the fifth piece of data in the uncommitted transaction of session II, which is called phantom reading.

Phantom reading is very similar to unrepeatable reading, but phantom reading focuses on adding and deleting, while unrepeatable reading focuses on change. what they have in common is that the data results obtained by two queries in the same transaction are inconsistent.

Therefore, the conclusion drawn from the test results is:

The default isolation level adopted by MongoDB is Snapshot consistent snapshots (especially when readConcern=majority is set, depending on this isolation level when reading a historical version of a row of data.)

Snapshot is between Repeatable Read and Serializable, which avoids dirty, unrepeatable and phantom reading, and does not degrade concurrency performance because of Serializable serialization.

That's all for the comparison of multiple document transaction support capabilities in MySQL and MongoDB. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.