Skip to content

MatrixOne Intelligence transaction overview

What is a MatrixOne Intelligence transaction?

MatrixOne Intelligence transactions follow the standard definition and basic characteristics (ACID) of database transactions. It is designed to help users ensure that every database data operation behavior can ensure the consistency and integrity of the data in a distributed database environment, and be isolated from each other without interference under concurrent requests.

Transaction Types of MatrixOne Intelligence

In MatrixOne Intelligence, transactions are divided into the following two categories, like general transactions:

  • It is divided into explicit transactions and implicit transactions according to whether there is a clear start and end.
  • According to the stage of using resource locks, it is divided into optimistic transactions and pessimistic transactions.

The classification of these two major types of transactions is not restricted by each other. An explicit transaction can be an optimistic transaction or a pessimistic transaction, and a pessimistic transaction can be an explicit transaction or an implicit transaction.

Explanatory transaction

In MatrixOne Intelligence, a transaction is explicitly declared with START TRANSACTION, which becomes an explicit transaction.

Implicit transaction

In MatrixOne Intelligence, an implicit transaction is if a transaction is not explicitly declared through START TRANSACTION or BEGIN.

Optimistic-transaction

At the beginning of an optimistic transaction, it is assumed that the transaction-related table is in a state where no write conflict occurs, and the insertion, modification or deletion of the data is cached in memory. At this stage, the data will not be locked, and the corresponding data table or data row will be locked when the data is submitted, and unlocked after the submission is completed.

Pessimistic transaction

MatrixOne Intelligence defaults to pessimistic transactions. When the pessimistic transaction starts, it is assumed that the transaction-related table is in a state where a write conflict occurs. Lock the corresponding data table or data row in advance. After completing the locking action, the data insertion, modification or deletion is cached in memory. After committing or rolling back, the data is completed and the lock is released.

MatrixOne Intelligence supports cross-database transactions

MatrixOne Intelligence provides transactional capabilities that support cross-databases, allowing one transaction to access and modify multiple different databases simultaneously.

In practical applications, some business requirements may involve operations of multiple databases, and cross-database transactions emerge to meet this requirement. This capability ensures that operations between different databases can be consistent and isolated, just like performing operations within a single database. This means that when you need to perform a series of operations between multiple databases, you can wrap them in a transaction so that you can do these operations while maintaining data integrity and consistency.

Cross-database transactions often play a key role in complex enterprise application scenarios. In these scenarios, different business functions or departments may use different databases, but they need to work together to meet complex business needs. MatrixOne Intelligence's cross-database transaction capabilities increase system flexibility and scalability, but also requires careful design and management to ensure data remains complete and consistent.

Transaction isolation level for MatrixOne Intelligence

MatrixOne Intelligence supports two isolation levels: Read Committed and Snapshot Isolation. The default isolation level is Read Committed.

Read Submitted

Read Committed is the default isolation level for MatrixOne Intelligence and one of the four isolation levels in the SQL standard. Its most prominent features are:

  • Between different transactions, only data that has been committed by other transactions can be read, and data that has not been submitted in status cannot be viewed.
  • Reading the submitted isolation level can effectively prevent dirty writing and dirty reading, but cannot avoid non-repeat reading and phantom reading.
Isolation Level P0 Dirty Write P1 Dirty Read P4C Cursor Lost Update P4 Lost Update
READ COMMITTED Not Possible Not Possible Possible Possible

Snapshot Quarantine

Unlike the four isolation levels defined by the SQL standard, in MatrixOne Intelligence, the supported isolation level is Snapshot Isolation, which isolates betweenREPEATABLE READ andSERIALIZABLE of the SQL-92 standard. Different from other isolation levels, snapshot isolation has the following characteristics:

  • Snapshot isolation does not reflect changes made to the data by other synchronized transactions for data read within a specified transaction. Specifies that the transaction uses the data rows read at the beginning of the transaction.

  • The data is not locked when reading it, so snapshot transactions do not prevent other transactions from writing to the data.

  • Transactions that write data will not prevent snapshot transactions from reading data.

Compared with other isolation levels, snapshot isolation also realizes effective avoidance for scenarios such as dirty read (reading unsubmitted data), dirty write (writing records that were not submitted after modification), fantasy read (reading multiple times before and after, the total amount of data is inconsistent) and other scenarios:

|Isolation Level|P0 Dirty Write|P1 Dirty Read|P4C Cursor Lost Update|P4 Lost Update|P2 Fuzzy Read|P3 Phantom|A5A Read Skew|A5B Write Skew| |---|--|--|--|---|---|---|---|---|---|---| |MatrixOne Intelligence's Snapshot Isolation|Not Possible|Not Possible|Not Possible|Not Possible|Not Possible|Not Possible|Not Possible|