INF: Implementation of Searched Updates in the Access Driver (127096)

MORE INFORMATION

The Microsoft Access ODBC driver relies on the Jet engine for data processing. Jet is Microsoft's relational database engine that handles the database processing for Microsoft Access and Microsoft Visual Basic.

A lost update is a typical concurrency problem and can occur when two processes read the same data from the database, and try to update the data based on what they read before either of the updates succeeds. With the Access ODBC driver (Jet), there is no guaranteed way of preventing lost updates. This is primarily because the Jet engine does not support a Read or Intent-to-Update type lock. It only supports Write locks, which conflict only with the other writers.

To understand why a lost update happens, you need to know how the following SQL statement is executed: Even though this is a single SQL statement, it is implemented in roughly two steps:

1. Get a bookmark for all rows in the table that match the WHERE clause. A bookmark is the unique key value of a row.
2. For each bookmarked row, update it with the new values in the SET clause.

Now, consider two instances of an application running simultaneously. There are several combinations of possible executions in the two processor update case. If both processors attempt to do step 2 at the same time, one processor would get a lock conflict error. The lock conflict error tells you that the record is locked by another user on another machine.

If both processors do step 1 at the same time and processor #1 completes (finishes and releases the lock) the update before processor #2 attempts the update, processor #1's update will be lost. No error will be generated. This is a classic lost update problem without transactions.

Transactions can help. A transaction ensures that the write lock obtained by the first processor is held, which would mean that the second processor would notice the lock. However, through the Jet driver, there is no way to ensure that at any given time some other processor is not in the step 1 phase of the update. Hence, even with transactions, a lost update might occur in a multi-machine update scenario.

The same reasoning applies to the combination of the DELETE and UPDATE statements in a multi-user situation. You should note that this problem is encountered very rarely.

Also, using the SQLSetPos, an ODBC API call, to do the UPDATEs or DELETEs is faster and minimizes the chance of any lost updates or deletes.