Consultor Eletrônico

Status: Verified

GOAL:

What is a dirty read?

GOAL:

Why records read with NO-LOCK may contain inconsistent data.

GOAL:

Example of Dirty Reads from the 4GL.

FACT(s) (Environment):

All Supported Operating Systems
Progress/OpenEdge Product Family

FIX:

A dirty read is any case where a query reads data from the database without lock protection, that is without a guarantee that the data is not changing during the time that the query is running.

One simple scenario of dirty reads is when one client changes a value in a record within a transaction, and a second client reads this value before the original change has been committed or rolled back; in this case it's possible that the first client rolls back the transaction, resulting in the second client having read an invalid value.

Dirty reads occur from an SQL client if a connection to the database is done with the isolation level READ UNCOMMITTED.
From a 4GL client, any time the NO-LOCK clause is used dirty reads can occur.

Using NO-LOCK option implies that any Progress record locking mechanism is bypassed.

While NO-LOCK has better performance over SHARE-LOCK and EXCLUSIVE-LOCK (especially in client/server configurations), the record read from the database may be in an inconsistent state if it is read while another user is modifying it.

The extreme example of the possible level of inconsistency in the data read with the NO-LOCK option is the case of an indexed field not matching the search criteria. The following example demonstrates this:

Run the following code in one Progress session, against a brand new copy of the sports database:

/* firstSession.p */
DO TRANSACTION:
CREATE employee.
ASSIGN employee.empnum = 42
employee.deptcode = "ZZZ".
PAUSE.
END.

While this session is pausing, run the following on another Progress session:

/* secondSession.p */
FIND LAST employee WHERE deptcode = "ZZZ" USE-INDEX DeptCode NO-LOCK.
DISPLAY employee.empnum
employee.deptcode.

What you'll see