01_Basic knowledge of transaction

Basic knowledge of affairs

1. ACID of the transaction

  • Atomic:
    A group of SQL is executed together. Either all of them are executed successfully, or they are not executed. There is no state of partial successful execution. (The focus is on the state of the data)
  • Consistency:
    During the execution of a transaction, the data of the intermediate state is invisible to the outside, only the initial and final state is visible to the outside. (The focus is on the visibility of the data)
  • Isolation: Isolation
    Multiple transactions cannot interfere with each other during operation. Prevent this situation: Transaction A is operating some data, and transaction B is also operating this data at this time, and it also causes transaction A to report an error.
  • Durability:
    As long as the transaction is executed successfully, its impact on the data is permanent. Don't make it happen for a while, the data just updated is automatically restored again.

2. The isolation level of the transaction

2.1 Read, uncommitted (READ UNCOMMITTED)

Insert picture description here


Assume that service B is updating the data with id=1, changes the name to Zhang San, and has not yet committed the transaction. Under the read uncommitted transaction isolation level, service A initiates a request. In the created transaction A, you can see the modified data in transaction B, that is, the name is equal to Zhang San. very dangerous.

2.2 Read, submitted (READ COMMITTED)

Insert picture description here
  1. Service B modified the data with id=1, changed the value of name to Zhang San, and did not commit the transaction.
  2. Service A queries the data with id=1 and finds that name is equal to Li Si.
  3. Service B submitted the transaction.
  4. In the same transaction , service A queries the data with id=1 again, and finds that name is equal to Zhang San.

2.3 REPEATABLE READ

Insert picture description here
  1. Service B is modifying the data with id=1, changing the value of name to Zhang San, and has not committed the transaction.
  2. Service A requests Mysql to query the data with id=1 in transaction A-1, and the name is "Li Si" at this time.
  3. Service B commits transaction B,
  4. Service A queries the data with id=1 again in transaction A-1, and the value of name is still "Li Si" at this time.
  5. If and only if service A opens a new transaction and queries the data with id=1 again, the value of name can be found to be "Zhang San".

The data after the commit operation can only be visible to the new Session. No matter how many query statements you execute in the old Session (such as transaction A-1), it is impossible to see the data after transaction B commit. Mysql's default transaction isolation level is repeatability.

We can easily understand the above conclusions by using the concept of MVCC.

  1. Why is the data after a transaction committed can only be visible to the new session, but not to the old session?
    Answer: Because the SessionId of this transaction is smaller than the new SessionId and larger than the old SessionId, Mysql's MVCC mechanism tells us that the data that the transaction can see must be satisfied, and the creation transaction id of this data is less than or equal to the current transaction.

2.4 Phantom reading

  1. This data is stored in the database with id=1 and name=Zhang San.
  2. Service A creates a transaction, queries the database, and finds that there is only one piece of data with id equal to 1.
  3. Service B adds two new data to the database and submits the transaction.
  4. In the transaction that has not yet been closed, Service A queried the database again and found that 3 pieces of data were found. This is different from the previous query results. From my own point of view, it seems that there are 2 more pieces of data "out of thin air". read.

Repeatable reading can alleviate part of the phantom reading problem. It can be done in the same Session. No matter how many times you check, as long as you don’t add, delete, or modify, the result of the check will remain the same, regardless of whether other sessions are added or not Delete the data. But this has a major premise, that is, you can only do query operations all the time. Let’s say, I used repeatability, I first made a query in Session1 and got the result set A, then added two pieces of data in Session2 and submitted Session2, and then updated all the data in Session1 (such as (Speaking of unconditional update) , submit Session1, at this time, you will be surprised to find that, including the two new data in Session2, all the data are affected by the update operation of Session1.

In other words, although in Session1, we cannot see the data submitted by Session2, but we can update them. Why?

This is because Select takes a snapshot read. When the Select operation is performed, Mysql will generate a snapshot of the result of this execution. The subsequent Selects are actually querying this snapshot. Of course, the newly generated data in Session2 There is no way to see it. Once a row of data is added, deleted, and modified, this matter has essentially changed, because Mysql will generate a new snapshot point for this row of data, and you can obviously see the latest data of this row of data by executing Select again, but it is worth noting The thing is, the data of other rows still take the snapshot generated before.

Therefore, to completely solve the phantom reading, an isolation mechanism that is stricter than repeatability is required, that is, serialization.

2.5 Serialization (SERIALIZABLE)

Insert picture description here


During the execution of transaction A of service A, no other clients can start the transaction. In the above figure, service A starts transaction A, first queries the data in the table, then performs CRUD operations, and finally commits the transaction. In this process, other clients cannot create transactions, and can only wait until transaction A is submitted. , In order to start and execute your own affairs. After transaction A is completed, transaction B can be executed. This is the serialized execution of the transaction.

2.6 MVCC mechanism

The above mentioned 4 kinds of transaction isolation mechanisms, the default is to use repeatable read (REPETABLE READ), then how is this achieved? Mysql is implemented through the MVCC mechanism. MVCC (multi-version concurrency control) means multi-version concurrency control.

Concept 1: When any service is connected to Mysql to create a transaction, Mysql will assign a transaction id to it, and the transaction id is globally unique and self-increasing.
Concept 2: In addition to its own fields, each row of data also maintains two hidden fields, which are the transaction id of creating this row of data and the transaction id of deleting this row of data.
Concept 3: Each transaction can only see the following data: 1. The current transaction id >= the transaction id that created the data 2. The current transaction id <the transaction id that deleted the data

For example, there is a student table that contains three fields, namely id, name and age.

① Transaction (id=100) added data with id=1 and data with id=2

idnameageThe id of the transaction that created the dataThe id of the transaction that deleted the data
1Zhang San27100empty
2Li Si26100empty

When the transaction (id=101) executes select * from student where id = 1, you can see id=1, name=Zhang San, age=27, this is because 101 >= 100

② The transaction (id=102) deletes the data with id=1.

idnameageThe id of the transaction that created the dataThe id of the transaction that deleted the data
1Zhang San27100102
2Li Si26100empty

The transaction (id=101) can still see the data with id=1 because 101 <102

③ The transaction (id=102) modified the data of id=2, changed the name to "Wang Wu", and the age to 28

idnameageThe id of the transaction that created the dataThe id of the transaction that deleted the data
1Zhang San27100102
2Li Si26100empty
2Wang Wu28102empty

When transaction (id=101) select * from student where id = 2, only the old data can be seen, that is, name=Li Si and age=26. This is because 101 <102.

Note: When there are multiple pieces of data with the same id in the data table (not really multiple pieces of data, after all, the id cannot be repeated, here is just because in the transaction), what we query must meet the MVCC conditions, and " The data with the largest transaction id that created the data. For example, suppose a transaction (id=102) performs a full table query, and the table has three pieces of data created SessionId=100, 101, and 103, and their data id is equal to 2, then the transaction (id=102) queried The data with id=2 should be SessionId=101.

2.7 Classic small case

Suppose there are three fields id, name, age in the data table

idnameageThe id of the transaction that created the dataThe id of the transaction that deleted the data
1Zhang San2799no

Transaction A (SessionId=100) and transaction B (SessionId=101) start the transaction in turn, the SessionId of transaction A <SessionId of transaction B.

Transaction A added two pieces of data, modified the data with id=1, changed age to 35, and submitted the transaction.

idnameageThe id of the transaction that created the dataThe id of the transaction that deleted the data
1Zhang San2799empty
1Zhang San35100empty
2Li Si26100empty
3Wang Wu28100empty

Transaction B is the first full table query. At this time, only id=1, name=Zhang San, age=27 can be found. After all, it is a snapshot.

Next, transaction B updated the data with id=1 and changed the name to "Zhang San (after modification)".

idnameageThe id of the transaction that created the dataThe id of the transaction that deleted the data
1Zhang San2799empty
1Zhang San35100empty
1Zhang San (modified)35101empty
2Li Si26100empty
3Wang Wu28100empty

Now, the entire table query of transaction B can still only find the data with id=1, but name=Zhang San (modified), and age=35.
Through the update operation, Mysql recreates the snapshot point for the data with id=1, so you can see the latest piece of data with id=1 in the current database. But for the data of other IDs, the old snapshot is still read, so no data can be found.

Three. Spring's transaction support and propagation features

Spring supports three methods of declarative, programmatic, and annotation to manipulate transactions.

Declarative transaction is to make an XML file, add data source, add transaction manager, add transaction template, and then use transaction template (TransactionTemplate) in the code, where transaction is needed, use TransactionTemplate.

transactionTemplate.execute(new TransactionCallbackWithoutResult() {
    @Override
    protected void doInTransactionWithoutResult(TransactionStatus transactionStatus) {
        String sql = ...
        jdbcTemplate.update(...)
    }
})

Programmatic transaction is to make an XML, configure DataSourceTransactionManager in it, then make a bunch of AOP, configure pointcuts, such as which method of which class should the transaction be used, the advantage is that it is not intrusive to the code.

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
</bean>

<tx:advice id="txAdvice" transaction-manager="txManager">
        <!-- 事务语义... -->
        <tx:attributes>
            <!-- 所有用'get'开头的方法都是只读的 -->
            <tx:method name="get*" read-only="true"/>
            <!-- 其他的方法使用默认的事务配置(看下面) -->
            <tx:method name="*"/>
        </tx:attributes>
</tx:advice>

 <aop:config>
        <aop:pointcut id="fooServiceOperation" expression="execution(* x.y.service.FooService.*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="fooServiceOperation"/>
</aop:config>

But no one writes such a complicated configuration file now, and annotations are usually used. The annotation is to use @Transational directly to specify the propagation behavior of the transaction by assigning a value to the propagation.

There are 7 kinds of communication behaviors in the transaction:

  • PROPAGATION_REQUIRED (default)
    assumes that ServiceA.method() calls ServiceB.method(). If ServiceA.method() opens the transaction and ServiceB.method() also opens the transaction, then ServiceB.method() will not open the transaction separately. Instead, put yourself in the transaction of ServiceA.method(). Any error of ServiceA.method() and ServiceB.method() may cause the entire transaction to roll back.
  • PROPAGATION_SUPPORTS
    assumes that ServiceA.method() calls ServiceB.method(). If ServiceA.method() opens the transaction, ServiceB.method() will add itself to the former transaction. If ServiceA.method() does not open a transaction, ServiceB.method() will not open a transaction either.
  • PROPAGATION_MANDATORY
    must be called by a method that has opened a transaction, otherwise an error will be reported.
  • PROPAGATION_REQUIRED_NEW
    forces itself to open a transaction. Assuming that ServiceA.method() calls ServiceB.method(), after calling ServiceB.method(), ServiceA will be stuck. If and only after ServiceB.method() is executed, ServiceA then performs its own operation. Therefore, if we add try/catch to the code that calls ServiceB for Service, then even if ServiceB.method() reports an error, it will only roll back the operations performed by ServiceB.method(), and it has no relationship with ServiceA. .
  • PROPOGATION_NOT_STOPPED
    ServiceB.method() does not support transactions. When ServiceA.method() calls ServiceB.method(), the request will be suspended. After ServiceB.method() runs in a non-transactional way, ServiceA will execute again. The advantage of this approach is that even if the code of ServiceB reports an error, causing ServiceA transaction rollback, the code executed by ServiceB will not be rolled back.
  • PROPAGATION_NEVER
    cannot be called by a method that has opened a transaction, such as ServiceA.method() has opened a transaction, then it will report an error when calling ServiceB.method().
  • PROPAGATION_NESTED
    opens a nested transaction. ServiceB opens a sub-transaction. Once ServiceB reports an error, it will roll back to ServiceA calling ServiceB, which is the savePoint that opened the sub-transaction.