In depth analysis of Mysql transactions and Spring transactions

Posted by cringe on Mon, 10 Jan 2022 20:53:40 +0100

In depth analysis of Mysql transactions and Spring transactions

I saw the last blog shared a year ago. I didn't share it for a long time. I feel a little less. So now I set a goal to share at least two technology blogs every month from August. I've been precipitated for too long. I forget why I had to look at the source code and why I had to deeply learn technology. Back to the point, this article shares some information about how Mysql solves the problem of multi transaction concurrency, how Spring source code controls transactions, and some scenarios of transaction failure.

Share content

  • Mysql transaction isolation mechanism
  • Locking mechanism
  • MVCC multi version concurrency control isolation mechanism
  • Spring transaction application and source code analysis
  • Transaction invalidation problem

1, Mysql transaction

In order to solve the problem of multi transaction concurrency, the database designs transaction isolation mechanism, locking mechanism and MVCC multi version concurrency control isolation mechanism, and uses a complete set of mechanisms to solve the problem of multi transaction concurrency.

1.1 transaction properties

Transaction is a logical unit of a series of operations in a database system. All operations either succeed or fail.

Transaction is one of the important characteristics to distinguish file storage system from Nosql database. Its significance is to ensure that crud operations can be performed correctly even in the case of concurrency. What is right? At this time, four features that transactions need to guarantee, namely ACID, are proposed:

  • Atomicity: a transaction is an atomic operation unit whose modifications to data are either executed or not executed.
  • Consistency: data must be consistent at the beginning and completion of a transaction. This means that all relevant data rules must be applied to the modification of transactions to maintain data integrity.
  • Isolation: the database system provides a certain isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations. This means that the intermediate state in the transaction processing process is invisible to the outside, and vice versa.
  • Durable: after the transaction is completed, its modification to the data is permanent and can be maintained even in case of system failure.

1.2 isolation level of transactions

In the case of high concurrency, it is very difficult to fully ensure its ACID characteristics, unless all transactions are serialized, but the negative impact will be a great discount in performance. Many times, some of our businesses have different requirements for transactions, so four isolation levels are designed in the database for users to choose based on business.

Database default isolation level:

The default level in Oracle is Read committed

The default level in Mysql is Repeatable read

#View the default isolation level of mysql
SELECT @@tx_isolation

#Set to read uncommitted
set tx_isolation='read-uncommitted';  

#Set to read committed
set tx_isolation='read-committed';  

#Set to repeatable
set tx_isolation='REPEATABLE-READ';   

#Set to serialize
set tx_isolation='SERIALIZABLE';

Problems caused by concurrent transaction processing

  • Lost update or dirty write

When two or more transactions select the same row and then update the row based on the initially selected value, the problem of missing updates occurs because each transaction is unaware of the existence of other transactions - the last update overwrites the updates made by other transactions.

  • Dirty Reads

A transaction is modifying a record. Before the transaction is completed and committed, the data of the record is in an inconsistent state; At this time, another transaction also reads the same record. If it is not controlled, the second transaction reads these "dirty" data and makes further processing accordingly, which will produce uncommitted data dependencies. This phenomenon is vividly called "dirty reading".

Brief summary: transaction A reads the modified but uncommitted data of transaction B and operates on this data. At this time, if transaction B rolls back, the data read by A is invalid and does not meet the consistency requirements.

  • Non repeatable reads

At a certain time after reading some data, a transaction reads the previously read data again, but finds that the read data has changed or some records have been deleted! This phenomenon is called "unrepeatable reading".

Brief summary: the results of the same query statement in transaction A read out at different times are inconsistent and do not comply with isolation

  • Phantom Reads

A transaction re reads the previously retrieved data according to the same query criteria, but finds that other transactions insert new data that meets its query criteria. This phenomenon is called "phantom reading".

Brief summary: transaction A reads the new data submitted by transaction B, which does not comply with isolation

The actual demonstration of the specific isolation level is omitted

1.3 locking mechanism

A lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently.

In the database, in addition to the contention of traditional computing resources (such as CPU, RAM, I/O, etc.), data is also a resource for users to share. How to ensure the consistency and effectiveness of data concurrent access is a problem that all databases must solve. Lock conflict is also an important factor affecting the performance of database concurrent access.

Lock classification

  • In terms of performance, it is divided into optimistic lock (implemented by version comparison) and pessimistic lock
  • The types of database operations are divided into read locks and write locks (both belong to pessimistic locks)

Read lock (shared lock, S lock): for the same data, multiple read operations can be performed simultaneously without affecting each other

Write lock (exclusive lock, X lock): it will block other write locks and read locks before the current write operation is completed

  • From the granularity of data operation, it can be divided into table lock and row lock

Watch lock

Lock the whole table for each operation. Low cost and fast locking; No deadlock; The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest; It is generally used in the scenario of whole table data migration.

1.4 wal principle

InnoDB's ARIES three principles Write Ahead Logging (WAL):

  • After the log is successfully written, the transaction will not be lost. Subsequently, the checkpoint mechanism is used to ensure the consistency between the disk physical file and the redo log;
  • Use redo log to record the changed data, that is, record the changed value of transaction data in redo;
  • undo log is used to record the data before the change, that is, the value before the change of transaction data is recorded in undo, which is used for rollback and multi version reading of other transactions.

1.5 concurrent transaction control

  • Single version control - lock

    Locks are used exclusively to ensure that transactions are isolated from each other when there is only one version.

    In MySQL transactions, the implementation of locks is related to the isolation level. At the RR (Repeatable Read) isolation level, in order to solve the problem of unreal reading, MySQL uses Gap locks to prevent data writing at the expense of parallelism. This kind of lock often causes deadlock because of insufficient parallelism and many conflicts. Now the popular Row mode can avoid many conflicts and even deadlock problems. Therefore, it is recommended to use the isolation level of Row + RC (Read Committed) mode by default, which can greatly improve the read-write parallelism of the database.

  • MVCC multi version concurrency control isolation mechanism

    How can Mysql ensure high transaction isolation at the repeatable read isolation level? The same sql query statement is executed multiple times in a transaction, and the query results are the same. Even if other transactions modify the data, the query results of the current transaction sql statement will not be affected.

    This isolation is guaranteed by mvcc (multi version concurrency control) mechanism. By default, the read and write operations of a row of data will not be locked and mutually exclusive to ensure isolation, avoiding frequent locking and mutually exclusive. At the serialization isolation level, in order to ensure high isolation, all operations are locked and mutually exclusive.

    Mysql implements MVCC mechanism at both read committed and repeatable read isolation levels.

The implementation of MVCC mechanism is through the read view mechanism and undo version chain comparison mechanism, so that different transactions will read different versions of the same data on the version chain according to the data version chain comparison rules.

undo log version chain

For a table using the InnoDB storage engine, its clustered index record contains two necessary hidden columns (row_id is not necessary. When the table we create has a primary key or a non NULL unique key, the row_id column will not be included):

  • trx_id: each time a record is changed, the corresponding transaction id will be assigned to trx_id hides the column.
  • roll_pointer: every time a record is changed, a pointer will be stored in this hidden column, and the information before the modification of the record can be found through this pointer.

Detailed explanation of read view mechanism

For transactions using the READ UNCOMMITTED isolation level, it is good to directly read the latest version of the record. For transactions using the SERIALIZABLE isolation level, use the locking method to access the record. For transactions using the READ COMMITTED and REPEATABLE READ isolation levels, we need to use the version chain mentioned above. The core problem is to judge which version in the version chain is visible to the current transaction.

ReadView mainly contains four important contents:

  1. m_ids: indicates the transaction id list of active read-write transactions in the current system when generating ReadView.
  2. min_trx_id: represents the smallest transaction id among the active read / write transactions in the current system when generating ReadView, that is, M_ Minimum value in IDS.
  3. max_trx_id: indicates the id value that should be assigned to the next transaction in the system when generating ReadView.
  4. creator_trx_id: indicates the transaction id of the transaction that generated the ReadView.

Attention max_trx_id is not M_ The maximum value in IDS, and the transaction id is incrementally allocated. For example, there are three transactions with id 1, 2 and 3, and then the transaction with id 3 is committed. When a new read transaction generates a ReadView, m_ids includes 1 and 2, min_ trx_ The value of id is 1, max_ trx_ The value of id is 4.

With this ReadView, when accessing a record, you only need to judge whether a version of the record is visible according to the following steps:

  • If the accessed version of TRX_ The ID attribute value is the same as the creator in ReadView_ trx_ The same ID value means that the current transaction is accessing its own modified records, so the version can be accessed by the current transaction.

  • If the accessed version of TRX_ The ID property value is less than min in ReadView_ trx_id value, indicating that the transaction generating this version has been committed before the current transaction generates ReadView, so this version can be accessed by the current transaction.

  • If the accessed version of TRX_ The ID attribute value is greater than max in ReadView_ trx_id value, indicating that the transaction generating this version is started only after the current transaction generates ReadView, so this version cannot be accessed by the current transaction.

  • If the accessed version of TRX_ The ID attribute value is in the min of ReadView_ trx_id and Max_ trx_ Between IDS, you need to judge TRX_ Is the ID attribute value in M_ In the IDS list, if the transaction that generates this version when creating ReadView is still active, the version cannot be accessed; If not, the transaction that generated this version when creating ReadView has been committed and the version can be accessed.

Implementation of READ COMMITTED

A ReadView is generated before reading data each time

REPEATABLE READ implementation

Generate a ReadView when reading data for the first time

Caption: the details of undo log version chain and read view mechanism need to be discussed

2, Spring transaction

2.1 spring transaction related API s

After the breakpoint and spring source code, draw the flow chart of spring transaction execution on processon

https://www.processon.com/view/5eea03981e085326373032a7

Spring transactions are encapsulated and extended on the basis of database transactions. Their main features are as follows:

  • It supports the isolation level of original database transactions and adds the concept of transaction propagation
  • It provides the function of merging or isolating multiple transactions
  • Declarative transactions are provided to separate business code from transactions and make transactions easier to use (AOP)

For a general description, Spring provides transaction related interfaces:

TransactionDefinition

Transaction definition: the isolation level of a transaction and the propagation behavior of a transaction

public interface TransactionDefinition {

	int PROPAGATION_REQUIRED = 0;

	int PROPAGATION_SUPPORTS = 1;

	int PROPAGATION_MANDATORY = 2;

	int PROPAGATION_REQUIRES_NEW = 3;

	int PROPAGATION_NOT_SUPPORTED = 4;

	int PROPAGATION_NEVER = 5;

	int PROPAGATION_NESTED = 6;

	int ISOLATION_DEFAULT = -1;

	int ISOLATION_READ_UNCOMMITTED = Connection.TRANSACTION_READ_UNCOMMITTED;

	int ISOLATION_READ_COMMITTED = Connection.TRANSACTION_READ_COMMITTED;

	int ISOLATION_REPEATABLE_READ = Connection.TRANSACTION_REPEATABLE_READ;

	int ISOLATION_SERIALIZABLE = Connection.TRANSACTION_SERIALIZABLE;

	int TIMEOUT_DEFAULT = -1;

	int getPropagationBehavior();

	int getIsolationLevel();

	int getTimeout();

	boolean isReadOnly();

	String getName();

}

TransactionAttribute

Transaction attribute, which implements the extension of rollback rules (exception handling)

public interface TransactionAttribute extends TransactionDefinition {

	String getQualifier();

	boolean rollbackOn(Throwable ex);

}

PlatformTransactionManager

Platform transaction manager

public interface PlatformTransactionManager {

	TransactionStatus getTransaction(@Nullable TransactionDefinition definition)
			throws TransactionException;

	void commit(TransactionStatus status) throws TransactionException;

	void rollback(TransactionStatus status) throws TransactionException;

}

TransactionStatus

Transaction runtime status

public interface TransactionStatus extends SavepointManager, Flushable {

	boolean isNewTransaction();

	boolean hasSavepoint();

	void setRollbackOnly();

	boolean isRollbackOnly();

	void flush();

	boolean isCompleted();

}

TransactionInterceptor

The transaction interceptor implements the MethodInterceptor

public class TransactionInterceptor extends TransactionAspectSupport implements MethodInterceptor, Serializable {

	/**
	 * Create a new TransactionInterceptor
	 * The transaction manager and transaction properties need to be set.
	 */
	public TransactionInterceptor() {
	}

	public TransactionInterceptor(PlatformTransactionManager ptm, Properties attributes) {
		setTransactionManager(ptm);
		setTransactionAttributes(attributes);
	}

	public TransactionInterceptor(PlatformTransactionManager ptm, TransactionAttributeSource tas) {
		setTransactionManager(ptm);
		setTransactionAttributeSource(tas);
	}


	@Override
	@Nullable
	public Object invoke(MethodInvocation invocation) throws Throwable {
		Class<?> targetClass = (invocation.getThis() != null ? AopUtils.getTargetClass(invocation.getThis()) : null);

		return invokeWithinTransaction(invocation.getMethod(), targetClass, invocation::proceed);
	}


	private void writeObject(ObjectOutputStream oos) throws IOException {

		oos.defaultWriteObject();

		oos.writeObject(getTransactionManagerBeanName());
		oos.writeObject(getTransactionManager());
		oos.writeObject(getTransactionAttributeSource());
		oos.writeObject(getBeanFactory());
	}

	private void readObject(ObjectInputStream ois) throws IOException, ClassNotFoundException {

		ois.defaultReadObject();

		setTransactionManagerBeanName((String) ois.readObject());
		setTransactionManager((PlatformTransactionManager) ois.readObject());
		setTransactionAttributeSource((TransactionAttributeSource) ois.readObject());
		setBeanFactory((BeanFactory) ois.readObject());
	}

}

Transaction interceptor not found
Core: TransactionInterceptor#invoke

TransactionAspectSupport

Transaction aspect support. The internal class TransactionInfo encapsulates transaction related attributes

TransactionAspectSupport.TransactionInfo

protected final class TransactionInfo {
    @Nullable
    private final PlatformTransactionManager transactionManager;

    @Nullable
    private final TransactionAttribute transactionAttribute;

    private final String joinpointIdentification;

    @Nullable
    private TransactionStatus transactionStatus;

    @Nullable
    private TransactionInfo oldTransactionInfo;

2.2 programmatic and declarative transactions

Programming transaction

Demo code:

public class SpringTransactionExample {
    private static String url = "jdbc:mysql://127.0.0.1:3306/test";
    private static String user = "root";
    private static String password = "root";



    public static void main(String[] args) {

        // Get data source
        final DataSource ds = new DriverManagerDataSource(url, user, password);
        // Programming transaction
        final TransactionTemplate template = new TransactionTemplate();
        // Set up transaction manager
        template.setTransactionManager(new DataSourceTransactionManager(ds));

        template.execute(new TransactionCallback<Object>() {
            @Override
            public Object doInTransaction(TransactionStatus status) {
                Connection conn = DataSourceUtils.getConnection(ds);
                Object savePoint = null;
                try {
                    {
                        // insert
                        PreparedStatement prepare = conn.
                                prepareStatement(
                                "insert INTO account (accountName,user,money) VALUES (?,?,?)");
                        prepare.setString(1, "111");
                        prepare.setString(2, "aaa");
                        prepare.setInt(3, 10000);
                        prepare.executeUpdate();
                    }
                    // Set save point
                   savePoint = status.createSavepoint();
                    {
                        // insert
                        PreparedStatement prepare = conn.
                                prepareStatement(
                                "insert INTO account (accountName,user,money) VALUES (?,?,?)");
                        prepare.setString(1, "222");
                        prepare.setString(2, "bbb");
                        prepare.setInt(3, 10000);
                        prepare.executeUpdate();
                    }
                    {
                        // to update
                        PreparedStatement prepare = conn.prepareStatement(
                                "UPDATE account SET money= money+100 where user=?");
                        prepare.setString(1, "aaa");
                        prepare.executeUpdate();
                        //int i=1/0;

                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } catch (Exception e) {
                    System.out.println("Update failed");
                    if (savePoint != null) {
                        status.rollbackToSavepoint(savePoint);
                    } else {
                        status.setRollbackOnly();
                    }
                }
                return null;
            }
        });
    }


}

Declarative transaction

@Transactional

<!-- Enable annotation support for transaction control -->  
<tx:annotation-driven transaction-manager="txManager"/>

Transaction annotation configuration, acting on classes and methods

Attribute nameexplain
nameWhen there are multiple transaction managers in the configuration file, you can use this attribute to specify which transaction manager to select.
propagationTransaction propagation behavior. The default value is REQUIRED.
isolationThe isolation of transactions. The DEFAULT value is DEFAULT.
timeoutThe timeout of the transaction. The default value is - 1. If the time limit is exceeded but the transaction has not completed, the transaction is automatically rolled back.
read-onlySpecifies whether the transaction is read-only. The default value is false; To ignore methods that do not require transactions, such as reading data, you can set read only to true.
rollback-forIt is used to specify the exception types that can trigger transaction rollback. If multiple exception types need to be specified, each type can be separated by commas.
no-rollback- forThrow the exception type specified by no rollback for without rolling back the transaction.

Java Configuration

@EnableTransactionManagement

Register two components with the container using the transaction management configuration selector

  • AutoProxyRegistrar

    Register a post processor of infrastructure advisor autoproxycreator in the container, return a proxy object (enhancer), and the proxy object execution method is called by interceptor chain;

  • ProxyTransactionManagementConfiguration is a @ Configuration

    •    Register a transaction enhancer with the container transactionAdvisor；

    •    AnnotationTransactionAttributeSource Parsing transaction annotations

    •    Transaction interceptor transactionInterceptor

@Configuration
@ComponentScan("com.xuchang")
@EnableTransactionManagement
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class AppConfig {

    @Bean
    public DataSource dataSource(){
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?characterEncoding=utf8");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

2.3 transaction invalidation

  • Is the Bean a proxy object

  • Is the entry function public

  • Whether the database supports transactions (MyIsam of Mysql does not support transactions), and only row locks support transactions

  • Is the tangent point configured correctly

  • The call between internal methods invalidates the transaction

    Because this is not a proxy object, you can configure expose proxy = "true" through aopcontext Currentproxy() get

    A proxy object to the current class.

    <!-- expose-proxy="true" Class can get the proxy object of the current class -->
<aop:aspectj-autoproxy expose-proxy="true"/>

    @EnableAspectJAutoProxy(exposeProxy = true)

    You can also inject the current bean

  • Is the exception type configured correctly

    By default, only RuntimeException and Error are supported, and exception checking is not supported

    To support checking exceptions, you need to configure rollbackFor

    @Transactional(rollbackFor = Exception.class)

    Abnormal system:

Source code analysis

#Transaction interceptor not found
TransactionInterceptor#invoke
# Transaction related calls
TransactionAspectSupport#invokeWithinTransaction
#Logic of exception rollback
TransactionAspectSupport#completeTransactionAfterThrowing

#Exception rollback
txInfo.transactionAttribute.rollbackOn(ex)

#You can set exception rollback rules  
RuleBasedTransactionAttribute#rollbackOn

# Default exception rollback rule
DefaultTransactionAttribute#rollbackOn
public boolean rollbackOn(Throwable ex) {
	return (ex instanceof RuntimeException || ex instanceof Error);
}

2.4 communication mechanism of affairs

spring defines seven transaction propagation behaviors in the TransactionDefinition interface:

  • propagation_ Requisited: if there is no transaction at present, create a new transaction. If there is already a transaction, join it. This is the most common choice.
  • propagation_supports: supports the current transaction. If there is no current transaction, it will be executed in a non transaction method.
  • propagation_mandatory: use the current transaction. If there is no current transaction, an exception will be thrown.
  • propagation_required_new: create a new transaction. If there is a current transaction, suspend the current transaction.
  • propagation_not_supported: perform operations in a non transactional manner. If there is a transaction, suspend the current transaction.
  • propagation_never: execute the operation in a non transactional manner. If the current transaction exists, an exception will be thrown.
  • propagation_nested: if a transaction currently exists, it is executed within a nested transaction. If there is no transaction at present, the and propagation are executed_ Required similar operations

Common transaction propagation mechanisms:

  • PROPAGATION_REQUIRED

    This is also the default propagation mechanism;

  • PROPAGATION_REQUIRES_NEW

    Always start a new transaction. This propagation mechanism is applicable to operations that are not affected by the parent method transaction. For example, in some business scenarios, the business log needs to be recorded for asynchronous backchecking. No matter whether the entity business logic is completed or not, the log needs to be recorded. The log cannot be lost because the entity business logic reports an error;

  • PROPAGATION_NOT_SUPPORTED

    It can be used to send prompt messages, station letters, text messages, e-mail prompts, etc. It does not belong to and should not affect the entity business logic. Even if the sending fails, the entity business logic should not be rolled back.

2.5 source code analysis

#Transaction interceptor not found
TransactionInterceptor#invoke
# Transaction related calls
>TransactionAspectSupport#invokeWithinTransaction

#Return transaction informationtransactioninfo
>TransactionAspectSupport#createTransactionIfNecessary

# The returned TransactionStatus contains the logic of the transaction propagation attribute
>AbstractPlatformTransactionManager#getTransaction

After the breakpoint and spring source code, draw the flow chart of spring transaction execution on processon

https://www.processon.com/view/5eea03981e085326373032a7

Topics: Java Spring

Hot Topics

©2024 Programmer Think