Empress provides Dirty Read functionality which allows data to be read from an Empress database without any concern with locking and provides the data in its potentially unclean state.
Empress dirty read functionality does not alter in any way or manner the locking mechanisms for normal Empress operations. Normal locking still applies unless dirty read is specified. Dirty read just adds an option making it possible for curious and hasty users to bypass the placing of read locks. Dirty read does not override privileges.
Dirty read can use checksum to verify and validate data that is read from an Empress database and take appropriate action if the data fails these validation tests. This is governed by the ALTER TABLE command to turn on the checksum and MSVALIDATELEVEL variable to control the behavior of dirty read as result of checksum.
If checksum is set to validate data, the checksum of a record is calculated and stored with the record when it is inserted. This is also done every time that record is updated.
If Empress detects a problem with the checksum:
Even if the checksum fails, the option is provided of seeing the data as is or not seeing it at all.
Although dirty read and checksum features are complementary to each other but they can both function independently without each other.
Dirty read is available to all Empress database interfaces:
Dirty read has added the keyword BYPASS_LOCK to the Empress SQL. Users can simply form a query like:
SELECT BYPASS_LOCK * from table_name;
to retrieve records bypass any locks.
There are three modes in which a table can be opened in mx, mf and mr routines:
Dirty read has added two new modes in which a table can be opened:
For example:
mxopen ("db_name", "table_name1", "n");
mxopen ("db_name", "table_name2", "N");
A table that is opened in n mode will retrieve records bypass any locks when the retrieval is performed through mxget/mrget or other corresponding routines. An update operation will fail in this situation.
A table that is opened in N mode actually opens the table in update mode. If an update/insert/delete operation is performed, normal locking behavior applies. However, if a retrieval is performed, mxget/mfget/mrget or other corresponding routines will get data bypass any locks.
In cases of cursor operations in Embedded Static SQL and in Embedded Dynamic SQL, when a query containing the keyword BYPASS_LOCK is specified in the cursor declaration, the opening of the cursor will open the corresponding table in n mode, unless the cursor is opened for update operations. In this later case, the table will be opened in N mode.
If a table is opened explicitly , for example:
EXEC SQL OPEN_TABLE table_name FOR BYPASS_LOCK;
This will open the table in N mode and all subsequent cursor declarations will not reopen the table.
In order to use dirty read in Empress 4GL, the table has to be opened for dirty read mode using the following command:
OPEN table_name BYPASS_LOCK [AS tabinst]
Checksum is set by using ALTER TABLE command. (Please refer to the ALTER TABLE in Empress SQL Reference manual).
If a table has checksum activated, it will be shown when using the DISPLAY ALL command. For example:
DISPLAY loans ALL;
will produce the following output:
*** Table: loans ***
Attributes:
number integer
name character(25,1)
date date(1)
amount dollar(6,1)
System Attributes:
EMPRESS_CHECK_SUM longinteger
Creator: joe
Lock Level: RECORD
Property: varchecksum
Table Type: normal
Table #: 19
Records: 12
Record size: 40
System attribute EMPRESS_CHECK_SUM indicates the table has record checksum.
Property varchecksum indicates the table has variable checksum.
There are three system variables related to the dirty read. These variables are located in $EMPRESSPATH/config/initfile. Their values can be set within the user environment from the command line, in the $EMPRESSPATH/config/initfile file or in an Interactive SQL session, just like all other Empress variables. The following is a description of these system variables:
Table 11-1: Dirty Read System Variables
| Variables | Description |
| MSVALIDATELEVEL | This variable controls the behavior of dirty read in the cases of reading the main table file (i.e., .rel file), the overflow file (i.e., .dtf file) and reading border records. |
| MSVALIDATERETRY | This variable specifies the number of retries Empress will make to read an invalid record. If the record still appears to be invalid after all the retries, Empress will either deliver the invalid record to the user or discard the invalid record depending on the setting of the variable MSVALIDATELEVEL. |
| MSVALIDATESLEEP | This variable specifies the interval in seconds that Empress will sleep between retries to read invalid records. |
The value of the MSVALIDATELEVEL variable is a three digit number of the form XYZ (the default is 440), where each digit signifies the dirty read behavior in the cases of:
Border records form a special case of dirty read. Border records are records in the main table file (i.e., the .rel file) which cross a disk block boundary. For example, if the disk block size is 1024 bytes, there will be no border records if the main record size is exactly 1024 bytes or some even fraction of 1024 bytes such as 512 bytes or 256 bytes etc. In all other cases, there will be border records. Since, no locks are placed, the system can flush one block (containing part of the record) separately from the other adjoining block (containing the rest of the record). Dirty read provides an option to lock border records to avoid this situation. Locking of border records is especially useful when there is no checksum validation.
The values of the digit X and their descriptions are as follow:
| 0 | Discard invalid records and use checksum data validation only for dirty read retrievals. |
| 1 | Deliver invalid records and use checksum data validation only for dirty read retrievals. |
| 2 | Discard invalid records and use checksum data validation only for dirty read as well as normal retrievals. |
| 3 | Deliver invalid records and use checksum data validation only for dirty read as well as normal retrievals. |
| 4 | Deliver invalid records and do not use checksum data validation only for dirty read or normal retrievals. |
The values of the digit Y and their descriptions are as follow:
| 0 | Discard invalid variable-length data and use checksum data validation only for dirty read retrievals. You will get a string containing all spaces for invalid TEXT attributes and a string containing all zeros for invalid BULK attributes. |
| 1 | Deliver invalid variable-length data and use checksum data validation only for dirty read retrievals. |
| 2 | Discard invalid variable-length data and use checksum data validation only for dirty read as well as normal retrievals. You will get a string containing all spaces for invalid TEXT attributes and a string containing all zeros for invalid BULK attributes. |
| 3 | Deliver invalid variable-length data and use checksum data validation only for dirty read as well as normal retrievals. |
| 4 | Deliver invalid variable-length data and do not use checksum data validation only for dirty read or normal retrievals. |
The values of the digit Z and their descriptions are as follow:
| 0 | When this is set to 0, all border records are not locked while a SELECT BYPASS_LOCK is performed. |
| 1 | When this is set to 1, read locks are placed on all border records while a SELECT BYPASS_LOCK is performed. |
MSVALIDATERETRY specifies the number of retries Empress will make to read an invalid record. If the record still appears to be invalid after all the retries, Empress will either deliver the invalid record to the user or discard the invalid record depending on the setting of the variable MSVALIDATELEVEL.
The default value for MSVALIDATERETRY is 10. This means that up to 10 retries will be made on invalid records to check if they pass validity tests.
MSVALIDATESLEEP specifies the interval in seconds that Empress will sleep between retries to read invalid records.
The default value for MSVALIDATESLEEP is 0.09. This means that Empress will sleep for 9 milliseconds between retry attempts.
Dirty read has added a privilege type to the database privileges. The privilege is called BYPASS_LOCK and is a part of the USER privilege group. The BYPASS_LOCK privilege allows user to SELECT BYPASS_LOCK from the table. This feature prevents unauthorized users from reading locked records. Having this privilege can be very useful in transactions when intermediate records are locked to other users for a period of time.
Dirty read and checksum introduced two new error codes/messages:
"bad record retrieved" error occurs when an invalid record is retrieved.
In the case of mr routines, mroperr is set to MRERBADRECORD. If MSVALIDATELEVEL is set to retrieve all records including invalid records, mrget returns 1, otherwise it returns -1. The routine mrerrmsg returns the error message bad record retrieved.
If MSVALIDATELEVEL is set to retrieve all records including invalid records, invalid records are also delivered to the user and the following message appears:
*** Warning *** read error, bad record retrieved
If MSVALIDATELEVEL is set otherwise, only valid records are delivered and the following message appears for each invalid record:
*** Warning *** bad record retrieved, skipping record
In the case of Embedded Static SQL and Embedded Dynamic SQL, SQLCODE is set to -10 and SQLERRMC is set to Error -10: read error bad record retrieved.
Empress 4GL Script Language error_number is set to 2017, the variable got_records is set to 1 and the error message is set to bad record retrieved.
Empress GUI Builder variable sqlcode is set to 2015, sqlresult is set to 1, and sqlmessage is set to bad record retrieved.
"bad variable-length value retrieved" error occurs when an invalid variable-length value is retrieved.
In case of mr routines, mroperr is set to MRERBADDATARETR. The routine mrerrmsg returns the error message bad variable-length value retrieved.
Interactive SQL:
If MSVALIDATELEVEL is set to retrieve invalid variable-length attributes, the invalid data is also delivered to the user and the following message appears:
*** Warning *** read error, bad variable-length value retrieved
If MSVALIDATELEVEL is set otherwise, invalid text attributes are filled with spaces and invalid bulk attributes are filled with zeros. Both are delivered to the user along with the following message:
*** Warning *** read error, bad variable-length value retrieved
In the case of Embedded Static SQL and Embedded Dynamic SQL, SQLCODE is set to -10 and SQLERRMC is set to Error -10: read error bad variable-length value retrieved.
Empress 4GL Script Language error_number is set to 2018, the variable got_records is set to 1, and the error message is set to bad bulk/text value(s) retrieved.
Empress GUI Builder variable sqlcode is set to 2016, sqlresult is set to 1, and sqlmessage is set to bad bulk/text value(s) retrieved.