doubt

Today we’ll look at the relationship between multithreading, database transactions, and database connections

As mentioned above, the database transaction related knowledge, you can see this article for details. The interviewer asked me: “What about MySql transaction,MVCC?” However, what is the relationship between multi-transactions and multi-threading? Is it one thread for one transaction? Multi-threading is multiple transactions? Each project has a pool of database connections. What is the relationship between database connections and transactions and threads? Today, LET’s have a good talk about this topic, to clarify the relationship, more convenient CRUD in the future

Under the single thread

Let’s start with a piece of code

   @Transactional
    @Override
    public void updateTest(Test updateVO) {
        Test test = testMapper.selectByPrimaryKey(updateVO.getId());
        System.out.println(The current thread is:+Thread.currentThread().getName()+"The value found out is"+JSON.toJSON(test).toString());
        testMapper.updateByPrimaryKey(updateVO);
        testMapper.deleteByPrimaryKey(2);
    }
Copy the code

What does this code say?

  • Start the transaction
  • There are three interactions with the database

So the question is, are these three operations to the database, are they the same connection to the database? Or different connections?

  • To start the transaction, make a connection: the console Fetched Fetched SqlSession
  • If no transaction is enabled, create three connections: The console prints Creating a new SqlSession

Why is that? Because of the things that we always talk about

Let’s say we have thread A executing this method, and we find that this method opens A transaction that is based on A database Connection. This transaction has three DAO methods that operate on the database. If we want to use one transaction to manage three DAO methods that operate on the database, Then all three DAO methods must be based on the same Connection, and thread A will get the database Connection ConnectionA from the database Connection pool

Let’s take a look at transactions. Let’s say our program has two interfaces, each with transactions enabled, and each interface has three DAO methods

  • Conclusion: A single Thread holds a database Connection that can have multiple transactions

Under the multithreading

Suppose there are two threads entering the interface at the same time, what will the relationship look like when the threads operate on the database?

As you can see from the diagram, the two threads have different database connections, and each has its own transaction. In this case, it is the multi-transaction mentioned above

Transaction characteristics

As we can see from the figure, the start and commit times of the two threads’ respective transactions are sometimes inconsistent. There are commit first and commit later. Do they affect each other? At this point, the transaction property I (isolation) in ACID, as we call it, becomes important

  • If we set it to repeatable read, then transaction A’s operations on the database will be transparent to transaction B’s operations and will not be perceived by transaction B’s operations
  • If it is set to read committed, then transaction A’s operation on the database will not be transparent to transaction B, and transaction B will read the data changed by transaction A and perform the operation

The interviewer at JD.com asked me, “What about MySql,MVCC?”

  • It is highly recommended that these two articles be eaten together for better results so that they can be strung together as a whole

conclusion

  • At the same time, different threads will have different database connections and each will start its own transaction. The specific relationship between transactions is determined by the ACID isolation setting of the transaction property

  • If different threads access the same database connection, will generate transaction conflict, A thread is created A transaction, B threads created B transaction, likely A transaction has not been submitted and B transaction is submitted, so when the multithreaded execution dao methods related database operations will take effect, and the other method is not A transaction execution, lead to problems, Transaction isolation is based on different connections, so this is inevitable

  • Why can three DAO methods get the same Connection after a transaction is started? Spring uses ThreadLocal to ensure that the same thread will get the same database connection each time it operates the database multiple times during its lifetime (many DAOs). Why make sure it is the same database connection? Because database transactions are based on database connections, if the thread performs three DAO operations and each connection is different, there is no guarantee that these three operations will be managed by the same transaction

Wechat search: Java xiaojie to refueling, there are more wonderful

Good recommendation

  • Various traversal methods of binary tree
  • Illustrated, HTTP detailed explanation
  • How is the network connected from the four-layer model
  • How to use thread pools and source profiling gracefully
  • Interviewer: What are spring’s seven transaction propagation behaviors?