/usr/share/doc/gnumed/user-manual/Gnumed/DBConcurrencyHandling.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en_US" lang="en_US">
<head>
	<title> DBConcurrencyHandling &lt; Gnumed &lt; Foswiki</title>
		  
	<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <meta name="robots" content="noindex" /> <link rel="alternate" type="application/rss+xml" title="RSS Feed" href="WebRss.html" />
	<link rel="icon" href="../rsrc/System/ProjectLogos/favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="../rsrc/System/ProjectLogos/favicon.ico" type="image/x-icon" />
	<link rel="alternate" href="http://wiki.gnumed.de/bin/edit/Gnumed/DBConcurrencyHandling?t=1391005508" type="application/x-wiki" title="edit DBConcurrencyHandling" />
	<meta name="description" content="DBConcurrencyHandling" />
	 <!--[if IE]></base><![endif]-->
	
	<style type="text/css" media="all">
@import url('../rsrc/System/SkinTemplates/base.css');
</style>
<style type="text/css" media="all">
@import url('../rsrc/System/SkinTemplates/default.css');
</style>
<!--[if IE]><style type="text/css" media="screen">
pre {
	overflow-x:auto;
	padding-bottom:expression(this.scrollWidth > this.offsetWidth ? 16 : 0);
}
</style>
<![endif]-->

<style type="text/css" media="all">
.foswikiTable {border-width:1px}
.foswikiTable .tableSortIcon img {padding-left:.3em; vertical-align:text-bottom}
.foswikiTable td {border-style:solid none; vertical-align:top}
.foswikiTable th {border-style:none solid; vertical-align:top; background-color:#d6d3cf; color:#000000}
.foswikiTable th a:link {color:#000000}
.foswikiTable th a:visited {color:#000000}
.foswikiTable th a:hover {color:#000000; background-color:#d6d3cf}
.foswikiTable th.foswikiSortedCol {background-color:#c4c1ba}
.foswikiTable tr.foswikiTableRowdataBg0 td {background-color:#ffffff}
.foswikiTable tr.foswikiTableRowdataBg0 td.foswikiSortedCol {background-color:#f7f7f6}
.foswikiTable tr.foswikiTableRowdataBg1 td {background-color:#f7f7f6}
.foswikiTable tr.foswikiTableRowdataBg1 td.foswikiSortedCol {background-color:#f0f0ee}
</style><!--TABLEPLUGIN_default-->
<meta name="foswiki.PUBURL" content="http://wiki.gnumed.de/pub" /> <!-- PUBURL -->
<meta name="foswiki.PUBURLPATH" content="/pub" /> <!-- PUBURLPATH -->
<meta name="foswiki.SCRIPTSUFFIX" content="" /> <!-- SCRIPTSUFFIX -->
<meta name="foswiki.SCRIPTURL" content="http://wiki.gnumed.de/bin" /> <!-- SCRIPTURL -->
<meta name="foswiki.SCRIPTURLPATH" content="/bin" /> <!-- SCRIPTURLPATH -->
<meta name="foswiki.SERVERTIME" content="29%20Jan%202014%20-%2015:25" /> <!-- SERVERTIME -->
<meta name="foswiki.SKIN" content="twikinet%2c%20pattern" /> <!-- SKIN -->
<meta name="foswiki.SYSTEMWEB" content="System" /> <!-- SYSTEMWEB -->
<meta name="foswiki.TOPIC" content="DBConcurrencyHandling" /> <!-- TOPIC -->
<meta name="foswiki.USERNAME" content="KarstenHilbert" /> <!-- USERNAME -->
<meta name="foswiki.USERSWEB" content="Main" /> <!-- USERSWEB -->
<meta name="foswiki.WEB" content="Gnumed" /> <!-- WEB -->
<meta name="foswiki.WIKINAME" content="KarstenHilbert" /> <!-- WIKINAME -->
<meta name="foswiki.WIKIUSERNAME" content="Main.KarstenHilbert" /> <!-- WIKIUSERNAME -->
<meta name="foswiki.NAMEFILTER" content="%5b%5cs%5c*%3f~%5e%5c%24%40%25%60%22'%26%3b%7c%3c%3e%5c%5b%5c%5d%23%5cx00-%5cx1f%5d" /> <!-- NAMEFILTER --><!--JQUERYPLUGIN::FOSWIKI::META-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/jquery-1.4.3.js'></script><!--JQUERYPLUGIN-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/plugins/livequery/jquery.livequery.js'></script><!--JQUERYPLUGIN::LIVEQUERY-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/plugins/foswiki/jquery.foswiki.js'></script><!--JQUERYPLUGIN::FOSWIKI-->
<script type='text/javascript' src='../rsrc/System/JSTreeContrib/jquery.jstree.js'></script><!--JQUERYPLUGIN::JSTREE-->
</head>
<body class=""><div class="foswikiPage">
<a name="PageTop"></a> 
<p></p>
<p></p>
<h1><a name="Handling_concurrent_database_modifications_in_GNUmed"></a>  Handling concurrent database modifications in GNUmed </h1>
<p></p>
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.
<p></p>
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:
<p></p>
<h2><a name="A_1_41_Propagation_of_changes_to_a_patient_record_displayed_on_several_machines"></a>  1) Propagation of changes to a patient record displayed on several machines </h2>
<p></p>
Assume this scenario as an example:
<p></p> <ul>
<li> reception nurse elicits report of allergy from patient
</li> <li> patient moves to exam room
</li> <li> patient record opened in exam room
</li> <li> nurse gets around to record allergy at front desk
</li></ul> 
<p></p>
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 <em>without</em> dependence on the downstream user to check whether the information had undergone any change.
<p></p>
To enable this notification GNUmed uses the <code>NOTIFY/LISTEN</code> facility of PostgreSQL.
<p></p>
<h3><a name="Backend"></a>  Backend </h3>
<p></p>
During database creation the allergy table is configured to deliver signals to interested frontends:
<pre>
   select gm.add&#95;table&#95;for&#95;notifies(&#39;clin&#39;, &#39;allergy&#39;, &#39;allg&#39;);
</pre>
There are several flavours of <code>add_table_for_notifies()</code> available and documented in the <code>gm</code> schema. Metadata for tables sending out notifications is stored in <code>gm.notifying_tables</code>. The last (boolean) parameter defines whether the notification system will .
<p></p>
Later in the bootstrapping process the Python script <code>gmNotificationSchemaGenerator.py</code> creates appropriate <code>AFTER UPDATE/INSERT/DELETE</code> triggers based on the information in <code>gm.notifying_tables</code>. 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.
<p></p>
During normal database operation those triggers send out a <code>NOTIFY</code> whenever an <code>UPDATE/INSERT/DELETE</code> commits from any client. In the above example the signal name eventually sent out would be <code>allg_mod_db:123</code> assuming a row related to patient 123 was changed.
<p></p>
<h3><a name="Frontend"></a>  Frontend </h3>
<p></p>
During client startup a thread is created which listens for <code>NOTIFY</code> signals from the database. It autoconfigures itself to listen for all signals recorded in <code>gm.notifying_tables</code>. 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 <code>gmDispatcher.py</code>. That way the frontend code only needs to listen to a generic signal (such as <code>allg_mod_db</code>) and can still be sure to receive signals relevant to the currently active patient only.
<p></p>
Going along with the above example: the top panel (the two-line pane at the top of the client) listens for the signal <code>allg_mod_db</code>. Note that this signal can arrive asynchronously at any time <strong>but in the context of the listener thread</strong> ! 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 <code>wx.CallAfter()</code> to execute the GUI code in the context of the GUI thread like this:
<pre>
gmDispatcher.connect(signal &#61; &#39;allg&#95;mod&#95;db&#39;, receiver &#61; &#95;on&#95;allg&#95;mod&#95;db&#95;in&#95;listener&#95;thread)

def &#95;on&#95;allg&#95;mod&#95;db&#95;in&#95;listener&#95;thread(&#42;args, &#42;kwargs):
   wx.CallAfter(self.&#95;on&#95;allg&#95;mod&#95;db&#95;in&#95;gui&#95;thread, &#42;args, &#42;kwargs)

def &#95;on&#95;allg&#95;mod&#95;db(&#42;args, &#42;kwargs):
   update&#95;allergies&#95;display()
</pre>
<p></p>
This code makes allergies entered by any client automagically appear in all other clients displaying the same patient.
<p></p>
<h2><a name="A_2_41_Conflicting_patient_record_updates"></a>  2) Conflicting patient record updates </h2>
<p></p>
GNUmed only ever "takes a write lock" on a row when the row is actually about to be written to. It does <em>not</em>:
<p></p> <ul>
<li> lock it
</li> <li> let the user edit the data
</li> <li> then save it
</li></ul> 
<p></p>
but rather
<p></p> <ul>
<li> read it
</li> <li> let the user edit the data
</li> <li> "lock" and save it
</li></ul> 
<p></p>
Assume this scenario as an example:
<p></p> <ul>
<li> reception staff reconfirms allergy with patient
</li> <li> new information elicited for allergy entry <ul>
<li> allergy to penicillin reported to have resulted in anaphylactic shock rather than simply a rash as had been recorded earlier
</li></ul> 
</li> <li> 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
</li> <li> patient moves to exam room
</li> <li> doctor reconfirms allergies before starting treatment
</li> <li> careful history taking reveals the patient "simply" had medium dyspnoe rather than a full-blown anaphylactic reaction
</li> <li> doctor records the newly found change to the allergies record
</li> <li> nurse finishes phone call and continues entering what she knows as the new allergy information
</li></ul> 
<p></p>
There are two potential problems in this situation:
<p></p>
<h3><a name="A_1_41_Two_overlapping_transactions_try_to_update_the_same_row"></a>  1) Two <em>overlapping transactions</em> try to update the same row </h3>
<p></p>
This occurs very rarely and happens in the following sequence of events (note how it only concerns itself with two concurrent transactions):
<p></p>
<table rules="none" border="1" class="foswikiTable">
	<thead>
		<tr class="foswikiTableOdd foswikiTableRowdataBgSorted0 foswikiTableRowdataBg0">
			<th align="center" class="foswikiTableCol0 foswikiFirstCol"> <a rel="nofollow" href="/bin/rest/PublishPlugin/publish?validation_key=b6adb20ba63cfbada77bacf048ebff7c;topic=Gnumed.PublishManual;ping=29%20Jan%202014%20-%2015:24;sortcol=0;table=1;up=0#sorted_table" title="Sort by this column">transaction A</a> </th>
			<th align="center" class="foswikiTableCol1 foswikiLastCol"> <a rel="nofollow" href="/bin/rest/PublishPlugin/publish?validation_key=b6adb20ba63cfbada77bacf048ebff7c;topic=Gnumed.PublishManual;ping=29%20Jan%202014%20-%2015:24;sortcol=1;table=1;up=0#sorted_table" title="Sort by this column">transaction B</a> </th>
		</tr>
	</thead>
	<tbody>
		<tr class="foswikiTableEven foswikiTableRowdataBgSorted0 foswikiTableRowdataBg0">
			<td class="foswikiTableCol0 foswikiFirstCol"> <code>BEGIN</code> </td>
			<td class="foswikiTableCol1 foswikiLastCol"> &nbsp; </td>
		</tr>
		<tr class="foswikiTableOdd foswikiTableRowdataBgSorted1 foswikiTableRowdataBg1">
			<td class="foswikiTableCol0 foswikiFirstCol"> &nbsp; </td>
			<td class="foswikiTableCol1 foswikiLastCol"> <code>BEGIN</code> </td>
		</tr>
		<tr class="foswikiTableEven foswikiTableRowdataBgSorted0 foswikiTableRowdataBg0">
			<td class="foswikiTableCol0 foswikiFirstCol"> ... UI work ... </td>
			<td class="foswikiTableCol1 foswikiLastCol"> ... UI work ... </td>
		</tr>
		<tr class="foswikiTableOdd foswikiTableRowdataBgSorted1 foswikiTableRowdataBg1">
			<td class="foswikiTableCol0 foswikiFirstCol"> <code>COMMIT</code> </td>
			<td class="foswikiTableCol1 foswikiLastCol"> &nbsp; </td>
		</tr>
		<tr class="foswikiTableEven foswikiTableRowdataBgSorted0 foswikiTableRowdataBg0">
			<td class="foswikiTableCol0 foswikiFirstCol foswikiLast"> &nbsp; </td>
			<td class="foswikiTableCol1 foswikiLastCol foswikiLast"> <code>COMMIT</code> </td>
		</tr>
	</tbody></table>
<p></p>
GNUmed uses the transaction level <em>serializable</em> which means transaction B will fail at the <code>COMMIT</code> step. This is standard transactional behaviour. Using the <em>serializable</em> transaction level is what amounts to the "write lock" alluded to above. GNUmed doesn't ever really take an <em>actual</em> lock since MVCC and transaction semantics afford the needed behaviour.
<p></p>
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.
<p></p>
In the above scenario it would require that doctor and nurse start <em>saving</em> their respective allergy update virtually at the same time.
<p></p>
<h3><a name="A_2_41_Two_user_actions_framing_each_other_try_to_update_the_same_row"></a>  2) Two user actions <em>framing</em> each other try to update the same row </h3>
<p></p>
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):
<p></p>
<table rules="none" border="1" class="foswikiTable">
	<thead>
		<tr class="foswikiTableOdd foswikiTableRowdataBgSorted0 foswikiTableRowdataBg0">
			<th class="foswikiTableCol0 foswikiFirstCol"> <a rel="nofollow" href="/bin/rest/PublishPlugin/publish?validation_key=b6adb20ba63cfbada77bacf048ebff7c;topic=Gnumed.PublishManual;ping=29%20Jan%202014%20-%2015:24;sortcol=0;table=2;up=0#sorted_table" title="Sort by this column">nurse</a> </th>
			<th class="foswikiTableCol1 foswikiLastCol"> <a rel="nofollow" href="/bin/rest/PublishPlugin/publish?validation_key=b6adb20ba63cfbada77bacf048ebff7c;topic=Gnumed.PublishManual;ping=29%20Jan%202014%20-%2015:24;sortcol=1;table=2;up=0#sorted_table" title="Sort by this column">doctor</a> </th>
		</tr>
	</thead>
	<tbody>
		<tr class="foswikiTableEven foswikiTableRowdataBgSorted0 foswikiTableRowdataBg0">
			<td class="foswikiTableCol0 foswikiFirstCol"> reads row (<code>begin; ...; commit;</code>) </td>
			<td class="foswikiTableCol1 foswikiLastCol"> &nbsp; </td>
		</tr>
		<tr class="foswikiTableOdd foswikiTableRowdataBgSorted1 foswikiTableRowdataBg1">
			<td class="foswikiTableCol0 foswikiFirstCol"> &nbsp; </td>
			<td class="foswikiTableCol1 foswikiLastCol"> reads row (<code>begin; ...; commit;</code>) </td>
		</tr>
		<tr class="foswikiTableEven foswikiTableRowdataBgSorted0 foswikiTableRowdataBg0">
			<td class="foswikiTableCol0 foswikiFirstCol"> changes row in UI </td>
			<td class="foswikiTableCol1 foswikiLastCol"> &nbsp; </td>
		</tr>
		<tr class="foswikiTableOdd foswikiTableRowdataBgSorted1 foswikiTableRowdataBg1">
			<td class="foswikiTableCol0 foswikiFirstCol"> &nbsp; </td>
			<td class="foswikiTableCol1 foswikiLastCol"> changes row in UI </td>
		</tr>
		<tr class="foswikiTableEven foswikiTableRowdataBgSorted0 foswikiTableRowdataBg0">
			<td class="foswikiTableCol0 foswikiFirstCol"> &nbsp; </td>
			<td class="foswikiTableCol1 foswikiLastCol"> updates row (<code>begin; ...; commit;</code>) </td>
		</tr>
		<tr class="foswikiTableOdd foswikiTableRowdataBgSorted1 foswikiTableRowdataBg1">
			<td class="foswikiTableCol0 foswikiFirstCol foswikiLast"> updates row (<code>begin; ...; commit;</code>) </td>
			<td class="foswikiTableCol1 foswikiLastCol foswikiLast"> &nbsp; </td>
		</tr>
	</tbody></table>
<p></p>
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 <strong>would commit successfully, too</strong> without her being aware of the intermittent change by the doctor ! The reason being that the database transactions do not actually conflict even in <em>serializable</em> mode.
<p></p>
One of the solutions to this <a href="http://www.agiledata.org/essays/concurrencyControl.html" target="_top">concurrency problem</a> is <em>Optimistic Locking</em>. GNUmed <a href="http://archives.postgresql.org/pgsql-general/2004-10/msg01352.php" target="_top">employs the <em>change indicator column</em> strategy</a> ("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 <code>XMIN</code> system column is still the same"). The update indicator comes cheap in the form of the <code>XMIN</code> column of PostgreSQL. <code>XMIN</code> is the ID of the database transaction which last modified the row. If a row changes <code>XMIN</code> changes, too. This is a core feature of PostgreSQL's MVCC architecture and is thus <a href="http://groups.google.com/group/pgsql.general/browse_thread/thread/e3566ba76173d0bf/6cf3c243a86d9233" target="_top">very unlikely to disappear</a> (google for " <code>XMIN</code> semantic at peril").
<p></p>
There is a base class <code>cBusinessDBObject</code> in <code>client/pycommon/gmBusinessDBObject.py</code> 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 <code>XMIN</code> 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 <code>WHERE</code> clause of its update query:
<pre>
update &#60;table&#62; set
   &#60;column&#62; &#61; &#60;value&#62;
   ...
where
   &#60;pk column&#62; &#61; &#60;pk value of this row&#62; and
   XMIN &#61; &#60;original XMIN value&#62;
</pre>
<p></p>
Assuming the query is syntactically correct three situations can arise:
<p></p> <ul>
<li> the original row has been <em>deleted</em> by another writer <ul>
<li> neither <code>XMIN</code> nor the primary key will match any row so no row is updated which is detected as a concurrency conflict
</li></ul> 
</li></ul> 
<p></p> <ul>
<li> the original row has been <em>updated</em> by another writer <ul>
<li> the primary key will find the row in question but <code>XMIN</code> 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
</li></ul> 
</li></ul> 
<p></p> <ul>
<li> the original row has not been touched <ul>
<li> both the primary key and <code>XMIN</code> will find the row in question and allow the update to proceed so one row is updated and all is well
</li></ul> 
</li></ul> 
<p></p>
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.
<a name="TopicEnd"></a>
<p></p>
<p></p>
<p></p>
<p></p>
</div>
</body></html>
gnumed-doc 1.4.6+dfsg-1 / usr / share / doc / gnumed / user-manual / Gnumed / DBConcurrencyHandling.html
