Optimistic Concurrency Control gets its name from the fact that the program "optimistically" takes the view that no other program will interfere with its updates, so that it can save itself the effort of locking the records it needs to update. The program only needs to worry about contention when it actually happens.
Optimistic Contention Control (sometimes called change-verify contention control) is built into DP4. When you post a set of records to the queue, they form a transaction. Whole transactions are written to the database (applying the specified checks) by db_update in one operation. This means that only complete transactions are written to the database, and so multiple record updates made by different programs remain in step. One of the checks the DP4 manager makes is that none of the records to be updated have already been updated since the user last read the record, or since the transaction began. (These are not necessarily the same thing, of which more later)
Suppose program A and program B both read the same record. They each change the record, and call rec_post() to place the updates in the queue. If program A is the first to call db_update() and assuming the call to db_update() succeeds (returning 0), a subsequent call to db_update() made by program B fails and will return -COMMIT_FAILURE.
Program B will usually avoid the contention error if it reads the problem record after program A has already commited its change to it, even if it had previously read the old state of the record earlier in the transaction. However a contention error is guaranteed if Program B tried to update the record based on its old state even if it subsequently overwrites the update with another call to rec_post().
Internally DP4 contention control works like this:
Each record on a database has a timestamp, set when the record is first created and updated each time the record is updated. All the records updated by a single transaction get the same timestamp - the current database timestamp.
Whenever a record is read from a database its timestamp is returned to the calling program. Additionally each transaction has a timestamp - the database timestamp at the time the transaction begins. A transaction begins when a program first calls a fetch() function that returns data (unsuccessful fetches don't count), or when it calls rec_autoinc(),rec_verify(),rec_post() or rec_kill()and ends when it calls db_update() or an equivalent function.
When a program calls rec_post() the database manager checks whether the update is an insert or a replace, and remembers this. If the update is a replace and the record has been updated since the transaction began, and the timestamp of the posted record does not match the actual timestamp of the record, a commit failure is guaranteed.
When a program calls db_update() the database manager checks the timestamps of the records in the update queue against the current state of the records on the database. An update of an individual record would be allowed if either of the following apply:
The record posted is supposed to be an insert and the record does not exist already.
The record posted is supposed to be a replace and the record exists already, and the timestamp of the current record is either older than the transaction start, or matches the timestamp of the record in the post file, indicating the program "saw" the updated record anyway.
The record posted is supposed to be a deletion, and the record either never existed, or the timestamps match for a replace
The record posted is a verify() and the record exists and the timestamps match as for a replace.
There are two important things to watch out for with DP4 contention control
Because DP4 does not force you to read a record before updating it, and because a transaction only lasts until you call db_update() it is possible to unintentionally overwrite another program's changes to a record with the earlier state of the record . This can happen as follows:
| Program A | Program B |
|---|---|
|
Read Record 1
Change and Post Record 1 Commit changes |
Do nothing |
| Do nothing |
Read Record 1
Change and Post Record 1 Commit changes |
|
Change and Post Record 1
Commit changes |
Do nothing |
Program A did not read record 1 again, because it had already read it. But to the DP4 database manager it looks as though program A simply did not care about the state of record 1, and this type of update is permitted, because it is very useful when importing data. (It would probably be a good idea if there were a flag for db_open() that a program could pass to indicate that it did not want to be allowed to update the database like this, but currently there is not.)
Obviously it is possible to come up with many possible scenarios. The solution in all similar cases is for a program never to rely on the previous state of records it read before its last call to db_update(), but always to read the record again.
If records are read via a DP4 ADC, the timestamp is usually set to 0. This means that updates are only permitted if the record has not been updated since the start of the transaction, because the real database manager never "sees" the fact that a program actually read a more recent state of the record. This can cause "spurious" commit failures to arise, for example when a transaction was begun a long time ago (perhaps through something as innocent as a map record being read from the database by the DP4 terminal manager), whereas the program has in reality been idle for a long time and a transaction is only "really" beginning now. The cure for this type of problem is to insert extra calls to db_update() when a program leaves an idle state.
If you are copying records from one database to another, and you do not zero the timestamp, there is slight possibilty that you might fortuitously avoid a commit failure where you should get one, through a timestamp from one database matching a later timestamp from another database.