<< Click to Display Table of Contents >> Navigation:  Application development > Database driver characteristics > SQL drivers > SQL record-locking behavior

SQL servers automatically and transparently lock data as required, although different SQL servers vary in the type of lock used, and how granular the lock is. For example, some servers provide individual record locks, while others can only lock a group, or page, of records. Also, some servers provide automatic record versioning or database snapshots so that other copies of data being modified can be read by clients instead of waiting for a modification to finish.

In addition to the automatic locking that SQL servers provide, SQL drivers provide a particular type of record locking called optimistic locking. Optimistic locking allows a client to make changes to a local copy of the record without the performance and concurrency penalty incurred by asking the server for a lock over the modification duration. When the client modifications are finished, the current SQL server record is first checked to make sure no changes have occurred to the record, then the modifications are completed. The operation is said to be optimistic because it assumes that no other client will change the record, but then makes sure of that as the final change is sent to the SQL server.

If the record was changed, an optimistic lock failure occurs. The client is notified that the requested operation cannot be performed because someone else has changed the data. The client can then inspect the new data and decide whether or not to make changes at that time.

Because server data cached on the client can immediately become out of date at the server, SQL drivers always perform optimistic locking. This protects the client against inadvertently changing data that has never been inspected.

Keyed Updates

Keyed updates give you more control over optimistic record locking for improved performance. You can control which columns are placed in the WHERE clause of an UPDATE or DELETE statement generated by calls to DbiModifyRecord or DbiDeleteRecord.

You can set and retrieve the SQL-specific cursor property curUPDLOCKMODE by using DbiGetProp and DbiSetProp. This property is valid for all SQL Link drivers and the ODBC Socket.

The following enumeration defines the options:

typedef enum

   {

    updWHEREALL,

    updWHEREKEYCHG,

    updWHEREKEY

   } UPDLockMode;

 

updWHEREALL
All fields (except BLOBs) are placed in the WHERE clause of the update or delete statement for DbiModifyRecord or DbiDeleteRecord. This is the default when a cursor is returned. The behavior is identical to current "optimistic record locking" behavior.

updWHEREKEY
If a unique index exists, only those fields in the key are placed in the WHERE clause of the update or delete statement for DbiModifyRecord and DbiDeleteRecord. The key that is used is based on the active index. If the active index is a unique index, then it will be used. Otherwise the driver will pick the "best" unique index. (Note: For Oracle, it will pick the special column, ROWID). If there is no unique index, then all fields are placed in the WHERE clause and the behavior is identical to updWHEREALL.

updWHEREKEYCHG
Similar to updWHEREKEY except that changed fields (as well as indexed fields) are placed in the WHERE clause.