Consultor Eletrônico

Status: Verified

FACT(s) (Environment):

Progress 9.1D
MERANT 3.60 32-BIT Progress SQL-92

SYMPTOM(s):

Visual Basic 6.0 application uses ADO and ODBC to talk to Progress database

Invoking the ADO RecordSet objects Update method to update a single record takes 30+ seconds to execute

Large number of exclusive locks are obtained against the table being updated

CAUSE:

The SELECT statement which is used to fetch the record uses the correct index to resolve the query, however, the index is not defined as being unique so when the update is invoked an index scan is performed which in turn causes each record to be exclusively locked until the correct record is found.

FIX:

The resolution for this problem is to use the ADO Connection objects Execute method to send a SQL UPDATE statement directly to the server.  This UPDATE statement must use a WHERE clause that references the ROWID to update.

In order to obtain the ROWID the SELECT statement must select an explicit list of columns along with the ROWID function.  

Sample code is shown below:

Dim MyConnection As Connection
Dim MyRecordSet As Recordset
Dim MyUpdateStatement As String
Dim MySelectStatement As String

Set MyConnection = New ADODB.Connection

MyConnection.Open "DSN=SomeDB;UID=SomeUser;PWD=SomePassword;"

Set MyRecordSet = New ADODB.Recordset

MySelectStatement = "SELECT ROWID, Field1 FROM PUB.SomeTable WHERE SomeField = SomeValue"

MyRecordSet.Open MySelectStatement, MyConnection, adOpenDynamic, adLockReadOnly

If Not MyRecordSet.EOF Then
MyUpdateStatement = "UPDATE PUB.SomeTable "
MyUpdateStatement = MyUpdateStatement + "SET Field1 = 'SomeValue' "
MyUpdateStatement = MyUpdateStatement + " WHERE ROWID = '" + MyRecordSet("Rowid").Value + "'"

MyConnection.Execute (MyUpdateStatement)

MyRecordSet.Close
MyConnection.Close
Set MyRecordSet = Nothing
Set MyConnection = Nothing
Else
MsgBox "Record not found", vbCritical
End If