Database inconsistencies can arise when two or more processes attempt to update or delete the same record or table. Read inconsistencies can arise when changes made during a transaction are visible to other processes before the transaction has been completed for example, the transaction might subsequently be abandoned.
To avoid such inconsistencies, BaanERP supports the following locking mechanisms: record/page locking, table locking, and application locking.
To ensure that only one process at a time can modify a record, the database driver locks the record when the rst process attempts to modify it. Other processes cannot then update or delete the record until the lock has been released. However, they can still read the record. While one process is updating a table, it is important that other processes retain read consistency on the table. Read consistency means that a process does not see uncommitted changes. Updates become visible to other processes only when the transaction has been successfully committed. Some database systems do not support read consistency, and so a dirty read is possible. A dirty read occurs when one process updates a record and another process views the record before the modications have been committed. If the modications are rolled back, the information read by the second process becomes invalid. Some databases, such as SYBASE and Microsoft SQL Server 6.5, use page locking instead of record locking. That is, they lock an entire page in a table instead of an individual record. A page is a predened block size (that is, number of bytes). The number of records locked partly depends on the record size.
Locking a record for longer than required can result in unnecessarily long waiting times. The use of delayed locks solves this problem to a great extent. A delayed lock is applied to a record immediately before changes are committed to the database and not earlier. When the record is initially read, it is temporarily stored. Immediately before updating the database, the system reads the value of the record again, this time placing a lock on it. If the record is already locked, the system goes back to the retry point and retries the transaction. If the record is not locked, the system compares the content of the record from the rst read with the content from the second read. If changes have been made to the record by another process since the rst read, the error ROWCHANGED is returned and the transaction is undone. If no changes have occurred, the update is committed to the database. You place a delayed lock by adding the keyword FOR UPDATE to the SELECT statement.
SELECT tccom001.* FROM tccom001 FOR UPDATE
tccom001.dsca = “….”
A retry point is a position in a program script to which the program returns if an error occurs within a transaction. The transaction is then retried. There are a number of situations where retry points are useful:
During the time that a delayed lock is applied to a record/page, an error can occur that causes the system to execute an abort.transaction(). In such cases, all that BaanERP can do is inform the program that the transaction has been aborted. However, if retry points are used, the system can automatically retry the transaction without the user being aware of this. Some database systems generate an abort.transaction() when a dirty record is read (that is, a record that has been changed but not yet committed). An abort.transaction() may also be generated when two or more processes simulta-
neously attempt to change, delete, or add the same record. In all these situations, BaanERP Tools can conceal the problem from the user by using retry points. It simply retries the transaction. If there is no retry point, the transaction is aborted and the session is terminated.
In BaanERP, updates are buered, so the success or failure of an update is not known until commit.transaction() is called. If an update fails, the commit of the transaction also fails, and the entire transaction must be repeated. If retry points
are used, the system automatically retries the transaction. Retry points can also resolve potential deadlock problems. If, for example, the system is unable to lock a record, it rolls the transaction back and tries again. It is vital that retry points are included in all update programs. The retry point for a transaction must be placed at the start of a transaction. The following example illustrates how you program retry points:
db.retry.point() | set retry point
if db.retry.hit() then
…… | code to execute when the system
| goes back to retry point
…… | initialization of retry point
The function db.retry.hit() returns 0 when the retry point is generated that is, the rst time the code is executed. It returns a value unequal to 0 when the system returns to the retry point through the database layer. When the system goes back to a retry point, it clears the internal stack of functions, local variables, and so on that were called during the transaction. The program continues from where the retry point was generated. The value of global variables is NOT reset. When a commit fails, the database automatically returns to its state at the start of the transaction; the program is set back to the last retry point. It is vital, therefore, that the retry point is situated at the start of the transaction. The db.retry.hit() call must follow the db.retry.point() call. Do not place it in the SQL loop itself as this makes the code very untransparent. When a retry point is placed within a transaction, the system produces a message and terminates the session.
BaanERP provides a table locking mechanism, which enables you to lock all the records in a specied table. A table lock prevents other processes from modifying or locking records in the table but not from reading them. This is useful when a particular transaction would otherwise require a large number of record locks. You use the db.lock.table() function to apply a table lock.
An application lock prevents other applications and users from reading and/or modifying an applications data during critical operations. It is not part of a transaction and so is not automatically removed when a transaction is committed. Instead, an application lock is removed when the application ends or when appl.detete() is called.