SAP HANA – SQL Transaction Management Statements

Hello Everyone,

In this article, we are going to look at different SQL Transaction Management Statements (TMS) available in SAP HANA along with their syntax and examples.

 

Different statements available as part of Transaction Management in SAP HANA are,

COMMIT
LOCK TABLE
ROLLBACK
SET TRANSACTION

Let’s look in detail about each of these statements.

COMMIT

COMMIT:

  • This statement is used to save all the changes performed on the system permanently.
  • If COMMIT command is issued and successfully processed, any change on the system which current transaction has done is applied to the system and the change will be visible to other jobs which will start in the future.
  • The changes which has already committed via COMMIT command cannot be reverted.
  • In a distributed system, standard 2-phase-commit protocol is complied. In the first phase, transaction coordinator consults every participant whether if it is ready to commit, and sends the result to the participants in the second phase.

COMMIT command only works in ‘autocommit’ disabled session.

  • By default SQL console screen that we open in SAP HANA Studio has ‘Autocommit’ setting ‘ON’. If we change the setting to ‘OFF’, then every time we make changes to either system configuration or write DMS operation we have to write COMMIT.

1

Syntax: COMMIT

Examples:

Change the ‘Autocommit’ to ‘OFF’ in SQL Console properties and write the below statements.

  1. Insert the new record into table ORDERS_DATA

Ans: INSERT INTO SAP_STUDENT.ORDERS_DATA VALUES (9999,123,728,’2015-10-28′,60,15000)

  1. Execute SELECT statement on table ORDERS_DATA (we should not see the record inserted using above statement)

Ans: SELECT * FROM SAP_STUDENT.ORDERS_DATA

  1. Execute COMMIT.

Ans: COMMIT

  1. Execute SELECT statement on table ORDERS_DATA (we should see the new record now).

Ans:  SELECT * FROM SAP_STUDENT.ORDERS_DATA

LOCK TABLE

LOCK TABLE:

  • This statement is used to acquire an exclusive lock for a table in SAP HANA database.
  • If the NOWAIT option is specified and LOCK TABLE fails to acquire lock an error code is returned.

When the error is generated the current transaction is not rolled-back.

Syntax: LOCK TABLE <schema_name>.<table_name> IN EXCLUSIVE MODE [NO WAIT]

LOCK MODE: EXCLUSIVE

  • Can be acquired by LOCK TABLE command explicitly or DDL command implicitly
  • The transaction that holds the lock can read and write the table.
  • Only the transaction that holds the lock can access the table.
  • Lock requests for the table by other transactions are blocked while the EXCLUSIVE lock is held.
  • The database releases acquired locks at the end of the transaction.

LOCK MODE: INTENTIONAL EXCLUSIVE

  • Acquired by DML implicitly.
  • Multiple transactions can acquired a INTENTIONAL EXCLUSIVE lock.
  • EXCLUSIVE lock requests for the table by other transactions are blocked while the INTENTIONAL EXCLUSIVE lock is held.
  • The database releases acquired locks at the end of the transaction.

We can check the current active lock in the system with the help of view M_OBJECT_LOCKS under SYS schema.

M_OBJECT_LOCKS – Information about active locks in the system.

Examples:

  1. Lock table ORDERS_DATA in exclusive mode.

Ans: LOCK TABLE SAP_STUDENT.ORDERS_DATA IN EXCLUSIVE MODE

  1. Lock table ORDERS_DATA in exclusive mode with no wait time.

Ans: LOCK TABLE SAP_STUDENT.ORDERS_DATA IN EXCLUSIVE MODE NOWAIT

ROLLBACK

ROLLBACK:

  • This statement is used to undo all the changes made during the current transaction/session.
  • During a transaction data manipulation language (DML) modifications to the database can be explicitly reverted via ROLLBACK command.
  • After ROLLBACK command is issued, changes made during the current transaction are reverted and current database session is set to an idle state. ROLLBACK command only works with an ‘autocommit’ disabled session.
  • If we attempt to use the ROLLBACK statements in an autocommit enabled session, nothing will occur as transactions are automatically committed to the database.

Before attempting to execute the example below, please ensure that we are using an ‘autocommit’ disabled session.

By default SQL console screen that we open in SAP HANA Studio has ‘Autocommit’ setting ‘ON’. If we change the setting to ‘OFF’, then every time we make changed to either system configuration or write DMS operation we can use ROLLBACK to revert the changes.

2

Syntax: ROLLBACK

Examples:

  1. Insert the new record into table ORDERS_DATA

Ans: INSERT INTO SAP_STUDENT.ORDERS_DATA VALUES (9999,123,728,’2015-10-28′,60,15000)

  1. Execute SELECT statement on table ORDERS_DATA (we should not see the record inserted using above statement)

Ans: SELECT * FROM SAP_STUDENT.ORDERS_DATA

  1. Execute ROLLBACK.

Ans: ROLLBACK

  1. Execute SELECT statement on table ORDERS_DATA (we should not see the new record as we revert the transaction).

Ans:  SELECT * FROM SAP_STUDENT.ORDERS_DATA

Check Also

Capture

SAP HANA – SQL Session Management Statements

Hello Everyone, In this article, we are going to look at different SQL Session Management …

Leave a Reply

Your email address will not be published. Required fields are marked *

20 − fourteen =