Tutorial 25: Concurrency Control Mechanism

This tutorial explains how to benefit from concurrency control mechanism in Formspider. You will discover how to implement a concurrency check mechanism as well as how and when to use it.

In this tutorial you will create a sample application through which users can edit employees information. This application is considered as a multi-user system where simultaneous access and alteration of an employee data is possible and should be supported. Therefore these simultaneous transactions should be coordinated while preserving data integrity (by preventing problems like lost updates, dirty reads.. etc.) by using a concurrency control mechanism.

Formspider’s Concurrency Control Approach

Formspider provides concurrency control through a datasource definition column named “Concurrency Column” which should be specified and maintained by the developer. This column can be considered as the version number or as the last edition time of a single row.

Every time a change made to a row is committed, Formspider automatically checks the value of the “concurrency column” to make sure that the row in the database has not been updated since the application session had queried it. Formspider uses the primary key defined for the datasource definition to find the row. Therefore, in order to use the concurrency control and be able to specify a “concurrency column” you must first specify a primary key in your datasource definition. If the row was changed (if a more recent version of this row exists), Formspider raises an exception and warns the developer about the concurrency violation.

Concurrency Control Locking Strategy

While providing concurrency control, Formpider uses the optimistic locking strategy. In other words, it allows users to edit any data and detects collisions when they occur, while the changes are being posted to the database.

This approach is preferred since it’s the more common form of locking in web applications. Since database connections are shared among user sessions, it is not possible to have a continuous database lock on a row while the user is interacting with an application. In addition to this, this approach also prevents users from locking rows longer than they should.

Implementing Concurrency Control Mechanism

Open Formspider IDE and click “New Application” under the “File” menu. The “New Application” dialog will show up. Enter “concurrency” as the name of the application and “HR” as its Datasource Schema name.  If you are using Formspider Online, you do not need to enter database schema name since your account is already configured to use your online database. Leave other fields empty and click “OK”. This creates the “concurrency” application with a default mainframe (called “mainFrame”) and a default panel (called “mainPanel”).

As the first step you will alter the “employees” table in order to make it suitable for Formspider concurrency mechanism. First, you will add a new column named “version_number” which will be used as “concurrency column” of your datasource definition. To achieve this, logon to your datasource schema and execute the following statement;

alter table employees add version_number number

You will use a database sequence for generating value of the “version_number” column. For this purpose create a sequence named “version_seq” using the following command;

create sequence version_seq
increment by 1
start with 1
minvalue 1
maxvalue 999999999999999999999999999
nocycle
order
cache 20

Next, you should update the “employees” table and populate “version_number” value for the rows which are currently existing in this table;

update employees
set version_number = version_seq.nextval

Then, add a not null constraint to the “version_number” column;

alter table employees modify (version_number not null)

Finally, you should maintain the “version_number” value and update it when the row is updated. To achieve this, you will create a before update trigger on “employees” table working for each row and updating their respective “version_number” values;

create or replace trigger employees_bu
 before
  update
 on employees
referencing new as new old as old
 for each row
begin
 select version_seq.nextval into :new.version_number from dual;
end;

Consequently, the “employees” table is now suitable for Formspider concurrency control mechanism. As the next step, you will create necessary datasource definition which will be used on listing employees and editing employees information.

Creating the Model

Expand the Datasource Definitions accordion, select the Datasource Definitions node and click the “+” button. Alternatively, you may right click the “Datasource Definitions” node and select “New” from the pop up menu. The “New Datasource Definition” Dialog will show up. Ensure that the “Table” radio button is selected and pick “EMPLOYEES” table from the combo box. Note that the combo box shows a list of all tables in your Datasource Schema “HR”. Also ensure that the “Create Datasource” checkbox is ticked. Please note that the Formspider IDE automatically assigned the name “EMPLOYEES” to your datasource definition and picked the “EMPLOYEE_ID” as its Primary Key.

For providing a concurrency control mechanism for this datasource definition pick “VERSION_NUMBER” from the “Concurrency Column” combo box, this selection assures that the column named “VERSION_NUMBER” will be used as your “Concurrency Column”.

Filling properties of the “EMPLOYEES” datasource definition

Click “OK” to save and close the “New Datasource Definition” dialog. This will create the “EMPLOYEES” datasource definition and the “EMPLOYEES1″ datasource. You will use the “EMPLOYEES1” datasource for listing and editing employees information.

Creating the User Interface

Double click the “mainPanel” under the “Panels” node of the “Containers” navigation tree to open it in the editor. Your panel will contain a grid displaying “First Name”, “Last Name”, and “Email” information of the employees using “EMPLOYEES1” datasource that you have created recently. Also you will add a button labeled “Edit” which will open a dialog through which users can change and save information of the currently selected employee. Your “mainPanel” XML will look like;

<panel>
  <tableLayout cellSpacing="5">
    <row height="20">
      <cell>
        <button label="Edit"/>
      </cell>
    </row>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

As mentioned above, you will show a dialog displaying employee information when “Edit” button is clicked. To achieve this you will create a new panel where employee details will be displayed and include this panel in a dialog that you will create successively.

Expand the “Containers” accordion panel, select the “Panels” node in the navigation tree and click the “+” button to create a new panel. Alternatively, you may right click the “Panels” node and select the “New” menu item from the pop-up menu. The “New Panel” dialog will show up. Enter “employeeDetailPanel” as the name of the panel. Click “OK” to create and open the panel in the editor.

Creating “employeeDetailPanel”

Through the “employeeDetailPanel” you will be able to change “First Name”, “Last Name”, “Email”, “Salary”, “Hire Date” and “Phone Number” information of the currently selected employee. Also you will be able to commit the changes you have provided by pressing a button labeled “Save”. This panel will contain an additional button labeled “Refresh Details” which will refresh the information belonging to currently selected employee by retrieving the most recent copy of the selected row from the “employees” table. When a concurrency violation occurs, users will use this button to retrieve the latest version of the respective employee row. Your panel XML should be as the following;

<panel>
  <tableLayout cellSpacing="5">
    <!-- dummy row for adjusting layout-->
    <row height="0">
      <cell width="100"/>
      <cell columnSpan="2"/>
      <cell width="50"/>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="First Name"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="FIRST_NAME" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Last Name"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="LAST_NAME" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Email"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="EMAIL" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Salary"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="salary" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Hire Date"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <dateField column="hire_date" dataSource="EMPLOYEES1" dateFormat="DD/MM/YYYY"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Phone Number"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="phone_number" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell/>
      <cell hAlign="Center">
        <button label="Refresh Details"/>
      </cell>
      <cell hAlign="Center">
        <button label="Save"/>
      </cell>
      <cell/>
    </row>
  </tableLayout>
</panel>

Next, you will create the dialog which will contain the “employeeDetailPanel”. Remember that this dialog will be shown when “Edit” button existing in your “mainPanel” is pressed.

Expand “Windows” node in the “Containers Navigation Tree” and select the “Dialogs” node. Click the “+” button to create a new dialog. Alternatively, you may right click the “Dialogs” node and select “New” from the pop up menu. The “New Dialog” dialog will show up. Enter “employeeDetailDialog” as the name of the dialog. Specify a “Width″ of “365″, a “Height″ of “210″, enter “Employee Details” as “Title”, uncheck “Close” check box and click “OK”. Formspider IDE creates the dialog and opens it in the editor.

Creating “employeeDetailDialog”

Include the “employeeDetailPanel” to the “employeeDetailDialog”;

<dialog title="Employee Details" width="365" height="210" close="N">
  <borderLayout>
    <cell docking="Center">
      <include panelName="employeeDetailPanel"/>
    </cell>
  </borderLayout>
</dialog>

As mentioned earlier, you have to show the “employeeDetailDialog” when “Edit” button is pressed . To achieve this, you will create a Formspider action which will be fired when “Edit” button is pressed.

In your datasource schema, create a package called “concurrency_pkg” and open your newly created “concurrency_pkg” package in your favorite PL/SQL Editor. Add a procedure named “edit” and ensure that the procedure is exposed in the package specification. This procedure uses api_dialog.setVisible API to display the “employeeDetailDialog”.

procedure edit is
begin
  api_dialog.setVisible('employeeDetailDialog','Y');
end;

In Formspider IDE expand the “Actions” accordion, select the “Actions” node, click the “+” button to create a new action. Alternatively you may right click the “Actions” node and select “New” from the pop-up menu. The “New Action” dialog will show up. Enter “edit” as the action name and “concurrency_pkg.edit” as the procedure. Click “OK” to save your action.

Creating “edit” action

Open the “mainPanel” in the editor. Add a buttonPress event to the “Edit” button triggering the “edit” action, the panel XML should look like;

<panel>
  <tableLayout cellSpacing="5">
    <row height="20">
      <cell>
        <button label="Edit">
          <events>
            <buttonPress action="edit"/>
          </events>
        </button>
      </cell>
    </row>
    <row>
      <cell hAlign="Full" vAlign="Full">
        <grid dataSource="EMPLOYEES1">
          <column headerLabel="First Name">
            <textLabel column="FIRST_NAME"/>
          </column>
          <column headerLabel="Last Name">
            <textLabel column="LAST_NAME"/>
          </column>
          <column headerLabel="Email">
            <textLabel column="EMAIL"/>
          </column>
        </grid>
      </cell>
    </row>
  </tableLayout>
</panel>

Next, you will create a procedure named “refresh” which will be used when “Refresh Details” button existing in “employeeDetailPanel” is pressed. Open “concurrency_pkg” package and create the “refresh” procedure, this procedure uses api_datasource.refreshCurrentRow
API to retrieve the most recent copy of the currently selected row of “EMPLOYEES1” datasource.

procedure refresh is
begin
  api_datasource.refreshCurrentRow('EMPLOYEES1');
end;

Create a new action and enter “refresh” as the action name and “concurrency_pkg.refresh” as the procedure. Click “OK” to save your action.

Open the “employeeDetailPanel” in the editor. Add a buttonPress event to the “Refresh Details” button triggering the “refresh” action, the panel XML will look like;

<panel>
  <tableLayout cellSpacing="5">
    <!-- dummy row for adjusting layout-->
    <row height="0">
      <cell width="100"/>
      <cell columnSpan="2"/>
      <cell width="50"/>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="First Name"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="FIRST_NAME" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Last Name"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="LAST_NAME" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Email"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="EMAIL" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Salary"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="salary" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Hire Date"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <dateField column="hire_date" dataSource="EMPLOYEES1" dateFormat="DD/MM/YYYY"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Phone Number"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="phone_number" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell/>
      <cell hAlign="Center">
        <button label="Refresh Details">
          <events>
            <buttonPress action="refresh"/>
          </events>
        </button>
      </cell>
      <cell hAlign="Center">
        <button label="Save"/>
      </cell>
      <cell/>
    </row>
  </tableLayout>
</panel>

As the final step, you will commit the changes when “Save” button is pressed. If any information belonging to the currently edited employee was already altered by another user, Formspider will raise an “e_concurrencyVialotion” exception. You will catch this exception and show a warning message when necessary.

procedure save is
begin
  api_application.docommit;
  api_dialog.setVisible('employeeDetailDialog','N');
exception
  -- catch concurrency violation exception
  when api_exception.e_concurrencyVialotion then
    api_application.showPopupMessage('This employee has already been edited by another user. Please click "Refresh Details" to access the most recent employee details.');
end;

Create a new action and enter “save” as the action name and “concurrency_pkg.save” as the procedure. Click “OK” to save your action.

Open the “employeeDetailPanel” in the editor. Add a buttonPress event to the “Save” button triggering the “save” action, the panel XML will look like;

<panel>
  <tableLayout cellSpacing="5">
    <!-- dummy row for adjusting layout-->
    <row height="0">
      <cell width="100"/>
      <cell columnSpan="2"/>
      <cell width="50"/>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="First Name"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="FIRST_NAME" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Last Name"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="LAST_NAME" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Email"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="EMAIL" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Salary"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="SALARY" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Hire Date"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <dateField dateFormat="DD/MM/YYYY" column="HIRE_DATE" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell hAlign="Full" vAlign="Full">
        <textLabel label="Phone Number"/>
      </cell>
      <cell columnSpan="2" hAlign="Full" vAlign="Full">
        <textField column="PHONE_NUMBER" dataSource="EMPLOYEES1"/>
      </cell>
    </row>
    <row height="20">
      <cell/>
      <cell hAlign="Center">
        <button label="Refresh Details">
          <events>
            <buttonPress action="refresh"/>
          </events>
        </button>
      </cell>
      <cell hAlign="Center">
        <button label="Save">
          <events>
            <buttonPress action="save"/>
          </events>
        </button>
      </cell>
      <cell/>
    </row>
  </tableLayout>
</panel>

Experimenting with the Concurrency Control Mechanism

Press “Run as a Web Application” button twice for running two instances of the application. In the first instance click to the first row from the list and press “Edit” button. Notice that the “employeeDetailDialog” is opened. Change “First Name” value as “Steven1” and press the “Save” button. Note that your change is committed successfully.

Next, in the second instance, open the same row for editing and change the “Last Name” value. This time, since this row was already changed by another application instance(first instance), when you press the “Save” button the concurrency violation occurs and the warning message appears.

Concurrency violation occured

Press the “Refresh Details” button to retrieve the most recent copy of this row. Note that the “First Name” value is updated as “Steven1”. Change the “Last Name” again and press the “Save” button. Since this time the commit is applied to the latest copy of the row, concurrency violation does not occur and your change is committed successfully.

  • Andrew

    Hello Team,

    e_concurrencyVialotion or e_concurrencyViolation?

    • http://www.gerger.co Yalim K. Gerger

      Hi Andrew,
      Thanks for pointing this out. As it turns out, we made a typo while creating the actual exception. So unfortunately, it is called Vialotion in 1.5 and below. We will fix this in the upcoming release.