Database Transaction

Feature Description

A Database Transaction is a sequence of database operations that access and possibly manipulate a variety of data. These operations are either all performed or none performed, and are an indivisible unit of work. Database Transactions have four basic characteristics:

Atomicity: All operations in a transaction are indivisible in the database, either all are performed or none are performed.
Consistency: The Result of several transactions executed in parallel must be consistent with the result of a sequential serial execution.
Isolation: The execution of a transaction is not interfered by other transactions, and the intermediate results of the execution of a transaction must be transparent to other transactions.
Persistence: For any committed transaction, the system must ensure that the changes made to the database by the transaction are not lost, even if the database fails.

Based on the four features of Database Transaction, for example, when importing data from external to GIS database, the transaction can ensure the complete import of data and avoid data corruption or inconsistency caused by failure in the middle (such as sudden power failure, operating system crash, etc.).

In SuperMap iDesktopX, Database Transaction is used as follows:

PostGIS Database Datasource is supported.
Support the Start Transaction of the user who uses and Creates Permissions for Datasource, but the operation permission for Dataset is restricted by User permissions .
Supported Dataset Types exclude: Image/Raster Dataset, Vector Dataset with Pyramid, Mosaic Dataset, and Register Version Dataset.

This article mainly introduces how to use Database Transaction, including Start Transaction, operating on the database, rolling back or committing data to end the transaction operation.

Start Transaction: Once enabled, you can perform transaction operations on the database.
Commit Transaction: Commit all operations after the Start Transaction to the database, and permanently save the update results to the database.
Rollback Transaction: Removes all operations after the Start Transaction. Changes to the database are not saved.

Starting with SuperMap iDesktopX 11i (2024), Database Transaction functionality is available.

Steps

Open Database Datasource and Start Transaction. There are two ways to Start Transaction:

Method 1: Click the Data tab -> Database Transaction Grouping -> Start Transaction button. Check the Datasource to be managed in the Start Transaction dialog. The button is available when there is a Datasource in the Current Workspace that does not have a Start Transaction.
Method 2: Right click in Workspace Manager and select Database Datasource - > Database Transaction -> for Start Transaction management. Start Transaction button .

After a DatasourceStart Transaction is performed in any of the above ways, a blue dot appears on the Datasource node icon to indicate that the Datasource is in a transaction. If the Datasource is disconnected from the database, the transaction operation will be invalidated and automatically restored to the state before the Start Transaction.

When multiple users start Transaction at the same time, they may be affected by other transactions. Therefore, different transaction locks are provided on the Dataset to ensure that the Database Transaction meets its four basic characteristics.

There are other transaction locks: the Dataset is being queried, edited, or analyzed by another user.
Current transaction lock: The current user queries, edits, or analyzes the Dataset first. Other users may operate the Dataset at the same time.
No transaction lock: No user queries, edits, or analyzes the Dataset.

You can check the transaction lock status of the dataset through the dataset right-click menu -> Manage -> Transaction Lock. The impact of different transaction lock statuses on other functions is as follows:

Functionality	Other Transaction Locks	Current Transaction Lock	No Transaction Lock
Create Dataset	Not affected by transactions, directly committed to the database
Modify Dataset Name, Modify Field Alias	Not affected by transactions, directly committed to the database
Import External Data as a New Dataset	Not affected by transactions. If the import fails, the dataset attribute table structure is directly saved to the database, while data records are saved within the transaction and only committed after the transaction is completed. If Create Spatial Index is not selected during dataset import, the imported records will be saved within the current transaction. They must be committed/rolled back to save or discard all uncommitted records before and during import. The dataset attribute table structure is directly saved to the database and is not affected by transactions. If Create Spatial Index is selected during dataset import, the current transaction is committed by default, and a new transaction starts automatically after import. Uncommitted records before and during import cannot be rolled back.
Delete Dataset	Not Supported	Not Supported	Directly deleted and committed to the database
Object Editing (Including Spatial and Attribute Information)	Can edit different objects When editing the same object, the first user locks the object, preventing other users from editing. (Editing will pause for 5 seconds, then revert the operation, with an output window prompt such as: "Dataset [NewRegion] submission failed, reason: the record to be modified may have been edited by another transaction.")	Can edit different objects When editing the same object, the first user locks the object, preventing other users from editing. (Editing will pause for 5 seconds, then revert the operation, with an output window prompt such as: "Dataset [NewRegion] submission failed, reason: the record to be modified may have been edited by another transaction.")	Can edit all objects
Operations on Attribute Table Structure, Including Data Import (Appending Fields)	Modification Not Supported	Current transaction is committed by default before editing the attribute table structure (including creating fields, deleting fields, modifying field types, modifying field default values, and modifying field names). The edits are directly committed to the database, and a new transaction is started.
Analysis	If there are no new/deleted fields, analysis results are saved within the current transaction. If there are new/deleted fields, analysis fails.	If there are no new/deleted fields, analysis results are saved within the current transaction. If there are new/deleted fields, the current transaction is committed by default, field modifications are directly saved to the database, and a new transaction is started. This means that operations before the analysis are saved to the database, while subsequent operations are saved within the new transaction.
Register Version	Not Supported	Supports registering replicas, requires committing the current transaction first, and after registration, it is not affected by transactions.	Supports registering replicas, and after registration, it is not affected by transactions.

After the transaction operation is completed, all changes to the database can be undone through Rollback Transaction, or all changes to the database can be saved through Commit Transaction, thus ending the transaction. The following two functional operation modes are provided:
- Method 1: Click the Data tab -> Database Transaction Grouping -> Rollback Transaction/Commit Transaction, check the Datasource to end the transaction in the pop-up dialog box.
- Method 2: Right click in Workspace Manager and select Datasource -> Database Transaction -> where rollback/Commit Transaction is required. Rollback Transaction/Commit Transaction button.

Feature Description

Steps

Related Topics