|
Extreme 1
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 Database 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"
|
