Handling concurrent database modifications in GNUmed

A design principle in GNUmed is to block concurrent use of patient data as little as possible. Most of the time this is no problem because different clients work on different patients and the design of PostgreSQL easily allows for that.

However, every so often, the same patient will be open and edited on several workplaces throughout a practice. There are two problems that need to be taken care of in this scenario:

1) Propagation of changes to a patient record displayed on several machines

Assume this scenario as an example:

• reception nurse elicits report of allergy from patient
• patient moves to exam room
• patient record opened in exam room
• nurse gets around to record allergy at front desk

The problem arises because the exam room machine read the patient record before the allergy was entered such that no allergy is being displayed to the doctor. While the doctor is, of course, medico-legally required to ask for allergies before starting a treatment, it is desirable that the exam room frontend be notified about the new allergy having been entered into the system without dependence on the downstream user to check whether the information had undergone any change.

To enable this notification GNUmed uses the NOTIFY/LISTEN facility of PostgreSQL.

Backend

During database creation the allergy table is configured to deliver signals to interested frontends:

   select gm.add_table_for_notifies('clin', 'allergy', 'allg');

There are several flavours of add_table_for_notifies() available and documented in the gm schema. Metadata for tables sending out notifications is stored in gm.notifying_tables. The last (boolean) parameter defines whether the notification system will .

Later in the bootstrapping process the Python script gmNotificationSchemaGenerator.py creates appropriate AFTER UPDATE/INSERT/DELETE triggers based on the information in gm.notifying_tables. If the respective table contains a column known to directly or indirectly link to a patient identity the trigger will add the respective patient primary key to the signal it sends out - as opposed to, say, lookup tables.

During normal database operation those triggers send out a NOTIFY whenever an UPDATE/INSERT/DELETE commits from any client. In the above example the signal name eventually sent out would be allg_mod_db:123 assuming a row related to patient 123 was changed.

Frontend

During client startup a thread is created which listens for NOTIFY signals from the database. It autoconfigures itself to listen for all signals recorded in gm.notifying_tables. It also listens to the patient change signals sent from within its own client. Whenever the active patient changes the thread reconfigures itself to only listen for those patient-related backend signals which are specific to that patient -- to that end the backend signals are tagged with patient primary keys where appropriate (see above). The signal is stripped of the patient ID and reinjected into the client for further processing via gmDispatcher.py. That way the frontend code only needs to listen to a generic signal (such as allg_mod_db) and can still be sure to receive signals relevant to the currently active patient only.

Going along with the above example: the top panel (the two-line pane at the top of the client) listens for the signal allg_mod_db. Note that this signal can arrive asynchronously at any time but in the context of the listener thread ! Since wxPython is not written to reliably do GUI operations outside the GUI thread (often the main thread but in any case the thread that initialized wxPython) care needs to be taken when acting upon signals. If any GUI work needs to get done (such as updating fields onscreen) the recommended procedure is to use wx.CallAfter() to execute the GUI code in the context of the GUI thread like this:

gmDispatcher.connect(signal = 'allg_mod_db', receiver = _on_allg_mod_db_in_listener_thread)

def _on_allg_mod_db_in_listener_thread(*args, *kwargs):
   wx.CallAfter(self._on_allg_mod_db_in_gui_thread, *args, *kwargs)

def _on_allg_mod_db(*args, *kwargs):
   update_allergies_display()

This code makes allergies entered by any client automagically appear in all other clients displaying the same patient.

2) Conflicting patient record updates

GNUmed only ever "takes a write lock" on a row when the row is actually about to be written to. It does not:

• lock it
• let the user edit the data
• then save it

but rather

• read it
• let the user edit the data
• "lock" and save it

Assume this scenario as an example:

• reception staff reconfirms allergy with patient
• new information elicited for allergy entry
  • allergy to penicillin reported to have resulted in anaphylactic shock rather than simply a rash as had been recorded earlier
• nurse starts editing the allergy entry but gets interrupted by phone call and scurries off for some lab work while the allergies editor stays open
• patient moves to exam room
• doctor reconfirms allergies before starting treatment
• careful history taking reveals the patient "simply" had medium dyspnoe rather than a full-blown anaphylactic reaction
• doctor records the newly found change to the allergies record
• nurse finishes phone call and continues entering what she knows as the new allergy information

There are two potential problems in this situation:

1) Two overlapping transactions try to update the same row

This occurs very rarely and happens in the following sequence of events (note how it only concerns itself with two concurrent transactions):

transaction A	transaction B
BEGIN
	BEGIN
... UI work ...	... UI work ...
COMMIT
	COMMIT

GNUmed uses the transaction level serializable which means transaction B will fail at the COMMIT step. This is standard transactional behaviour. Using the serializable transaction level is what amounts to the "write lock" alluded to above. GNUmed doesn't ever really take an actual lock since MVCC and transaction semantics afford the needed behaviour.

Note that since GNUmed keeps the duration of updating transactions to a minimum (by only starting them just-in-time) the probability of such serialization conflicts are very rare.

In the above scenario it would require that doctor and nurse start saving their respective allergy update virtually at the same time.

2) Two user actions framing each other try to update the same row

This is way more likely to occur than with overlapping transactions as described above. The sequence of events is as follows (note how this concerns itself with user actions reaching beyond the duration of a single transaction):

nurse	doctor
reads row (begin; ...; commit;)
	reads row (begin; ...; commit;)
changes row in UI
	changes row in UI
	updates row (begin; ...; commit;)
updates row (begin; ...; commit;)

There is no open transaction from when a read finishes until an update starts. Therefore the doctor's change to the row commits successfully. However, the nurse's change would commit successfully, too without her being aware of the intermittent change by the doctor ! The reason being that the database transactions do not actually conflict even in serializable mode.

One of the solutions to this concurrency problem is Optimistic Locking. GNUmed employs the change indicator column strategy ("mark the source with a unique identifier") to detect intermittent updates (google for "handy proxy for row has not changed is to see if its XMIN system column is still the same"). The update indicator comes cheap in the form of the XMIN column of PostgreSQL. XMIN is the ID of the database transaction which last modified the row. If a row changes XMIN changes, too. This is a core feature of PostgreSQL's MVCC architecture and is thus very unlikely to disappear (google for " XMIN semantic at peril").

There is a base class cBusinessDBObject in client/pycommon/gmBusinessDBObject.py which implements the optimistic locking for all it subclasses. Each instance represents a row in the database. When the table data is read from the backend the corresponding XMIN and primary key value are remembered. Later on when the data has been updated and is to be written back to the backend the class employs the following construct in the WHERE clause of its update query:

update <table> set
   <column> = <value>
   ...
where
   <pk column> = <pk value of this row> and
   XMIN = <original XMIN value>

Assuming the query is syntactically correct three situations can arise:

• the original row has been deleted by another writer
  • neither XMIN nor the primary key will match any row so no row is updated which is detected as a concurrency conflict

• the original row has been updated by another writer
  • the primary key will find the row in question but XMIN will have changed so it doesn't match any row and no row is updated which indicates that a concurrent change happened which, again, is detected as a concurrency conflict

• the original row has not been touched
  • both the primary key and XMIN will find the row in question and allow the update to proceed so one row is updated and all is well

GNUmed needs to improve the handling of detected deletions and concurrent updates. Currently it at least detects them and refuses to silently overwrite existing medical data.