Concurrency and Consistency

While your program is modifying data, another program may also be reading or modifying the same data. To prevent errors, database servers use a system of locks.

When another program requests the data, the database server either makes the program wait or turns it back with an error. BDL provides a combination of statements to control the effect that locks have on your data access:

SET LOCK MODE TO {WAIT [n]| NOT WAIT }

This defines the timeout for lock acquisition for the current connection. The timeout period can be specified in seconds (n). If no period is specified, the timeout is infinite. If the LOCK MODE is set to NOT WAIT, an exception is returned immediately if a lock cannot be acquired.

Important: This feature is not supported by all databases. When possible, the database driver sets the corresponding connection parameter to define the timeout. If the database server does not support setting the lock timeout parameter, the runtime system generates an exception.

SET ISOLATION LEVEL TO { DIRTY READ
                       | COMMITTED READ
                       | CURSOR STABILITY
                       | REPEATABLE READ }

This defines the ISOLATION LEVEL for the current connection. When possible, the database driver executes the native SQL statement that corresponds to the specified isolation level.

For portable database programming, the following is recommended:

Transactions must be enabled in your database.
The ISOLATION LEVEL must be at least COMMITTED READ. On most database servers, this is usually the default isolation level and need not be changed.
The LOCK MODE must be set to WAIT or WAIT time period, if this is supported by your database server.

See Database transactions in the Genero Business Development Language User Guide for a more complete discussion.

The SQL adaptation guides provide detailed information about the behavior of specific database servers.