Skip to content

Isolation level

Read Submitted

MatrixOne Intelligence defaults to read commited (Read Committed) isolation level, and its features are as follows:

  • Only data that has been committed by other transactions can be read between different transactions, and data that has not been submitted 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.

Read the submitted principle

  • When a transaction begins, the database generates a unique transaction ID for the transaction.
  • When generating the timestamp of the transaction ID, each time the data is added, deleted, modified, and checked, TAE automatically detects whether there is an updated timestamp in the corresponding table. If so, the update timestamp is the latest.
  • When operating on data, the TAE caches the operation's data in memory. When submitting a transaction, the TAE writes the data in memory to disk.

Read submitted examples

You can refer to the following example to understand theread committed isolation level.

First, in MatrixOne Intelligence, we create a database named test with table t1 and insert data:

create database test;
use test;
CREATE TABLE t1
(
tid INT NOT NULL primary key,
tname VARCHAR(50) NOT NULL
);
INSERT INTO t1 VALUES(1,'version1');
INSERT INTO t1 VALUES(2,'version2');

In session 1, start a transaction:

use test;
begin;
UPDATE t1 SET tname='version3' WHERE tid=2;
SELECT * FROM t1;

In session 1, the results we can see are as follows:

+------+--------------+
|tid | tname |
+------+--------------+
| 2 | version3 |
| 1 | version1 |
+------+--------------+

At this time, session 2 is opened, and a new transaction is opened to query the content of t1:

use test;
begin;
SELECT * FROM t1;

The results you see are still the original data:

+------+--------------+
|tid | tname |
+------+--------------+
| 1 | version1 |
| 2 | version2 |
+------+--------------+

In session 2, modify the line with tid=1:

UPDATE t1 SET tname='version0' WHERE tid=1;

At this time, the content of t1 query in session 1 is still the modified data:

SELECT * FROM t1;
+------+--------------+
|tid | tname |
+------+--------------+
| 1 | version1 |
| 2 | version3 |
+------+--------------+

After submitting your own data in session 2, querying session 1 and you will find that the content of session 1 has become the data after submitting session 2:

  • Session 2:
-- Submit data in session 2:
COMMIT;
  • Session 1:
-- Query whether the content of session 1 has become the data after session 2 is submitted:
SELECT * FROM t1;
+------+--------------+
|tid | tname |
+------+--------------+
| 1 | version0 |
| 2 | version3 |
+------+--------------+

Snapshot Quarantine

In MatrixOne Intelligence, Snapshot Isolation is also supported. In order to be consistent with the MySQL isolation level, MatrixOne Intelligence snapshot isolation is also called Repeatable Reading (REPEATABLE READS). The implementation principle of this level of isolation is as follows:

Snapshot isolation principle

  • When a transaction starts, the database generates a transaction ID for the transaction, which is a unique ID.
  • When generating the timestamp of the transaction ID, a snapshot of the corresponding data is generated. At this time, all operations of the transaction are performed based on the snapshot.
  • When the transaction commits the data to be modified, release the transaction ID and data snapshot.

Snapshot Quarantine Example

You can refer to the following example to help understand snapshot isolation.

First, in MatrixOne Intelligence, we create a database test and table t1:

create database test;
use test;
CREATE TABLE t1
(
tid INT NOT NULL primary key,
tname VARCHAR(50) NOT NULL
);
INSERT INTO t1 VALUES(1,'version1');
INSERT INTO t1 VALUES(2,'version2');

In session 1, start a transaction:

use test;
begin;
UPDATE t1 SET tname='version3' WHERE tid=2;
SELECT * FROM t1;

In session 1, the result we can see is as follows:

+------+--------------+
|tid | tname |
+------+--------------+
| 2 | version3 |
| 1 | version1 |
+------+--------------+

At this time, start session 2 and query the content of t1:

use test;
SELECT * FROM t1;

The results you see are still the original data:

+------+--------------+
|tid | tname |
+------+--------------+
| 1 | version1 |
| 2 | version2 |
+------+--------------+

In session 1, we commit the transaction:

COMMIT;

At this time, the content of querying t1 in session 2 becomes the submitted data:

SELECT * FROM t1;
+------+--------------+
|tid | tname |
+------+--------------+
| 1 | version1 |
| 2 | version3 |
+------+--------------+