How to Improve Concurency in Empress mr Applications.

Introduction

When designing a C application with Empress mr Routines, one of the objectives facing application developers is to provide concurrent access to data. Once an application program is developed, there is a good chance that it will be invoked by a large number of users or processes at the same time. It will result in multiple processes competing for the same data. While pursuing maximum concurrency developers must be also mindful of database intergrity. Applications performing inserts, updates, and deletes on a database have the potential to destroy database integrity, and therefore must be strictly regulated. The mechanism that protects database integrity is known as Locking (Manual A5: Empress C/C++ Kernel Level Interface - mr Routines, CHAPTER 12: Empress Locking). In Empress RDBMS access to the database is arbitrated by the database engine, which governs applications' access to data, while maintaining its integrity. Effective utilization of Empress Locking mechanism for maximum concurency is the main topic of this technical note.

Implementation

For the sake of simplicity we will analyze a program which scans a database table record by record, once it finds the required record, the program updates it.

The following is a pseudo code for this operation:


    LOOP
          GET (fetch) record from a table
          IF desired record
                UPDATE record
    END-LOOP

1. The first consideration is the locking level defined on a table. Empress RDBMS can impose a number of table-level lock categories: TABLE, GROUP, RECORD, NULL, going in this order in terms of alleviating concurrency. NULL level locking provides for the maximum speed and least concurrency. NULL lock level is not an option for those applications that write to a database since it introduces high risk to data integrity. The RECORD level lock is a default lock level and it provides a good setting for concurrent applications.

2. Another aspect important for application developer is the open mode of a table. If a table is opened in "deferred" ('d') mode read locks are initially placed on a table. The table is not prevented from an update operation. When update occurs, the read lock which was initially placed on the table will be upgraded to an update lock. Once the operation is completed, this will be downgraded to a read lock. While records are fetched, read lock will prevent other processes from updating the same record but not from reading them.

3. If an application developer wishes to introduce a greater concurrency, then "deferred dirty read" ('N') mode can be used. When an update is performed, an update lock will be placed. However, when fetching records, the process will bypass Empress locking mechanisms which will yield the best performance for records' fetching. The down-side of the this approach is the possibility that the required fetched record (the candidate for update) could be modified by another process before the time the update lock is placed on the record. 4. The remedy for this situation is to open the table twice, once in dirty read mode ('n') and the second time in update mode ('u') and obtain two table descriptors. The two table descriptors will give a developer the ability to use two different locking scenarios: one in case of fetching records and the other in case of re-fetching a record and updating it. Thus when the required record is fetched, it will be locked and fetched again. If the record hasn't been modified by another process, it will be updated.

The following is a pseudo code for this operation:


    LOOP
          GET (fetch) record
          IF desired record
                LOCK record
                RE-GET (re-fetch) record
                IF record NOT modified
                      UPDATE record
                UNLOCK record
    END-LOOP

APPENDIX

The following script can be executed with EMPRESS RDBMS installed on the system in order to acquire the above described functionality. The example application fetches an "amount" value from "loans" table in loansDB database. When it finds a value of 300 it replaces with a new value of 275. Error checking has been omitted in a number of calls for the sake of clarity. The example application can serve as a template for building more complex application scenarios.

#!/bin/sh cat > ./upd.c <<EOM #include <mscc.h> #define DATABASE "loansDB" msmain () { addr loans_tabdesc; addr loans_tabdesc2; addr loans_recdesc; addr loans_recdesc2; addr new_recdesc; addr amount_attrdesc; addr amount_attrdesc2; addr l_retrieve_desc; char* amount_value; char* amount_value2; long recptr; loans_tabdesc = mropen (DATABASE, "loans", 'n'); loans_tabdesc2 = mropen (DATABASE, "loans", 'u'); loans_recdesc = mrmkrec (loans_tabdesc); amount_attrdesc = mrngeta (loans_tabdesc, "amount"); amount_value = mrspv (amount_attrdesc); loans_recdesc2 = mrmkrec (loans_tabdesc2); new_recdesc = mrmkrec (loans_tabdesc2); amount_attrdesc2 = mrngeta (loans_tabdesc2, "amount"); amount_value2 = mrspv (amount_attrdesc2); printf ("Loans\n"); l_retrieve_desc = mrgetbegin (ADDRNIL, loans_recdesc, ADDRNIL); while (mrget (l_retrieve_desc) == 1) { mrcopyv (loans_recdesc, amount_attrdesc, amount_value); printf ("Amount value: %s\n", amount_value); if (!strcmp(amount_value, "300.00")) { /* obtain a pointer to a record */ recptr = mrgetptr (loans_recdesc); /* obtain a record given a pointer to it. mrgetrec will lock the record; it must be explicitly unlocked again by calling mrulrec */ if (mrgetrec (loans_recdesc2, recptr) == 1) { mrcopyr (new_recdesc, loans_recdesc2); mrcopyv (loans_recdesc2, amount_attrdesc2, amount_value2); if (!strcmp(amount_value, amount_value2)) { if (mrputvs (new_recdesc, amount_attrdesc2, "275")) mrput (new_recdesc, loans_recdesc2); else fprintf (stderr, "Update not done \n"); } mrulrec (loans_recdesc2); } } } mrgetend (l_retrieve_desc); mrfree (amount_value); mrfree (amount_value2); mrfrrec (loans_recdesc); mrfrrec (loans_recdesc2); mrfrrec (new_recdesc); mrclose (loans_tabdesc2); mrclose (loans_tabdesc); } EOM empcc -o upd upd.c empmkdb loansDB empbatch loansDB <<EOM CREATE loans (name CHAR (25, 1), date DATE (1), amount DOLLAR (6, 3)); INSERT INTO TABLE loans VALUES ( "Mosca " , "2 February 1992 " , "150.00"); INSERT INTO TABLE loans VALUES ( "Jones " , "7 February 1992 " , "33.95"); INSERT INTO TABLE loans VALUES ( "Kilroy " , "16 February 1992" , "250.00"); INSERT INTO TABLE loans VALUES ( "Wladislaw" , "27 February 1992" , "55.00"); INSERT INTO TABLE loans VALUES ( "Jones " , "3 April 1992 " , "25.00"); INSERT INTO TABLE loans VALUES ( "Mosca " , "4 May 1992 " , "200.00"); INSERT INTO TABLE loans VALUES ( "Wladislaw" , "12 May 1992 " , "25.00"); INSERT INTO TABLE loans VALUES ( "Peterson " , "6 June 1992 " , "50.00"); INSERT INTO TABLE loans VALUES ( "Wladislaw" , "25 June 1992 " , "75.00"); INSERT INTO TABLE loans VALUES ( "Jones " , "12 August 1992 " , "300.00"); INSERT INTO TABLE loans VALUES ( "Jones " , "25 December 1992" , "100.00"); EOM empcmd loansDB "select * from loans" ./upd empcmd loansDB "select * from loans"