UltraLite transaction processing

A transaction is a logical set of operations that are executed atomically: either all operations in the transaction are stored in the database or none are. An UltraLite application's access to the UltraLite runtime is serialized. While it is possible for multiple transactions to be open simultaneously, UltraLite only processes transactions one at a time. This behavior means that an application cannot:

  • Have blocked transactions (also known as deadlocks). UltraLite never blocks a request based on an existing row lock. In this case, UltraLite immediately returns an error.
  • Overwrite outstanding changes. A transaction cannot overwrite another transaction’s outstanding changes. When a transaction changes a row, UltraLite locks that row until the transaction is committed or rolled back. The lock prevents other transactions from changing the row, although they can still read the row.

For example, two applications, A and B, are reading the same row from the database and they both calculate new values for one of its columns based on the data they read. If A updates the row with its new value and B then tries to modify the same row, B gets an error. An attempt to change a locked row sets the error SQLCODE SQLE_LOCKED, while an attempt to change a deleted row sets the error SQLE_NOTFOUND. Therefore, you should program your application so it checks the SQLCODE value after attempting to modify data.

For more information about how to handle errors, see:

Programming tip

All of the UltraLite APIs—except the C++ API—can operate in autocommit mode. Some APIs use autocommit by default.

This default means that each transaction is automatically committed after each operation. If you are using one of these interfaces, you must set autocommit to off to exploit multi-operation transactions. The way of turning autocommit off depends on the programming interface you are using. In most interfaces it is a property of the connection object.

See: