ODBC - file locks and transations¶
ODBC - file locks and transactions
The Sculptor keyed file interface is low level. This give a high level of control to the programmer and is very fast but at the expense of more programming effort to achieve some tasks. The SQL interface is high level and transaction based. Typically, a transaction retrieves or updates many rows in one command. This is easy to program but provides less control; also, each transaction has an overhead, making the approach slow when only one record needs to be retrieved or updated.
Suppose that an application wishes to guarantee that a user can update a set of records as a single transaction. It needs to ensure that no other user can update one of these records until the transaction has been completed. Sculptor provides a file write lock, which guarantees that no other user can obtain a record for update until the lock has been released. This achieves the objective, but has the disadvantage that the entire file is locked for a time, even though only a subset of the records may need to be updated. If kfserver is being used, Sculptor provides the alternative of using the begin() and commit() functions. Sculptor then acquires and holds locks on the records as the transaction reads them, but the programmer must write code to deal with a “record in use” status, deciding whether to wait for the lock to be released or to abort the transaction by calling rollback(). In both cases, other users can read the records in an unlocked state.
Many SQL databases do not provide an explicit file lock. Instead, the programmer informs the database that the updates are a single transaction, and the database decides how to handle this. It might lock the entire table, or it might allow multiple transactions from different users to proceed concurrently in the hope that they will not overlap. If a conflict does occur, the database may arbitrarily choose a victim and abort that transaction. A SQL database may provide some options here, but normally there is no low level control. It may or may not be possible to prevent other users from reading the records that are being updated, and it may or may not be possible to determine whether the other users see these records in the state they were before or after being updated while the transaction is still in progress. These SQL features can be very useful, but the point to bear in mind is that behaviour will differ not only between Sculptor keyed files and a SQL database, but also between different SQL databases.
RELATED TOPICS