System reliability

It is important that the system stores data reliably. In the framework trials, each user inserted around 30 records into the database without any problems. In the performance testing, we inserted more than 100,000 records via the web service (including bulk loading a set of 5,000 records) with no problems. However, there could be two issues in real production

environments:

1. The system stores records in too many tables (base, organisational and individual level). If unexpected errors happen on the server (such as system crash), the system must ensure that the data is stored consistently, i.e. all saved to the corresponding tables or else rolled back.

2. In high concurrency situations (i.e. multiple users updating the same sets of data), the system must handle any update conflicts so that no data is lost.

10.3.1 Ensuring consistent updates

We have defined transaction boundaries for the database updates in the web service by using a begin transaction and commit transaction around all updated operations. Within a single transaction, all database updates must be either completed in entirety or aborted. This prevents data from becoming corrupted or inconsistent. For example, saving an Onion PCR assay object to the database requires inserting data into four different tables (see Figure 10-2): a framework level table, organisational level table, individual level table and the table for storing the associations. If everything goes well, the transaction is committed.

114

In contrast, if something goes wrong in updating any of the tables, the whole transaction will be automatically rolled back so that no records in any of the tables are changed. That

guarantees that the system updates all related tables or leaves them all unchanged. In addition, the transaction is handled by the web service, so the EUDs do not need to worry about

transaction details. The system will return an error message to the application if the update was not successful.

1 Find a onion DNA sample from the database 2 Assign the sample object to the assay object 3 Save data into Table Assay (framework) 4 Save data into Table PFRAssay (organisational) 5 Save data into Table OnionPCRAssay (individual)

6 Save assay and sample associations in Table Assay_Sample Save a onion

PCR Assay

everything goes well

something goes wrong

Transaction Committed

Transaction Rolled back

Figure 10-2 Transaction saving an Assay record in the database

10.3.2 Handle conflicting updates

The system should also provide concurrency control in order to avoid conflicts with data being updated by more than one user. For example, a user retrieves 20 sample records to update. After five minutes, the user submits the modified data and expects this will be saved. In this case, the user also believes that she/he is the only person updating those 20 records and no conflicting updates happen during the five minutes. If another user has updated those samples in the meantime, the system should reject conflicting updates from the first user, and return an error message to the client application. The system should help EUDs to make sure there are no conflicting updates.

At NZPFR, each user group has only one or two users updating the data which makes it a low concurrency environment. Therefore, we did not implement concurrency control which means the last committed transaction for a particular record “wins”. However, if the system is

employed in a high concurrency environment, tools must be provided for framework mangers to handle conflicting updates.

We used Hibernate to manage the persistence logic and it supports two concurrency strategies (similar strategies are available in other ORM libraries): Pessimistic and Optimistic

Concurrency Control (PCC and OCC).

1. PCC locks table rows when users retrieve data for updating. The lock can be enabled to prevent conflicting updates when “SELECT ... FOR UPDATE” is detected.

115

2. OCCdoes not lock the data when the first user retrieves a record for updating. The system can use timestamps or version numbers to check whether another user updates data record after the first user retrieved it. If conflicting updates are detected, the system rolls back the transaction and sends an error message to the first user. The system is able to force the first user to retrieve the latest version for updating.

Concurrent modifications can be committed only if the modifications do not conflict. In future, we will look at improving the framework configuration tools to make it easy to enable concurrency control. In order to prevent lost updates in a high concurrency

environment, we will recommend using the OCC strategy rather than the PCC strategy. PCC simply locks records when updating is detected. That means it is not possible for multiple users to work with the same records at the same time. Instead of locking records, OCC can detect and prevent conflicting updates. It allows multiple users work on the same record as long as there is no conflict detected.

If a conflict is detected by the framework an error message must be returned to the client application so it can handle the problem. The client application can display an error message to inform the users or retrieve the latest version of data.