|
|
|
|
|
|
|
|
Extreme 1 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. ImplementationFor 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
APPENDIXThe 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"
|
|
Company |
Products |
Services |
Partners |
Media |
|
|
USA: 301-220-1919 Canada
& International: 905-513-8888 |
|||||