Application scenarios

Application in actual scenarios

In a financial system, transfers between different users are very common scenarios, and the actual operation of transfers in the database usually involves two steps, first deducting the book amount of one user, and then increasing the book amount of another user. Only by leveraging the atomicity of the transaction can we ensure that the total book funds have not changed. At the same time, the accounts between the two users have completed their respective deductions and increases. For example, User A transfers money to User B at this time 50:

start transaction;
update accounts set balance=balance-50 where name='A';
update accounts set balance=balance+50 where name='B';
commit;

When both update is successful and finally submitted, the entire transfer is truly completed. If any step fails, the entire transaction must be rolled back to ensure atomicity.

In addition, during the transfer process of the two accounts, before submission, whether A or B, what you see is the book balance that has not been transferred yet, which is the isolation of the transaction.

During the transfer process, the database will check whether A's book funds are greater than 50, and whether A and B have any personalities in the system. Only when these constraints are met can the consistency of the transaction be ensured.

After the transfer is completed, regardless of whether the system restarts or not, the data has been persisted, reflecting the persistence of the transaction.

Pessimistic transactions and readings of MatrixOne Intelligence have been submitted

MatrixOne Intelligence defaults to thepessimistic transactions andreads committed** isolation levels, which combines the best performance.

Pessimistic Transaction refers to locking the resource during the transaction to avoid modification or reading of the resource by other concurrent transactions. Pessimistic transactions assume that concurrent transactions may operate on resources and prevent this from happening.

In MatrixOne Intelligence, you can use the UPDATE ... WHERE... statement to achieve pessimistic locking, which locks records that meet the criteria until the transaction is committed or rolled back. For example, the following SQL statement locks the record of id=1 in the user table:

START TRANSACTION;
UPDATE user WHERE id=1;
-- Perform other actions during a transaction, such as modifying the record
COMMIT;

Read Committed is an isolation level that ensures that modifications made by other transactions are not visible to the current transaction before the transaction is committed. Under the read committed isolation level, a transaction can only see the committed data and cannot see the uncommitted data. Therefore, at this isolation level, dirty reading can be avoided.

In SQL, you can set the isolation level to read commit using the SET TRANSACTION ISOLATION LEVEL READ COMMITTED; statement. For example, the following SQL statement querys the record with id=1 in the user table, but if another transaction is modifying the record, the current transaction cannot see uncommitted modifications:

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- If other transactions are modifying the record with id=1 in the user table, the following statement will wait until the lock is released
SELECT * FROM user WHERE id = 1;
COMMIT;

It should be noted that using pessimistic transactions and reading committed isolation levels can avoid some concurrency problems, but may also bring some additional overhead, so you need to weigh the pros and cons and choose to use them according to actual needs.