There are two key elements of ensuring data integrity, record locking and transaction control.

Locking

The most significant difference between coding applications for single-user environments and multi-user environments (including local area networks) has to do with performing record updates (rewrites) and record deletions.

The basic problem is that to perform a record update or deletion in a multi-user system, you must own a record lock on the record of interest. Locks should be acquired when the user reads the record in preparation for updates, and the programmer should not relinquish the lock until the update is completed. However, one must be careful to ensure that locks are held for the shortest time possible or the performance of the application will be adversely affected.

Two types of locks can be applied to a record:

• A READ lock (also called a "shared" lock) on a record allows an unlimited number of other READ locks on that record (from other threads, etc), but prevents WRITE locks.
• A WRITE lock (also called an "exclusive" lock) prevents any other locks (of either type) on that record.

The FairCom DB API API provides two methods of acquiring these record locks – session-wide record locking (discussed in Starting Session-Wide Locking below) and manual record locking (discussed in the Working with Records / Record Locking section). Session-wide locking is the preferred method, because it is safer:

• With session-wide record locking, you turn on automatic locking, and then any subsequent record reads, writes, or deletions will be automatically locked with the specified lock type.
• With manual record locking, you read a record and then lock it "by hand", by calling a "Lock Record" function, which leaves a window of opportunity for another thread to modify or delete the record after you read it and before you lock it. That is why session-wide locking is the preferred method of acquiring record locks.

Starting session-wide record locking

Calling ctdbLock() passing the appropriate lock mode (CTLOCK_READ, CTLOCK_READ_BLOCK, CTLOCK_WRITE, or CTLOCK_WRITE_BLOCK) initiates session-wide record locking. When calling ctdbLock(), use one of the lock modes listed in Session-Wide Lock Modes.

/* start locking */
if (ctdbLock(hAnyHandle, CTLOCK_WRITE_BLOCK) != CTDBRET_OK)
   printf("Lock failed\n");

After a successful call to ctdbLock(), all records read, written, or deleted by the FairCom DB API API will be automatically locked using the lock mode that was passed to ctdbLock(). The automatic locking of records can be suspended temporarily by calling ctdbLock() with the mode CTLOCK_SUSPEND. Suspending session-wide record locking does not cause any existing record locks to be released, but while locks are suspended, additional record reads, writes, and deletions will not be automatically locked.

It is important to keep in mind that you are suspending the session-wide locking mechanism (which makes it stop auto-acquiring new record locks). You are not suspending the locks on individual records.

/* suspend locking */
if (ctdbLock(hAnyHandle, CTLOCK_SUSPEND) != CTDBRET_OK)
   printf("Suspend lock failed\n");

Suspended session-wide record locking can be resumed by calling ctdbLock() with one of the resume lock modes CTLOCK_RESUME_READ, CTLOCK_RESUME_LOCK_BLOCK, CTLOCK_RESUME_WRITE, and CTLOCK_RESUME_WRITE_BLOCK. This turns auto-locking back on, while keeping all the locks that were in place before the SUSPEND happened.

/* resume locking */
if (ctdbLock(hAnyHandle, CTLOCK_RESUME_WRITE_BLOCK) != CTDBRET_OK)
   printf("Resume lock failed\n");

See Also:

• Session-Wide Lock Modes

Freeing locks

Locks that were automatically acquired using the session-wide record locking mechanism are freed / released by calling ctdbUnlock() or by calling ctdbLock() with mode CTLOCK_FREE. This also turns off automatic, session-wide locking, so future record reads, writes, and deletes will not automatically acquire locks.

Note that in some cases, releasing locks this way can also release some locks that were manually acquired using ctdbLockRecord(). See ctdbUnlock() for details.

If freeing locks inside an active transaction, session-wide record locking will be suspended, rather than turned off, and the existing record locks will actually be freed/released when the transaction terminates, via a call to ctdbCommit() or ctdbAbort(). Note that these two transaction-ending functions also clear the current session-wide locking mode (turn off session-wide record locking).

/* free automatically-acquired record locks and turn off session-wide record locking */
if (ctdbUnlock(hAnyHandle) != CTDBRET_OK)
   printf("Free lock failed\n");

Freeing locks associated with a table

If you wish, you can release only the record locks associated with a particular table by calling ctdbUnlockTable(). Only the locks held for records of that table are released, and all other record locks are kept. This function releases locks automatically acquired via the session-wide locking mechanism, and locks manually acquired via calls to the ctdbLockRecord() function. Note that this function does not affect the current session-wide locking mode.

/* free locks for a table */
if (ctdbUnlockTable(hTable) != CTDBRET_OK)
   printf("Free table locks failed\n");

If freeing locks associated with a table inside an active transaction, the locks (both automatically and manually acquired) of updated records will only be actually freed when the transaction terminates via a call to ctdbCommit() or ctdbAbort().

FairCom Server enhanced locking control for files opened multiple times in the same connection

Starting in V10.3, FairCom DB supports opening the same file multiple times in the same connection assigning a different file number to each file or, in FairCom DB API, a different file handle. This can be useful in situations where you want to allow the same file to be opened twice by the same thread with different locking attributes applied to each thread.

Each of these sibling files is referred to as a "co-file." For example, if the file customer.dat is opened in the same connection using file numbers 5 and 10, then we say that file 5 is a co-file of file 10, and vice versa.

In this case there are considerations about how locks interact within the same connection when operating using different co-files. For example, if a write lock is acquired on a record R using file number 5 within the same connection, what is the behavior of trying to acquire a lock on R using co-file number 10?

In this example, before this enhancement, FairCom Server behaved as follows:

The lock on R issued with co-file number 10 succeed and is considered a "secondary lock", while the lock acquired first (using file number 5) is considered "primary."

The difference in the locks manifests itself during calls to unlock the record: If the primary lock is unlocked first, then the primary lock and all the corresponding locks on co-files are removed. But if a secondary lock is unlocked before the primary lock is unlocked, then only the secondary user lock is removed; and the primary lock is maintained.

Any other connection saw the record locked until the primary lock was released.

This previous behavior has been maintained and it is the system-level default behavior.

It is now possible to configure the behavior choosing among 4 different options:

• NODIFUSR: The default as described above.
• DIFUSR: Locks on co-files are considered as acquired from a different connection, so the lock on R issued with co-file number 10 will fail.
• SAMUSR_M: Locks on record R on co-files are considered as the same lock acquired on the same file, so lock on R issued with co-file number 10 succeeds. As soon as the lock is released in one of the co-files that successfully requested the lock, the lock is released. Therefore, before acquiring the lock on R using file number 10, the lock can be released only using file number 5, but after acquiring the lock on R using file number 10, the lock can be released either by using file number 5 or 10.
• SAMUSR_1: Locks on record R on co-files are considered as the same lock acquired on the same file, so lock on R issued with co-file number 10 succeeds. As soon as the lock is released in one of the co-files (whether or not the lock was requested using the co-file) the lock is released. Therefore, even before acquiring the lock on R using file number 10 the lock can be released either by using file number 5 or 10.

Recursive locks are not supported for co-files. An attempt to open a co-file when recursive locks are pending on the underlying file will fail with the error MUOP_RCR (998). An attempt to issue a lock on a co-file with the ctLK_RECR bit set in the lock mode will fail with the error MLOK_ERR (999).

Read locks behave in a manner consistent with write locks. The notable issues are:

1. With DIFUSR, read locks can be issued for different co-files; and unlocking one co-file's read lock does not remove the read lock from any other co-files that requested the read lock.
2. With DIFUSR, a read lock on a co-file cannot be promoted to a write lock if other co-files have read locks; a non-blocking write lock will fail with DLOK_ERR (42) and a blocking write lock will fail with DEAD_ERR (86).
3. With SAMUSR_*, read locks can be issued for different co-files, and unlocking one co-file read lock unlocks all the co-file read locks.
4. With SAMUSR_*, read locks can be promoted to write locks as long as no other threads have also acquired read locks.
5. With SAMUSR_1, a read lock on a co-file can be unlocked using another co-file's file number even if no lock has been issued using the other co-file number.

The system-level default can be controlled by using one of the following configuration keywords which sets the behavior accordingly to their names.

• COMPATIBILITY MULTIOPN_DIFUSR
• COMPATIBILITY MULTIOPN_SAMUSR_M
• COMPATIBILITY MULTIOPN_SAMUSR_1

A connection can override the system-level default for all open instances of a file by calling:

PUTHDR(datno, mode, ctMULTIOPNhdr)

Where mode is one of the following:

• ctMULTIOPNnodifusr
• ctMULTIOPNdifusr
• ctMULTIOPNsamusr_M
• ctMULTIOPNsamusr_1

If no PUTHDR call is made, the system-level default is used for that connection's instances of the file. When a file is opened, if that connection already has the file open, the newly opened file inherits the MULTIOPN setting of the already-open file instance. An attempt to change the setting so that one instance of the file would be inconsistent with the others will fail with error MOFL_ERR. A file's MULTIOPN state can only be changed if it is the first open instance of the file and it has no pending locks.

Transactions

There are two major aspects to transaction processing, atomicity and automatic recovery. These are related yet different aspects of transaction processing, and not all products supply both. FairCom DB API provides a set of functions and file modes that cover both aspects of transaction processing.

Atomicity

Often, when updating a table, you perform several functions in a group. For instance, when creating an invoice, you update several tables: the account balance in the customer file, the invoice file, an invoice detail file, inventory records, and others. It is important that all of these actions take place to keep the files synchronized. If some of the actions take place, but not all, your files may be out of sync, and it can be difficult to correct the problem later. If one action cannot take place, it would be best to not let any take place. We call this atomicity. The FairCom DB API API provides functions that provide this feature. You can mark a set of operations so that none will take place unless they can all take place. The API goes beyond this, allowing you to create "savepoints" where you can partially back out a group of operations, and "roll back" transactions to a given point, so that you can restore your data back to a state that it was in sometime in the past.

Automatic Recovery

Once you establish full transaction processing by creating tables using the CTCREATE_TRNLOG mode, you can take advantage of the automatic recovery feature. Atomicity will generally prevent problems of files being partially updated. However, there are still situations where a system crash can cause data to be lost. Once you have signaled the end of a transaction, there is still a "window of vulnerability" while the application is actually committing the transaction updates to disk. In addition, for speed considerations some systems buffer the data files and indexes, so that updates may not be flushed to disk immediately. If the system crashes, and one of these problems exists, the recovery logic detects it. If you set up the system for automatic file recovery, the recovery logic automatically resets the table back to the last, most complete, state that it can. If any transaction sets have not been completed, or "committed", they will not affect the table.

Error During Automatic Recovery

An error 14, FCRP_ERR, indicates that FairCom Server detected that files appear corrupt at open. This occurs if files have been updated but not properly closed. They were not processed by automatic recovery so they are in an unknown (inconsistent) state.

If your transaction logs are corrupted, preventing automatic recovery from occurring, you can either:

• Restore from a backup and reapply changes if available (e.g., from application log or forward roll of good transaction logs you have saved).

or

• Rebuild the files, which will clear the error 14 but will still leave the files in a possibly inconsistent state. In this situation the files will not be guaranteed to be consistent as of any point in time; they can contain a mixture of old/new data, and the data files may not match the index files, due to caching.

Creating tables for transaction processing

Only tables created with the create modes CTCREATE_PREIMG and CTCREATE_TRNLOG will participate in a transaction. This means that the ctdbBegin(), ctdbCommit(), and ctdbAbort() functions provide transaction atomicity when used with records from these two types of tables. This also means that these three functions do NOT provide atomicity when used with records from other types of tables, such as CTCREATE_NORMAL tables. Record operations (and locks) on NORMAL tables are handled as if there was no transaction running. For CTCREATE_PREIMG and CTCREATE_TRNLOG tables to provide the promised protections, all operations that add, modify, and delete records in these tables must be done inside a transaction (between calls to ctdbBegin() and ctdbCommit() / ctdbAbort()). This is enforced by FairCom DB API; attempting to modify records outside of a transaction for these tables will generate an error.

Tables created with CTCREATE_PREIMG mode will participate in a transaction, and will benefit from transaction atomicity, but will not have automatic recovery, because the transaction log files needed for automatic recovery will not be generated. This saves disk space and gives speed gains, at the cost of more difficult recovery in case of a crash.

Tables created with CTCREATE_TRNLOG have all the positive attributes of CTCREATE_PREIMG but will also generate the transaction logs necessary for automatic recovery.

Starting a transaction

Using our example from above, you don’t want to have the transaction group involve more than one invoice. You also don’t want it to involve less than a whole invoice.

Record locks are held on updated records for the duration of the transaction, so you don’t want to make the transaction group too large or it will consume the system resources and cause delays. On the other hand, you may not want to make the transaction group too small or the effect of grouping actions is lost.

ctdbBegin() starts a new transaction. You will need to decide on logical groups of file updates that can be delimited as transactions.

/* start a new transaction */
if (ctdbBegin(hAnyHandle) != CTDBRET_OK)
{
   printf("Begin transaction failed\n");
}

Terminating a transaction

When all update operations have been completed, terminate a transaction by calling ctdbCommit() to commit all changes.

/* commit transaction */
if (ctdbCommit(hAnyHandle) != CTDBRET_OK)
{
   printf("Commit transaction failed\n");
}

Call ctdbAbort() to terminate the transaction and abort all changes done since the start of the transaction.

/* abort transaction */
if (ctdbAbort(hAnyHandle) != CTDBRET_OK)
{
   printf("Abort transaction failed\n");
}

Save Points

There are times when you want to abort only a portion of a transaction. You may be processing several optional paths of a program, going down one branch, then backing out and trying another branch. It may be possible that you don't want any of the updates to occur until you are completely finished, but you want the flexibility to back out part of the updates. Another possibility would be if you have run into some form of update error, such as an add record failing due to a duplicate key. You would want to back up to a prior point, correct the problem, and continue. The FairCom DB API API allows you to implement this by using savepoints.

A savepoint is a temporary spot in the transaction that you may want to roll back to without having to abort the entire transaction. During a transaction, when you want to put a place mark in the process, issue a ctdbSetSavePoint() call. This does not commit any of the updates. The function returns a savepoint number, which you should keep track of. You can make as many ctdbSetSavePoint() calls as you wish during a transaction, and each time you will be given a savepoint number, which is unique to the current transaction.

When you decide that you want to roll back to a savepoint previously saved by a call to ctdbSetSavePoint(), issue a ctdbRestoreSavePoint() call, passing in the desired savepoint number. This returns your data to the state it was at the point you issued the specified ctdbSetSavePoint() call, without aborting the entire transaction.