5.4 Commitment Control

When working with commitment control, you should be aware of the following:

• All files are automatically under commitment control all of the time under Visual LANSA.

• Unless you indicate that a file is under commitment control (and therefore that COMMIT commands are issued by the application), an SQL/ODBC COMMIT will be issued after each I/O operation that updates the database.
  
  This approach closely emulates the IBM i running a job without commitment control active, and it is very "clean and simple" to use. There are no known complications.

• As a result of duplicate key errors, some database management systems lock rows as though database changes have occurred. This type of error is returned as a validation error to an application. To release any such locks for files that are not under commitment control, LANSA automatically issues a COMMIT after a duplicate key error. For files that are under commitment control, it is the application developer's responsibility to determine whether the DBMS being used locks rows after a failed insert or update. If the DBMS does lock rows, you must issue a COMMIT or ROLLBACK so that these locked rows do not cause blocking for other users that may be attempting to access the same rows.

• Locks only timeout by default on IBM i. Refer to Lock Timeout in the Technical Reference for information on how you can emulate this feature for other platforms.

• In SQL, each transaction has an Isolation Level, which is similar to IBM's Lock Level for commitment control. The isolation level determines the degree to which the transaction is isolated from changes made by other connections to the database. Refer to ODBI Parameter in the Technical Reference and your database's manuals for further information.

• The function options *PGMCOMMIT and *NOPGMCOMMIT are ignored for Visual LANSA and RDMLX code.

• You should revise your commitment control strategy with all this information in mind. Some things to think about are:

• If you are going to design and build applications using Visual LANSA commitment control then you should acquire some knowledge of how it works and differs from the IBM i implementation of commitment control.

• Despite the hype, database management systems exhibit inter and intra commitment control differences. The same may be said for row locking methods in general, and for how row locking and timeouts are affected by commitment control in particular.

• Avoid actually using "programmer controlled" COMMIT and ROLLBACK techniques whenever possible. This simplifies your applications and makes you less likely to be affected by different DBMS commit/rollback methods in the future.

• If your application uses mixed mode commitment control (ie. some tables under RDML level commitment control, some not) please refer to 5.4.1 Using Mixed Mode Commitment Control for instructions on how to make this work on platforms other than the IBM i.

• If you must use program level COMMIT and ROLLBACK then keep your use clean and simple. Some very important considerations, that apply to all platforms, and to the general use of commitment control in any situation are:

• Keep it simple. Complex and elaborate commitment control strategies may look good on paper, but they often do not work in practice, and will become a maintenance nightmare in the future. Judge the cost of implementing, maintaining and fully testing the strategy versus the actual benefits it provides at the business level. Often the cost is very, very high and the benefit (as opposed to some simpler restore strategy) is very, very small.

• Use simple and well defined transaction boundaries.

• Avoid transaction boundaries that span interactions with the user via the user interface. You may be holding/locking all sorts of system resources while your end user is off having a cup of coffee.

• Avoid complicated strategies that involve subroutines that update data and rely on their callers to do the commits. This approach will quickly turn into a maintenance nightmare. What happens when the subroutine is modified to call another subroutine that actually issues a commit?

• If an UPDATE command includes BLOB or CLOB fields, the UPDATE is not a single operation at the database layer. Therefore, when the I/O command returns ER, it is possible that the non-LOB fields have been updated, but one or more LOB fields have not. Therefore, LANSA recommends executing a ROLLBACK (or ABORT) to return your database to its previous state. However, please note that some database management systems do not rollback BLOB and CLOB data. Please refer to your database manuals for further details.