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.

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, each operation takes place as soon as its statement is processed, and no operation can be rolled back. With xfODBC, committing or rolling back a transaction merely releases records locked for the transaction. For example, if a transaction includes an insert, the insert will take place as soon as the INSERT statement is processed.

If you don’t use a transaction, xfODBC locks rows only for the time it takes to process the DELETE, INSERT, or UPDATE statement (autocommit). If you do use a transaction, 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

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 

Note the following:

  • We don’t recommend using transactions because of the high overhead they incur.
  • 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.
  • 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.