|
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 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:
|
