Locking Behavior and Transaction Mode

Previous Next

In Microsoft SQL Server and Sybase RDBMS servers, a record cannot be read if it is already locked by another user. This may cause unwanted behavior in a USoft application.

You can influence this behavior in two ways:

1.The Rules Engine provides a BatchManipulation transaction mode, in which all data manipulations performed by an end user are sent to the server at commit time. This prevents end users from locking each other out, and greatly reduces the time during which records must be locked. You can set the transaction mode:

•In the Windows Designer for a Client/Server application, with the Transaction Mode property.

The Transaction Mode property is an application-level property.

•As a resource when accessing a USoft application via the Remote Rules Service or Batch Runner:

*transactionMode : BatchManipulation

•By invoking the SetTransactionMode method of the internal RulesEngine component.

The default value is the ImmediateManipulation transaction mode.

With BatchManipulation transaction mode, if the transaction fails, a rollback is sent to the RDBMS. Immediate feedback is returned for referential integrity, and cascading update and deletes are executed normally, even if the row has yet to be stored in the server.

Restricted update and delete, unique keys, and foreign keys are all checked both at store-time and at commit-time. If a commit is refused, it will appear as an error message (rather than in the transaction window). Additional fields will reflect updates at commit-time and, if possible, at store time. Re-querying data that has been stored (that is, saved locally) but not committed is possible, except for the following restrictions:

•The SELECT may not contain JOINS.

•The SELECT statement may not contain subqueries.

•The SELECT statement may not return DATE fields.

•The Refresh and Undo record-level commands will not work until pending changes have been committed.

2.By default, the lock timeout on SQL Server and Sybase is 10 seconds: the Rules Engine waits for a maximum of 10 seconds when requesting a record that is already locked.

This timeout can be changed by adding the following resource setting to the command line, using the -xrm option:

   -xrm "*OledbLogin.timeout:60"

or

   -xrm "*timeout:60"

In this instance, the result is a timeout of 60 seconds.

Increasing the timeout has the advantage that timeout error messages do not appear too soon, and stop the transaction too soon. A disadvantage is that too long a timeout may cause a user to wait too long for a locked record.