ODBC - record locking¶
ODBC - Record locking
The trappable error condition ue (update error) can be generated by the write[kl] and delete commands when an ODBC database that does not support standard (pessimistic) record locking is being used.
If optimistic record locking is in use, records that are read for update are not locked. More than one user can therefore read the same record for update simultaneously. When any of these users attempts to update the record, the driver checks to see if it has been updated since it was last read. The ue trap allows this situation to be trapped in Sculptor and the second user notified that their update operation has failed. Control is passed to the line indicated by label. If this error is not trapped, either by ue or by the all-purpose trap clause traps, Sculptor displays the default error message:
Update refused - record updated by another user
Optimistic locking is the default for the Microsoft Access driver and the native Oracle driver.
If pessimistic record locking is being used, a record that has been read for update by one user is locked, and must be released before a second user is allowed to read it for update.
Pessimistic locking is the default for the Microsoft SQLServer driver and the Microsoft driver for Oracle. Updates are generally faster when optimistic locking is used. In order to force optimistic locking on a database where it is not the default, assign the option flag DBFL_OPTIMISTIC_LOCKING in the flags clause within the database definition:
See Declaring an ODBC database.
SQLServer does not return to the client until the record is available and, because it does not provide an option to return if a record is locked, the riu trap cannot occur. Some databases do return an error when a record is locked, but it is not generally possible to determine the reason for the error. The precise behaviour is dependent on the database being used, but no database so far tested by Sculptor returns the riu error status.
Programmers should be aware that all database products have different features and that even if a database has a particular feature, it will not necessarily be supported by its ODBC driver. It is essential to ensure that a specific database and ODBC driver combination supports all the features that your application needs.
RELATED TOPICS |