management affairs
A transaction is a sequence of database operations defined by the user. These operations are either done or not done. It is an indivisible unit of work. The transaction control commands supported by the openGauss database include start, set, commit, and rollback transactions. The transaction isolation levels supported by the openGauss database are READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, and SERIALIZABLE. READ UNCOMMITTED is not recommended, and SERIALIZABLE is equivalent to REPEATABLE READ.
transaction control
The following transaction commands are supported by the database:
start transaction
Users can start transactions using the START TRANSACTION and BEGIN syntax.
set transaction
Users can use SET TRANSACTION or SET LOCAL TRANSACTION syntax to set transaction characteristics. For detailed operations, please refer to SET TRANSACTION.
commit transaction
Users can use COMMIT or END to complete the function of submitting a transaction, that is, all operations of submitting a transaction. For detailed operations, please refer to COMMIT | END.
rollback transaction
Rollback means that some kind of failure occurs during the running of the transaction, and the transaction cannot continue to execute. The system cancels all the completed operations on the database in the transaction. For detailed operations, please refer to ROLLBACK.
transaction isolation level
Transaction isolation level, which determines how multiple transactions handle the same object concurrently.
illustrate:
After the first data modification statement (SELECT, INSERT, DELETE, UPDATE, FETCH, COPY) in the transaction is executed, the transaction isolation level cannot be set again.
READ COMMITTED : Read committed isolation level, transactions can only read committed data and not uncommitted data, which is the default value.
In effect, the SELECT query looks at a snapshot of the database at the moment the query starts running. However, SELECT can see the execution results of previous updates in its own transaction. Even if the previous update has not been committed. Note that two adjacent SELECT commands within the same transaction may see different snapshots, because other transactions commit during the execution of the first SELECT.
Because in read-committed mode, each new command starts with a new snapshot that contains all committed transactions up to that point, subsequent commands in the same transaction will see any committed effects on other affairs. The concern here is whether to see an absolutely consistent view of the database in a single command.
The partial transaction isolation provided by read committed mode is sufficient for many applications, and this mode is fast and easy to use. However, for applications that do complex queries and updates, it may be necessary to ensure that the database has a more stringent view of consistency than read-committed mode.
REPEATABLE READ : transaction repeatable read isolation level, the transaction can only read the submitted data before the start of the transaction, and cannot read the uncommitted data and the modification submitted by other concurrent transactions during the execution of the transaction (however, the query can view its own transaction execution result of a previous update in , even if the previous update has not yet been committed).
This level is different from read committed, because the query in the repeatable read transaction sees the snapshot at the beginning of the transaction, not the snapshot at the beginning of the current query inside the transaction, that is, the select command inside a single transaction always It is to view the same data, but not to view the data submitted by other concurrent transactions after the start of its own transaction. Applications using this level must be prepared to retry transactions because serialization failures may occur.
Top comments (0)