Record locking and transactions with xfODBC

For updating Synergy databases, we strongly recommend using a Synergy application that’s designed to efficiently maintain database integrity. See Statements that modify data for more information.

We don’t recommend using transactions with xfODBC because of the high overhead they incur.

xfODBC supports the record locking aspect of database transactions, but it does not treat operations in a transaction as a single atomic event. In other words, with xfODBC a transaction is simply a mechanism for locking all rows affected by a statement for the duration of the transaction.

Each operation takes place as soon as its statement is processed (e.g., an insert will take place as soon as an INSERT statement is processed)
No operation can be rolled back.
Committing or rolling back a transaction merely releases records locked for the transaction.

When a transaction is not used, xfODBC locks rows only for the time it takes to process the DELETE, INSERT, or UPDATE statement (autocommit). However, if a transaction is used, xfODBC locks all rows that are read after the ODBC application starts the transaction and holds the locks for the duration of the transaction. Locks are released only when the transaction is committed or rolled back or when the connection to the database is terminated.

Because xfODBC locks all rows read after the start of a transaction, every row in the selected table will be locked until the transaction is committed or rolled back (or until the connection is terminated) unless the SQL statement that locked the rows includes a restriction that uses a unique index. For example, if you run the following query against the sample database that’s distributed with Connectivity Series, it locks all rows in the orders table, even those whose or_price is not greater than 1.50, because or_price is not a key.

SELECT or_customer FROM orders WHERE or_price > 1.50
    FOR UPDATE OF

The next example, however, locks only rows that meet the restriction clause because the restriction clause uses a unique key (the or_vendor field).

SELECT or_customer FROM orders WHERE or_vendor = 41 
    FOR UPDATE OF

Note the following:

If you use an application that uses transactions for operations that don’t update data (e.g., reporting), make sure the transactions are read-only.
To instruct xfODBC to ignore transactions that are started automatically by an ODBC-enabled application for read-only operations when using privilege-based users, use READ_ONLY. See Ignoring transactions invoked for read-only operations.
xfODBC supports only the following transaction isolation levels: SQL_TXN_READ_UNCOMMITTED and SQL_TXN_READ_COMMITTED.
If a Synergy database is read-only — i.e., if users have read-only access and tables are set to allow read-only access (as recommended) — read/write transactions will cause errors. If this is the case, don’t work around the problem by allowing read/write access to the database. Instead, set the application to use read-only transactions or, if that’s not possible, ask the application vendor to update the application so that it does not use transactions.