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 JDBCClosed Java DataBase Connectivity. This is an application programming interface (API) for the Java programming language, which defines how a client may access a database. 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.