Concurrency management

Data consistency and concurrency concepts

  • Data Consistency applies to situations when readers want to access data currently being modified by writers.
  • Concurrent Data Access applies to situations when several writers are accessing the same data for modification.
  • Locking Granularity defines the amount of data concerned when a lock is set (for example, row, page, table).

Informix®

Informix uses a locking mechanism to handle data consistency and concurrency. When a process changes database information with UPDATE, INSERT or DELETE, an exclusive lock is set on the touched rows. The lock remains active until the end of the transaction. Statements performed outside a transaction are treated as a transaction containing a single operation and therefore release the locks immediately after execution. SELECT statements can set shared locks, depending on isolation level. In case of locking conflicts (for example, when two processes want to acquire an exclusive lock on the same row for modification, or when a writer is trying to modify data protected by a shared lock), the behavior of a process can be changed by setting the lock wait mode.

Control:

  • Lock wait mode: SET LOCK MODE TO ...
  • Isolation level: SET ISOLATION TO ...
  • Locking granularity: CREATE TABLE ... LOCK MODE {PAGE|ROW}
  • Explicit exclusive lock: SELECT ... FOR UPDATE

Defaults:

  • The default isolation level is READ COMMITTED.
  • The default lock wait mode is NOT WAIT.
  • The default locking granularity is PAGE.

SAP HANA®

SAP HANA uses multi-version row and exclusive locks to manage data consistency and concurrency. Concurrent readers will see a consistent view of the database data without blocking concurrent write operations. Updates are implemented by inserting new versions of data and not by overwriting existing records. The database engine sets exclusive locks on the modified rows and shared locks when data is read, based on the isolation level. The locks are held until the end of the transaction. When multiple processes want to access the same data, the latest processes must wait until the first finishes its transaction. The lock granularity is at the row. For more details, see SAP HANA documentation.

Control:

  • Lock wait mode: SET TRANSACTION LOCK WAIT TIMEOUT milliseconds. Default is defined by server settings.
  • Isolation level: SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | REPEATABLE READ | SERIALIZABLE }.
  • Locking granularity: row (or table level, if demanded by the SQL commant semantics).
  • Explicit locking: SELECT ... FOR UPDATE
Defaults:
  • The default isolation level is READ COMMITTED.
Important: With SAP HANA, when a lock timeout occurs, the complete SQL transaction is rolled back: SAP HANA will cancel the SQL statements executed in the transaction before the SQL statement which produced the lock timeout.

Solution

The SET ISOLATION TO ... Informix syntax is converted to SET TRANSACTION ISOLATION LEVEL ... for SAP HANA. The table shows the isolation level mappings applied by the database driver:

Table 1. Isolation level mappings done by the SAP HANA database driver
SET ISOLATION instruction in program Native SQL command
SET ISOLATION TO DIRTY READ SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET ISOLATION TO COMMITTED READ [READ COMMITTED] [RETAIN UPDATE LOCKS]

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ISOLATION TO CURSOR STABILITY SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET ISOLATION TO REPEATABLE READ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

When using the SET LOCK MODE instruction in your Genero programs, the SAP HANA driver will execute the equivalent SET TRANSACTION LOCK WAIT TIMEOUT instruction, by converting the number of seconds to milliseconds.

For portability, it is recommended that you work with Informix in the read committed isolation level, to make processes wait for each other (lock mode wait) and to create tables with the "lock mode row" option.

See Informix and SAP HANA documentation for more details about data consistency, concurrency and locking mechanisms.

Regarding transaction rollback when a lock timeout occurs, you may need to review you code, to handle the SAP HANA behavior, where all SQL statements in the transaction are canceled. However, lock timeouts should not occur, if transactions are quick, and each process uses read committed isolation level, and define a lock timeout that is sufficient to let other processes terminate their transactions.