Introduction
Mimer SQL includes a very effective Transaction Management system. Basically, it builds on a method called Optimistic Concurrency Control (OCC).
Description
Mimer SQL’s pioneering work makes it the first relation database management system (RDBMS) to use the OCC method for transaction management. However, several Object Orientated Databases, which were more recently developed, have incorporated OCC within their designs to gain the performance advantages inherent in this technological approach.
Through the Group Commit concept, which is applied in Mimer SQL, the number of I/Os needed to secure committed transactions to the disk is reduced to a minimum. The actual updates to the database are performed in the background, allowing the originating application to continue.
The ROLLBACK statement is supported but, because nothing is written to the actual database during the transaction build-up phase, this only involves a re-initialization of structures used by the transaction control system.
Another significant transaction feature in Mimer SQL is the concept of Read-Only transactions, which can be used for transactions that only perform read operations to the database. When performing a Read-Only transaction, the application will always see a consistent view of the database. Since consistency is guaranteed during a Read-Only transaction, no transaction check is needed and internal structures used to perform transaction checks (i.e. the Read Set) are not needed, and for this reason no Read Set is established for a Read-Only transaction. This has significantly positive effects on performance for these transactions. This means that a Read-Only transaction always succeeds, unaffected by changes performed by other transactions. Also, a Read-Only transaction never disturbs any other transactions going on in the system. For example, a complicated long-running query can execute in parallel with OLTP (on-line transaction processing) transactions.
Function
Though optimistic methods were originally developed for transaction management, the concept is equally applicable for the more general problems of sharing resources and data. The methods have been incorporated into several recently developed operating systems, and many of the recent hardware architectures provide instructions to support and simplify the implementation of these methods.
Optimistic Concurrency Control does not involve any locking of rows as such, and therefore cannot involve any deadlocks. Instead, it works by dividing the transaction into phases.
- The Build-up phase commences the start of the transaction. When a transaction is started, a consistent view of the database is frozen based on the state after the last committed transaction. This means that the application will see this consistent view of the database during the entire transaction. This is accomplished by the use of an internal Transaction Cache, which contains information about all ongoing transactions in the system. The application ‘sees’ the database through the Transaction Cache. During the Build-up phase, the system also builds up a Read Set documenting the accesses to the database, and a Write Set of changes to be made, but does not apply any of these changes to the database. The Build-up phase ends with the calling of the COMMIT (or ROLLBACK) command by the application.
- The Prepare phase involves using the Read Set and the Transaction Cache to detect access conflicts with other transactions. A conflict occurs when another transaction alters data in a way that would alter the contents of the Read Set for the transaction that is checked. Other transactions that were committed during the checked transaction’s Build-up phase or during this check phase can cause a conflict. If a transaction conflict is detected, the checked transaction is aborted. No rollback is necessary, as no changes have been made to the database. An error code is returned to the application, which can then take appropriate action. Often this will be to retry the transaction without the user being aware of the conflict.
- In the Commit phase, if no conflicts are detected, the operations in the Write Set for the transaction are moved to another structure, called the Commit Set, which is to be secured on disk. All operations for one transaction are stored on the same page in the Commit Set (if the transaction is not very large). Before the operations in the Commit Set are secured on permanent storage, the system checks if there are any other committed transactions that can be stored on the same page in the Commit Set. After this, all transactions stored on the Commit Set page are written to disk (to the transaction databank TRANSDB) in one single I/O operation. This behavior is called a Group Commit, which means that several transactions are secured simultaneously. When the Commit Set has been secured on disk (in one I/O operation), the application is informed of the success of the COMMIT command and can resume its operations.
- During the Apply phase, the changes are applied to the database, i.e. the databanks and the shadows are updated. The background threads in the database server carry out this phase. Even though the changes are applied in the background, the transaction changes are visible to all applications through the Transaction Cache. Once this phase is finished the transaction is fully complete. If there is any kind of hardware failure that means that Mimer SQL is unable to complete this phase, it is automatically restarted as soon as the cause of the failure is corrected.
Most other DBMSs offer pessimistic concurrency control or locking. This type of concurrency control protects a user’s reads and updates by acquiring locks on rows (or possibly database pages, depending on the implementation), this leads to applications becoming ‘contention bound’ with performance limited by other transactions. These locks may force other users to wait if they try to access the locked items. The user that ‘owns’ the locks will usually complete their work, committing the transaction and thereby freeing the locks so that the waiting users can compete to attempt to acquire the locks.
For a locking system the order rows are accessed in the database by different users affects how likely it is that a deadlock will occur. With optimistic concurrency, the order rows are accessed has no effect on the transaction subsystem. This allows the SQL optimizer to choose and also later change join orders freely without affecting the running system. Applications are, of course, also free to access data in the database in any order without any effects to the transaction system. One effect of this is that it is easier to introduce new applications in already stable runtime environments with a system based on optimistic concurrency control.
Techniques
Optimistic Concurrency Control offers a number of distinct advantages including:
- Complicated locking overhead is completely eliminated. Scalability is affected in locking systems as many simultaneous users cause locking graph traversal costs to escalate.
- Deadlocks cannot occur, so the performance overheads of deadlock detection are avoided as well as the need for possible system administrator intervention to resolve them.
- Programming is simplified as transaction aborts only occur at the Commit command whereas deadlocks can occur at any point during a transaction. Also, it is not necessary for the programmer to take any action to avoid the potentially catastrophic effects of deadlocks, such as carrying out database accesses in a particular order. This is particularly important as potential deadlock situations are rarely detected in testing, and are only discovered when systems go live.
- Data cannot be left inaccessible to other users as a result of a user taking a break or being excessively slow in responding to prompts. Locking systems leave locks set in these circumstances denying other users access to the data.
- Data cannot be left inaccessible as a result of client processes failing or losing their connections to the server.
- Delays caused by locking systems being overly cautious are avoided. This can arise as a result of larger than necessary lock granularity, but there are also several other circumstances when locking causes unnecessary delays even when using fine granularity locking.
- Removes the problems associated with the use of ad-hoc tools.
Benefits
For a programmer, it is good to know that it is not possible to create a deadlock situation.
