How You Can Control Concurrency

No matter how many frames are opened or closed during a transaction, all locks are held until the transaction is completed. It is important, therefore, to design the application and its individual transactions to provide the best possible concurrency control.

Concurrency control refers to the management of shared data so that simultaneous users can access and update the data with little or no wait time while data integrity is maintained.

In general, data access and data integrity exist in an inverse relationship. If an application allows all users to access the data at will, then data integrity is severely threatened. If the application holds locks for long periods of time, for example by running as one continuous transaction, then it is difficult for users to share the data. Most application developers try to find a balance that lets multiple users access the data as quickly as possible yet maintain data integrity.

This section presents an example application with code samples that illustrate the choices inherent in maintaining concurrency control. For instance, the application selects rows from a part table and, after users make their changes, updates the database. The entire application is one transaction, because the commit statement is not executed until the table has been updated. Keeping a single transaction throughout the application promotes data integrity, because the locks taken by the select statement are held until the changes are committed.

Although integrity is maintained by holding a single lock, concurrency is poor because no other user can access the data used by this application until the application has finished executing. For example, if the user running the application decided to go to lunch after loading the table field and before making any changes, the parts table would be inaccessible to any other business that may need it.

The following examples demonstrate two alternative approaches to this application.

on click load_button,
on userevent 'load_data'=
begin
  commit work;   /* Start a new transaction */;
  parttable.Clear();      /* Clear out array */
  i = 1;
  select :parttable[i].partno = partno,
         :parttable[i].partno_save = partno,
         :parttable[i].short_desc = short_desc
  from   part
  begin
   i = i + 1;
  end;
end;

on click update_button =
begin
  i = parttable.FirstRow();
/* No error checking is done for simplicity of
*  example */
while i <= parttable.LastRow() do
  if parttable[i]._rowstate = RS_DELETED then
     repeated delete from part
     where partno = :parttable[i].partno;
  elseif parttable[i]._rowstate = RS_NEW then
     repeated insert into part
      (partno, short_desc)
     values
      (:parttable[i].partno,
       :parttable[i].short_desc);
  elseif parttable[i]._rowstate = RS_CHANGED

/* Does not allow direct update of key value */
   repeated update part
   set short_desc = :parttable[i].short_desc,
       partno = :parttable[i].partno
   where partno = :parttable[i].partno_save
  endif;
  i = i + 1;
endwhile;
commit work;
end;

on click load_button,
on userevent 'load_data' =
begin
   parttable.Clear(); /* Clear out array */
   i = 1;
   select  partno as :parttable[i].partno,
           partno as :parttable[i].partno_save,
           short_desc as
             :parttable[i].short_desc
   from part
   begin
      i = i + 1;
   end;
   commit;  /* Commit added here */
end;

on click update_button =
begin
   i = parttable.FirstRow;
   /* No error checking is done for simplicity
   ** of example */
   while i <= parttable.LastRow do
   ...
   <data integrity checks must be added here
   before rows are written back to the database>
   endwhile;
   commit;
end;

The second example provides better concurrency by dividing the operation into two transactions. This is achieved by placing a commit statement after the select statement and another commit statement after the loop that updates the table. More than one user can access the data simultaneously, because the commit statement after the select statement has released the locks held by the select statement.

However, because the program must ensure that a second user has not changed the data already retrieved into the application's data set, making the application's data inconsistent with the data stored in the database table, you would need to add code to the second transaction to ensure data integrity. For example, adding a timestamp or a version column to the database table lets you verify that the data is the same when you update as when you retrieved.

You could enhance concurrency further by issuing a commit statement after each insert or update statement. However, committing after each database modification is useful only if the set of items changed are not considered to be a true logical unit of work.