In a multiuser environment, there are two models for updating data in a database: optimistic concurrency, and pessimistic concurrency. [1]

Pessimistic Concurrency Model. Pessimistic concurrency involves locking rows at the data source to prevent users from modifying data in a way that affects other users. In a pessimistic model, when a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the lock owner releases it. This model is primarily used in environments where there is heavy contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

Therefore, in a pessimistic currency model, a user who reads a row with the intention of changing it establishes a lock. Until the user has finished the update and released the lock, no one else can change that row. For this reason, pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency is not a scalable option when users are interacting with data, causing records to be locked for relatively large periods of time.

Optimistic Concurrency Model. By contrast, users who use optimistic concurrency do not lock a row when reading it. When a user wants to update a row, the application must determine whether another user has changed the row since it was read. Optimistic concurrency is generally used in environments with a low contention for data. This improves performance as no locking of records is required, and locking of records requires additional server resources. Also, in order to maintain record locks, a persistent connection to the database server is required. Because this is not the case in an optimistic concurrency model, connections to the server are free to serve a larger number of clients in less time.

In an optimistic concurrency model, a violation is considered to have occurred if, after a user receives a value from the database, another user modifies the value before the first user has attempted to modify it.

The PASI core uses an optimistic concurrency approach using the PASICoreVersion (version) field on key data contracts like Student and StudentSchoolEnrolment. This version number is unique across the entire PASI core (e.g. there will not be a StudentSchoolEnrolment and Student with a PASICoreVersion of "2")When submitting new data the version field is ignored; however, the current version must be provided when updating or correcting data. If the version supplied by the client does not match the version in the core then no data is saved in the core and an error is returned to the client.

Each of the services available to update student information outlined in this document take the student's version as a parameter. If the PASI client wishes to update a student's demographic record they would supply the version number of the student and the updated demographic information. This is to check if the client is working with the correct version of the entire student record before proceeding. If the versions are not in sync a call to GetStudent can be made to get the current student record and the update can be done again if still applicable.

Following is a simplified example of updating a student name:

If PASI Client 1 attempted to update the name for student 123456789, version 2 would be passed in the service call and the update could proceed. If PASI Client 2 attempted to update the name for student 123456789 and passed version 1 into the service call it would fail because the PASI Core is currently at version 2. PASI Client 2 should call GetStudent to ensure the most current version available before attempting this update.

It is recommended that PASI Clients wishing to submit updates to the PASI Core implement a mechanism to keep track of the current PASI Version within their system. This can be best achieved by saving the PASI Version number during the data synchronization process (refer to the topic Data Synchronization for more details).

PASI core provides two ways of modifying student data:

1) Update - e.g. a student moved and now has a new address or a change in guardianship.

2) Correction - e.g. a student noticed their postal code or other information was incorrect and it needs to be corrected.

The key difference between the two types of updates from the PASI perspective is whether history is created or not in the PASI core. In the above correction example the postal code was never supposed to be the value currently stored so keeping history of the wrong postal code adds no value to the system. This correction would still be captured from an audit perspective but wouldn't become part of the student's record.

For the services that allow corrections to be provided the data contract will follow a general pattern that accepts the new value(s) as well the unique identifier for the current value type. As part of the optimistic concurrency the PASICoreVersion also needs to be provided.

The following class diagram represents the object to update a student address

If the PASI client wants to "update" the address because a student has moved the NewAddress, StateProvinceId and Student PASICoreVersion properties would have to be populated. In this case the NewAddress.RefId should be set to zero.

If the PASI client wants to "correct" an address, the NewAddress.RefId would need to be populated with the PASI core unique identifier for the address in question. This identifier would have been retrieved earlier by the client by calling one of the services to retrieve student information (please see the Student Retrieval Services document for details). The NewAddress would be populated with the complete address as it should appear and the StudentPASICoreVersion must be populated to ensure the client has the current version of the student.

In the above example, the identifier in the NewAddress.RefId property is used to locate the record that should be corrected. If the NewAddress.RefId is not found or does not belong to the Student (based on the StateProvinceId) no update will be preformed and an error is returned to the client.

Date fields on the data contracts in PASI are generally called one of two names:

1. EffectiveUtc
2. EffectiveDate or ExpiryDate

The key difference between these two names is what system is expected to populate the date. The EffectiveUtc fields will be populated by PASI Core. Even if the client supplies a value the value will be overwritten by PASI core when the information is saved for the first time. When an updates occurs to the same record the EffectiveUtc date is not changed. The EffectiveDate and ExpiryDate fields are expected to be populate by the PASI client when required.

How will it be used?

The offset allows us to determine when an event will happen locally and also the time it will occur in any other region. For example, if students are writing an exam in Paris, France, we can determine when the students are expected to write the exam (in Paris) and also what time that will be in Calgary.

Paris Date\Time: 2013-11-20 09:00:00
Paris Offset: UTC +1 hour

By knowing the offset in Paris, France on 2013-11-20 is +1 hour, we can determine the UTC.

UTC Date\Time: 2013-11-20 08:00:00

Using the UTC Date\Time, we can determine the time this event will occur in any other region by applying the region’s offset to the UTC representation of event date\time.

Calgary Offset: UTC -7 hours
Calgary Date\Time: 2013-11-20 01:00:00

Technical Details

DateTimeOffset is composed of two parts; the DateTime and Offset. The DateTime can be represented in local or UTC time. In either scenario, the Offset portion is mandatory.

The DateTime portion can be represented as UTC. A trailing “Z” (Zulu) is part of the ISO 8601 datetime standard for UTC times and is used to indicate when a DateTime is UTC. Example: 2013-11-20T08:00:00Z DateTimeOffset values returned from PASI will be in UTC.

MSDN - DateTimeOffset Structure: https://msdn.microsoft.com/en-us/library/system.datetimeoffset(v=vs.110).aspx