# CHAPTER 11: Dirty Read and Checksum

## 11.1 Introduction

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:

- `bad variable-length data` message will be printed for variable length attribute (eg. `TEXT`, `NLSTEXT`, `BULK`) overflow file (i.e. `.dtf` file),
- `invalid record` message will be printed for the main table file (i.e. `.rel` file).

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.

## 11.2 Dirty Read User Specifications

Dirty read is available to all Empress database interfaces:

- Empress SQL and Empress Report Writer

  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.

- Empress API

  There are three modes in which a table can be opened in mx, mf and mr routines:

  - `r` for read mode,
  - `u` for update mode, and
  - `d` for deferred mode.

  Dirty read has added two new modes in which a table can be opened:

  - `n` for dirty read mode, and
  - `N` for dirty deferred mode.

  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.

- Empress 4GL

  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]

## 11.3 Checksum User Specifications

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.

## 11.4 Dirty Read System Variables

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.

### 11.4.1 MSVALIDATELEVEL

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:

- digit `X` controls the reading of main table file (i.e., `.rel` file),
- digit `Y` controls the reading of overflow file (i.e., `.dtf` file) and
- digit `Z` controls the reading of border records.

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.

### 11.4.2 MSVALIDATERETRY

`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.

### 11.4.3 MSVALIDATESLEEP

`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.

## 11.5 Dirty Read Privileges

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.

## 11.6 Error Codes and Error Messages

Dirty read and checksum introduced two new error codes/messages:

1. `"bad record retrieved"` error occurs when an invalid record is retrieved.

   - mr Routines:

     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`.

   - Interactive SQL:

     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

   - Embedded SQL:

     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:

     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 Script Language:

     Empress GUI Builder variable `sqlcode` is set to `2015`, `sqlresult` is set to `1`, and `sqlmessage` is set to `bad record retrieved`.

2. `"bad variable-length value retrieved"` error occurs when an invalid variable-length value is retrieved.

   - mr Routines:

     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

   - Embedded SQL:

     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:

     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 Script Language:

     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`.