KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q127096: INF: Implementation of Searched Updates in the Access Driver

Article: Q127096
Product(s): Open Database Connectivity (ODBC)
Version(s): WINDOWS:2.0
Operating System(s): 
Keyword(s): 
Last Modified: 12-JUN-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Open Database Connectivity, version 2.0 
-------------------------------------------------------------------------------

SUMMARY
=======

This article discusses how the Microsoft Access ODBC driver implements the SQL
UPDATE statement and the implications of this in a concurrent multi- user
situation.

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:

  Update <table name> Set <column values> Where
  <searchcondition>

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.

Additional query words: 2.00.2317 concurrency locking VISUAL C++

======================================================================
Keywords          :  
Technology        : kbAudDeveloper kbODBCSearch kbODBC200
Version           : WINDOWS:2.0

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.