Transactions and Locking Overview
RaimaDB implements pessimistic locking using update transactions, read transactions and snapshots.
An update transaction is used to observe and update data. A read transaction and snapshots are used to observe data. These operations can be accommodated with table IDs to specify which tables are affected. This ensures the logical consistency of data with proper isolation by allowing multiple, related observations or updates to be grouped together where updates are done atomically. See ACID Properties of Transactions for more details.
Nesting Transactions
The last parameter to rdm_dbStartUpdate(), rdm_dbStartRead() and rdm_dbStartSnapshot() can return a handle for the operation. The handle can be used with any of the transaction end functions that accept the handle to end that operation. Those functions include rdm_transEnd() and rdm_transEndRollback(). Other similar functions are rdm_transRollback() and rdm_transFree().
Any time we start a transaction when there is already an ongoing transaction started with rdm_dbStartUpdate() or rdm_dbStartRead(), the second transaction is nested inside the first one. The nesting can be arbitrarily deep (except at one point we may run out of memory).
A snapshot (started with rdm_dbStartSnapshot()) cannot be nested. It means no transactions can be started when there is an ongoing snapshot, and no snapshots can be started when there is an ongoing read or update transaction.
If there is no corresponding call to rdm_transEnd() or rdm_transEndRollback() with the actual handle, a call to rdm_dbEnd() or rdm_dbEndRollback() may be the corresponding call. rdm_dbEnd() and rdm_dbEndRollback() will end all transactions started with rdm_dbStartUpdate(), rdm_dbStartRead() or rdm_dbStartSnapshot() and not already ended by a previous call to rdm_dbEnd(), rdm_dbEndRollback(), rdm_transEnd() or rdm_transEndRollback(). When a database is closed or an application terminates, an implicit call to rdm_dbEndRollback() may be issued.
Root Transactions
Transactions that are not nested inside another transaction are called root transactions. There are two types of root transactions:
- Root read transaction
- Root update transaction
The root read transaction complies with the definition provided above. It is a root transaction started with rdm_dbStartRead().
A root update transaction covers a slightly more general case. It is a transaction started with rdm_dbStartUpdate() that is not nested inside any other update transactions.
This means in addition to a strict root transaction that can be a root read transaction or a root update transaction, we may also have a combination of them where a root read transaction has a root update transaction nested inside it. The nesting can be direct or indirect, where one or more read transactions can be nested between the root read transaction and the root update transaction.
It follows from this that a root read transaction can have one or more root update transactions nested inside it in a parallel manner. Naturally, each of these root update transactions cannot be nested inside each other. An update transaction nested inside another update transaction becomes a nested update transaction, not a root transaction.
Application Semantics for Nested Transactions
Nesting of transactions mainly serves three purposes:
- Locks can be taken and released incrementally
- Savepoints can be created where some changes can be rolled back
- Read locks can be kept after changes have been committed to the database
Tables to lock are specified for each transaction started. Nesting transactions allows additional locks to be acquired. When a transaction ends, additional locks that were acquired at its start will be released. If access for the tables whose locks have been released is needed again, a new transaction has to be started to request the additional locks again.
In addition to acquiring locks, a nested update transaction can be used as a "savepoint." A savepoint is the beginning of a set of changes that can be rolled back. You roll back to a savepoint by calling rdm_transEndRollback() or rdm_transRollback(), using the handle acquired with the rdm_dbStartUpdate().
You can also end a savepoint by calling rdm_transEnd(). The changes made within this nested update transaction will be finalized (unless another update transaction outside of it is rolled back) when the root update transaction that includes the savepoint has been committed.
Ending a transaction will also end all transactions inside it. Any handles for nested transactions will be invalidated. Calling rdm_transRollback() or rdm_transEndRollback() will logically roll back all the transactions nested inside the update transaction we roll back. The difference between rdm_transEndRollback() and rdm_transRollback() is that rdm_transRollback() will keep the transaction intact while rdm_transEndRollback() will invalidate the transaction handle. These functions cannot be called for read transactions.
rdm_transFree() can also be called to just invalidate the transaction handle. Once a handle is invalidated, you will not be able to roll back to the savepoint represented by that handle.
When a root update transaction is nested within a root read transaction, any read locks acquired before the start of the root update transaction will be kept after the root update transaction has been ended.
Deadlocks
Care should be taken when you request locks incrementally. If the timeout is set to infinite, deadlocks may occur. Therefore, to avoid deadlocks, it is important to understand how locking affects other users. These rules will be outlined in the next section.
Multi-user Concurrency Semantics
As mentioned above, locks may be requested and freed incrementally. However, other database users may observe that locks are held for a longer duration than the actual request from the application. Users connecting to the same database are mainly interested in how locks are managed on the TFS.
For root update transactions, any locks requested by the root update transaction, as well as any of the nested transactions inside the root update transaction, are held until the root update transaction has ended. This semantics is required to ensure ACID properties of transactions.
Semantics for the root read transactions is similar. Any locks requested by the root read transaction and any of the nested transactions inside the root read transaction are held as read locks until the root read transaction has ended. This means that when a root update is nested inside a root read transaction, the write locks held by the root update transaction are downgraded to read locks when the root update transaction is ended. Read locks are held as-is.
The semantics for root read transactions has been chosen for simplicity and performance. It avoids acquiring and releasing the same locks repeatedly from the TFS. This may be important for SQL, especially when stored procedures are executed. A stored procedure may request and free the same locks repeatedly as the control flow goes between different SELECT statements inside the procedure.
This approach provides a performance benefit for core RaimaDB applications as well, where they always acquire locks just before a read or an update and free them right after. Such applications may end up acquiring and releasing the same locks repeatedly, thereby degrading performance. Simply adding one root transaction that doesn't request any locks will effectively keep all the locks on the TFS until the root transaction has been ended. This will also ensure a consistent view of the data.
Another aspect that follows from what we have covered so far is that, as mentioned above, a nested call to rdm_dbStartUpdate() where we already have a call to rdm_dbStartUpdate() will not immediately apply its changes to the database when the corresponding rdm_transEnd() is called. Once all the transactions started with rdm_dbStartUpdate() are ended, will the changes be sent to the TFS, applied to the database and made visible to other users.