XAP

XAP Skyline

Smart DIH

Transactions Control Language (TCL) Syntax

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group are successfully completed. If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results: success or failure. You can explore more about transactions

This is supported by JDBC V3

Transaction Control Language (TCL Syntax)

BEGIN/START and END/COMMIT are available to define the beginning and end of a Logical Unit of Work as a transaction.

The default transaction isolation level is READ_COMMITTED.

Example 1:

Syntax Explanation Examples

BEGIN (or START);

  • Starts a Transaction.

  • Commands are case insensitive, meaning both upper and lower cases will work.

  • Transaction keyword is optional. This is also case insensitive.

Using Begin:

Using Start Transaction:

INSERT …xxx…;

 

  

INSERT …yyy…;

 

  

SELECT …xxx…;

OK

  

SELECT …yyy…;

OK

  

END (or COMMIT);

  • Both End and Commit commands are used to commit the changes.

  • Commands are case insensitive, meaning both upper and lower cases will work.

  • Commit command could be used both inside transaction block and instead of End command.

  • Commit inside transaction block committing all above changes and then a new transaction is created to proceed below statements.

Using End:

Using Commit:

Example 2:

Syntax Explanation Examples

BEGIN (or START);

Begin transaction

  

INSERT …xxx…;

 

  

SELECT …xxx…;

OK

  

ROLLBACK;

  • Rollback is used to rollback all the changes during one transaction (changes until explicit commit was run before).

  • Commands are case insensitive, meaning both upper and lower cases will work.

  • Rollback command can be called:

    1. Explicitly - Explicit Rollback rollbacks all changes during transaction execution and then a new transaction is created to proceed below statements.

    2. In the case where an exception was thrown during transaction execution - rollbacks all changes during transaction and all below statements won’t be executed.

Explicit Rollback Call:

Rollback in the Case of Exception:
     

SELECT …xxx…;

Not found

  • This is because of the rollback. The xxx insert was rolled back and this is the reason the select xxx will return no results.

 

INSERT …yyy…;

 

  

SELECT …yyy…;

OK

  

COMMIT;

End transaction with commit

  

SELECT …yyy…;

OK

  

END (or COMMIT);

  • Both End and Commit commands are used to commit the changes.

  • Commands are case insensitive, meaning both upper and lower cases will work.

  • Commit command could be used both inside transaction block and instead of End command.

  • Commit inside transaction block committing all above changes and then a new transaction is created to proceed below statements.

  

Transaction Properties (Set Env Properties)

Property Explanation

com.gs.jdbc.v3.transaction_manager.transaction_isolation_level

Transaction isolation level. Default transaction isolation level is READ_COMMITTED.

com.gs.jdbc.v3.transaction_manager.commit_timeout

Commit timeout. Default value is MAX_VALUE of Long.

com.gs.jdbc.v3.transaction_manager.rollback_timeout

Rollback timeout. Default value is MAX_VALUE of Long.

com.gs.jdbc.v3.transaction_manager.default_timeout

The requested lease time for the transaction. Default value is 90000.