Transactions
Tags: opengauss
Published: 2023-03-07 04:03:33

A transaction is a user-defined sequence of database operations, which form an integral unit of work. In openGauss, you can start, set, commit, and roll back transactions. openGauss supports the **READ COMMITTED** and **REPEATABLE READ** transaction isolation levels.

**· READ COMMITTED**: At this level, a transaction can access only committed data. This is the default level.

The SELECT statement accesses the snapshot of the database taken when the query begins. It can also access the data updates in its transaction, regardless of whether they have been committed. Note that different database snapshots may be available to two consecutive SELECT statements for the same transaction, because data may be committed for other transactions while the first SELECT statement is executed.

At the **READ COMMITTED** level, the execution of each statement begins with a new snapshot, which contains all the transactions that have been committed at the execution time. Therefore, during a transaction, a statement can access the result of other committed transactions. Note whether a single statement always accesses absolutely consistent data in a database.

Transaction isolation at this level meets the requirements of many applications, and is fast and easy to use. However, applications performing complicated queries and updates may require data that is more consistent than this level can provide.

**· REPEATABLE READ**: A transaction can only read data committed before it starts. Uncommitted data or data committed in other concurrent transactions cannot be read. However, a query can read earlier data updates in its transaction, regardless of whether they have been committed. **READ UNCOMMITTED** differs from this level in that a transaction reads the snapshot taken at the start of the transaction, not at the beginning of the current query within the transaction. Therefore, the SELECT statement within a transaction always reads the same data, and cannot read data committed by other concurrent transactions after the transaction starts. Applications at this level must be able to retry transactions, because serialization failures may occur.

**Syntax**

**·** Start a transaction.

Execute the BEGIN statement to start a transaction.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/teh3q884urwpxy8bno3y.png)

Execute the START TRANSACTION statement to start a transaction.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/igrb9qfscejkigoum2ev.png)

**·** Set transaction features.

Use the SET TRANSACTION statement or the syntax to set the isolation level and read/write mode of a transaction.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uqev4lykc3gnxj2mr13l.png)

**·** Commit a transaction.

Execute the COMMIT or END statement to commit all operations of a transaction.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q8346f0rqeinpur9c04g.png)

**·** Roll back a transaction.

If a fault occurs during a transaction and the transaction cannot proceed, the system performs rollback to cancel all the completed database operations related to the transaction.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ddf8sdoooag49ory38mq.png)

**Parameter Description**

**· WORK | TRANSACTION**

Specifies the optional keyword in BEGIN format without functions.

**· ISOLATION LEVEL**

Specifies the transaction isolation level that determines the data that a transaction can view if other concurrent transactions exist.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7kkwwz8tzoo53rkzbnoo.png)

Value range:

**· READ COMMITTED**: Only committed data is read. This is the default.
**· REPEATABLE READ**: Only the data committed before transaction start is read. Uncommitted data or data committed in other concurrent transactions cannot be read.
**· SERIALIZABLE**: Currently, this isolation level is not supported in functions. It is equivalent to **REPEATABLE READ**.
**· READ WRITE | READ ONLY**

Specifies the transaction access mode (read/write or read only).

**· LOCAL**

Specifies that the specified statement takes effect only for the current transaction.

**· SESSION**

Specifies that the specified statement takes effect for the current session.

Value range: a string. It must comply with the identifier naming convention.

**· COMMIT | END**

Commits the current transaction and makes all changes made by the transaction become visible to others.

**Examples**

Assume that the customer_t1 table exists and contains the following data:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rsl8ykn1yr7rd8103l6y.png)

Start a transaction, delete the row where amount is set to 1000 from the table, and execute the ROLLBACK statement to undo all changes.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mdq6ca6823jregtr9vrd.png)

In the customer_t1 table, the row where amount is set to 1000 still exists.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/070uk8d7tgc3lkkszyur.png)

Start another transaction, delete the record whose amount is set to 1000 from the table, and execute the COMMIT command to commit all changes.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/spnjmku2tn1cnjott7oj.png)

The record whose amount is set to 1000 has been deleted from the customer_t1 table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b1fb2gyjlvzx13jpwcyo.png)