Transactions and the DataFlex ODBC Driver

The DataFlex ODBC Driver allows DataFlex programs to access data through ODBC. Open Database Connectivity (ODBC) is a widely accepted application programming interface (API) for database access. ODBC is designed to enable an application to access different database management systems (DBMS’s) with the same source code. An application calls ODBC functions which are implemented in database specific modules called drivers. ODBC uses SQL to access data.

ODBC is an API specification. The API is independent from any DBMS or operating system. It is important to understand that ODBC is designed to expose database capabilities, not supplement them. Thus, application writers should not expect that using ODBC will suddenly transform a simple database into a fully featured relational database engine. Nor are driver writers expected to implement functionality not found in the underlying database.

A variety of database management systems can be accessed through ODBC. These include enterprise database systems such as Oracle, Sybase, DB2, SQL Server, flat file systems as dBase, Paradox and even non-database systems as Excel, XML and ASCII.

ODBC supports transactions if the database connected to supports transactions. The same can be said about locking. How a DataFlex application behaves with transactions and locking when using the DataFlex ODBC Driver depends on the database used and the ODBC driver used to connect to that database.

Transactions and Locking

There are ODBC drivers for non-database environments. These generally do not support transactions and locking at all. The discussion below is limited to databases only.

If the database supports the Read Uncommitted Isolation Level, the ODBC Driver will use that Isolation Level. If Read Uncommitted is not supported the driver does not set up an Isolation Level and will use the default level for the data source.

If transactions are supported they will be used automatically by the DataFlex ODBC Driver. In order to determine if a database supports transactions see its documentation. In general enterprise databases support transactions with rollback and roll forward features. Flat file databases provide the full range from no support to full rollback / forward support. If there is no support for transactions, the DataFlex ODBC Driver cannot use it.

Most databases support row locking. The DataFlex ODBC Driver assumes the environment to be a row-locking environment. If the granularity happens to be greater, page or table, this will also work. Assuming the smallest lock granularity assures that locking (if supported) works in all cases. In general databases use two approaches to locking: Optimistic and pessimistic locking.

When optimistic locking is used records are not really locked. All transactions have access to the record at all times. When the transaction requests a lock, a copy of the contents of the record is made. When, at a later time in the transaction, the record is updated, the current record in the table will be compared to the copy made at the time of the lock. If the two versions do not match, an error is declared and the update is not executed. The idea is that the chance of two transactions trying to modify the same row simultaneously is very small. Applications connecting to a database that uses optimistic locking should handle the optimistic lock error condition. A way to automatically handle this error is to add it to the list of deadlock/lock timeout errors. When using pessimistic locking, records are actually locked when the transaction requests a lock. Other transactions that try to lock the record will have to wait for the lock to be released.

The 5.0 version of the CLI based drivers simplified the update/delete logic considerably and only uses the "where RowId = current RowId" method.

In versions 4.1 and earlier, the way the DataFlex ODBC Driver handles locks depends on the database that is used. At login time a number of attributes of the database are queried that determine the locking method. The method used will eventually select a so-called cursor to be used when accessing the database the cursor choice logic essentially is the following:

If DBMS supports a dynamic cursor, use it

Else If DBMS supports positioned updates in a forward only cursor, use it

Else If DBMS supports positioned updates in a keyset driven cursor, use it

Else If DBMS supports SQLSetPos updates in a forward only cursor, use it

Else If DBMS supports SQLSetPos updates in a keyset driven cursor, use it

Else use forward only cursor

SQL is a set oriented language. A find (select) operation will return a set of records instead of just one like in record oriented environments. To traverse these sets a mechanism known as database cursors is used. A cursor allows a non set-oriented environment to obtain information from a set one row at a time.

Dynamic Cursors

Dynamic cursors are the most advanced type of cursor. Unfortunately very few databases support dynamic cursors. If a database supports dynamic cursors, the driver will use that type of cursor. Dynamic cursors support record locking in all find modes.

The moment at which a lock is applied is different from the moment the native DataFlex database acquires a lock. In native DataFlex a lock is acquired when the lock command is issued. In ODBC using dynamic cursors locks are acquired as data is read in a locked state.

Positioned Updates

Positioned Updated are no longer use in driver revisions 5 or higher, so this section applies to revisions 4.1 and earlier.

A positioned update is an update at the current position of an active cursor. In order to be able to use this form of updating a special form of the select statement must be used. The select statement must contain a so-called update clause. The update clause will instruct the database to lock the record in question. Most enterprise databases support positioned updates through one or more cursor types.

The update clause will only be generated when a Find Eq by Recnum operation is done. The driver will therefore only place Update locks on rows that are found through a Find Eq by Recnum operation after the database has been locked. A Reread generates a Find Eq by Recnum after a lock so rows found by a Reread will also be locked. All rows that are found in some other way will be locked when they are updated or deleted.

The moment at which a lock is applied is different from the moment the native DataFlex database acquires a lock. In native DataFlex a lock is acquired when the lock command is issued. In ODBC locks are acquired as data is read in a locked state or as it gets updated or deleted.

When accessing ODBC make sure rows are locked, always use either Reread or a Find Eq by Recnum in a locked state. Data Sets and Data Dictionaries will use Reread when updating data. Programs using the DDO/DSO objects will therefore correctly lock all data that is updated. Procedural programs may require code changes for all locks to be applied.

A further difference between DataFlex and ODBC is the amount of data that is updated in an update operation. DataFlex will read and write entire records. ODBC (actually SQL) on the other hand is capable of updating one or more columns in a row. If for example one wants to update the name of a customer. DataFlex will read the entire record, modify the name and write the entire record back to the data file. ODBC should only write the modified customer name to the database.

The ability to update specified columns will reduce the amount of procedural code that needs adjusting. If the program overwrites columns with values not based on previous values of columns in the row, there is no need to make sure the row is locked. If the program uses previous values of columns in the row to calculate new values of columns in the row, you must make sure the row is locked. In that case a Reread or Find Eq by Recnum must be added to the code if it is not already present. Below an example of a procedure that does not need to be adjusted and one that must be adjusted.

Procedure NoNeedToAdjust

  Clear SomeTable

  Begin_Transaction

  Repeat

    Find Gt SomeTable By SomeIndex

    If (Found) Begin

      Move "SomeValue" To SomeTable.SomeColumn

      Saverecord SomeTable

    End

  Until (Not(Found))

  End_Transaction

End_Procedure

 

Procedure MustBeAdjusted

  Clear SomeTable

  Begin_Transaction

  Repeat

    Find Gt SomeTable By SomeIndex

    If (Found) Begin

      Move (SomeTable.SomeColumn * 1.16) To ;

            SomeTable.SomeColumn

      Saverecord SomeTable

    End

  Until (Not(Found))

  End_Transaction

End_Procedure

The procedure that must be adjusted can be adjusted the following way:

Procedure MustBeAdjusted

  Clear SomeTable

  Begin_Transaction

  Repeat

    Find Gt SomeTable By SomeIndex

    If (Found) Begin

      Reread

      Move (SomeTable.SomeColumn * 1.16) To ;

            SomeTable.SomeColumn

      Saverecord SomeTable

      Unlock

    End

  Until (Not(Found))

  End_Transaction

End_Procedure

Data Access has been recommending the use of Reread when updating records for quite some time. It is expected that the number of programs that need adjusting in this area is limited. If you want both procedures to apply update locks on the records involved, you should adjust both procedures.

SQLSetPos Updates

ODBC provides an alternative way to update records from SQL through an API know as SQLSetPos. This API can be used to update and delete rows in a table. Even if SQLSetPos is supported, there are a variety of details in support for the API. SQLSetPos can be used to lock a row exclusive, update a row, delete a row or refresh a row. Not all of the possibilities have to be supported by a database. If the database supports updating rows through SQLSetPos, the driver will use SQLSetPos.

When SQLSetPos is supported with support for exclusive locks this will be called directly after each find in a locked state. This way records that are found in a locked state will be locked as they are found.

If the exclusive lock is not supported the driver will perform a so-called “dummy update” to ensure that the row is locked. Directly after a row has been found, the row will be updated. The dummy update column will be set to the value that has just been found. By default the dummy update column is the record identity column. This column is least likely to change. If however a different column should be used for the dummy update logic, this can be set up through the intermediate file keyword Dummy_Update_Column. Another reason to use an alternative dummy update column is when the record identity column cannot be updated. Some database support system assigned columns like auto-increment columns. In general it is considered an error to try to update such a column. After the dummy update has been done, a re-find is done to ensure the latest version is in the record buffer.

If SQLSetPos is supported all records that are read in a locked state will be locked as they are read. Depending on the support this may be as simple as placing a lock or as complicated as doing a dummy update and re-finding the record.

No Update Support

If the database reports no support for dynamic cursors, positioned updates and SQLSetPos, the driver will use a forward only cursor. It is not defined how locking works in this case. In general these type of environments will lock rows as they are updated, deleted or inserted. Locks are applied at the moment of the DataFlex Save, SaveRecord or Delete command. No locks are applied when finding in a locked state.

Summary on Locking

There are a lot of options and unknown factors for ODBC in general. A database might support a particular feature or it may not. It would be quite confusing to keep track of all the different possibilities and implementations. Luckily most programmers only use one or two databases to connect to through ODBC.

Basically there are three possible ways locking can work:

1. No locks are set during find operations. If a record is updates, it will be locked at the moment of the update (save).

2. Only find equal operations set a lock. Records found in a different way will be locked at the moment of the update (save).

3. All find operations set a lock. 

The following table lists the tested environments with it locking type support

Back End

Lock Behavior

MySQL

2

Oracle

3

PostgreSQL

2

Microsoft Access

3

Deadlocks

In most databases that can be accessed through ODBC, deadlocks can occur. Whether the database detects a deadlock depends on the database in use. Check the database documentation whether deadlocks can occur and if so, if detection is supported. If a deadlock is detected, the transaction will be aborted and the deadlock detection error is sent to the victim transaction. If the database supports a lock timeout, the same happens when lock timeout occurs. There is no way DataFlex ODBC Driver can know up front what the error numbers of the database for the two events are. Errors generated by databases will be reported under number 12289. There is no way to know if an error was a deadlock, timeout or some other error that can be generated by the database in use.

The DataFlex ODBC Driver allows the user to setup a translation list of deadlock and lock timeout errors or SQL States. This list can be setup in the database specific configuration file using the Native_LockError and/or the Lock_State keywords. Errors defined in these two lists will be translated to DFERR_LOCK_TIMEOUT (4106) thus enabling the automatic retry mechanism.

If the translation of deadlock and lock timeout errors is switched off, the DataFlex ODBC Driver will report both these errors in the same way, since they result in the same behavior for the transaction involved. The error is passed to DataFlex as CLIERR_DEADLOCK_OR_TIMEOUT (12303). When not using the lock error translation, it is the programmer’s responsibility to handle deadlock or lock timeouts. The only way to detect if Deadlock occurred (in this case) is by parsing the error text of the latest error. The ODBC Driver formats it errors in a special way: <SQLState> (<Native error number>)-<Error text>. If the SQLState that is set when deadlock or timeout occurs is known a DataFlex program could test for this state. SQLState is a five-character string. Not all databases pass native error numbers through ODBC, if it does the DataFlex programmer could also check the native error number.

See Also

Transactions