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 Pervasive PSQL OLE DB provider, the ADO parameter that enables pessimistic cursors,
adLockPessimistic
, was not supported.
Now you can use this parameter, along with some Pervasive 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:
1
Client A opens a recordset
2
Client A reads row one (a)
3
Client A changes field 1 (b)
4
Client A changes field 2
5
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
Pervasive’s 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