Pessimistic Cursors

You use a pessimistic cursor when it is very important to your application that updates always succeed, even if this may increase the likelihood of other clients encountering record locks.

In past versions of the PSQL OLE DB provider, the ADO parameter that enables pessimistic cursors, adLockPessimistic, was not supported.

Now you can use this parameter, along with some PSQL connection string enhancements, to create applications with pessimistic cursors.

OLE DB Specification and Pessimistic Cursors

The OLE DB specification defines a pessimistic cursor as one that ensures that changes made to a single row from the most recent fetch will not fail because of a concurrency violation. After opening a recordset, some time may pass between that open and a deferred update. The use of pessimistic cursors is one way to mitigate concurrency problems that can arise because of this time gap.

The definition for pessimistic cursors in the OLE DB specification is intentionally ambiguous, as each provider may implement row-level locking differently. The definition alludes to two common implementations:

•

(a) lock the row on the read

•

(b) lock the row at the start of the update.

For example, consider the following sequence of events:

Client A opens a recordset

Client A reads row one (a)

Client A changes field 1 (b)

Client A changes field 2

Client A updates the row

From a developer standpoint, the difference between the two is that algorithm (a) is using a read lock, and algorithm (b) is using an update lock. The following table summarizes the behavioral differences between the two algorithms:

Table 1

Read vs. Update Locks

Lock Type

Behavior

Read Lock

Lock row on each read

Unlock the row on the first Move method

Lock the next row

Update Lock

Lock the row when data is changed

Unlock the row when the Update method is called

A read lock gives the developer confidence that if the row was read, it can be updated without concern of a concurrency error. Unfortunately, it also locks the row. An update lock only locks a row if data is changed. However, it allows a larger time frame in which a concurrency error could occur.

For more information on the architecture of this change, see OLE DB Provider Architecture.

Syntax for Pessimistic Cursors

The PSQL implementation of pessimistic cursors allow for choice with regards to the behavior you want. The following shows the parameter related to pessimistic cursors in the connection string:

•

Read Locks or Update Locks (Pessimistic Read Lock=true/false)

You use this connection string option in conjunction with specifying a pessimistic cursor with the ADO parameter adLockPessimistic during the Open method.

Specifying Read Locks vs. Update Locks

Using Pessimistic Read Lock=true in the connection string will perform a read lock, as shown in the following sample connection string:

"Provider=PervasiveOLEDB;Data Source=Demodata;Pessimistic Read Lock=true"

Change the value for Pessimistic Read Lock to false if you want an update lock.

Summary of Pessimistic Cursor Behavior

The following table summarizes the effects of using the pessimistic cursor options.

Table 2

Pessimistic Lock Options and Behavior Matrix

Pessimistic Read Lock=

Behavior

True

Lock record on read

Return error if already locked

False

Lock record on update

Return error if already locked

Sample Visual Basic Code with Pessimistic Cursor

The following Visual Basic sample snippet opens the Course table of the DEMODATA sample database using a pessimistic cursor with read locks.

Public myRecordSet as ADODB.Recordset

myRecordSet.CursorLocation = adUseServer

myConnString = "Provider=PervasiveOLEDB;Data Source=DEMODATA;Pessimistic Read Lock=true"

myRecordSet.Open "Course",myConnString, adOpenDynamic, adLockPessimistic, adCmdTableDirect

myRecordSet.MoveFirst ‘first record is locked