14. Understanding Multiversion Concurrency Control : Application Consistency
 
Share this page                  
Application Consistency
When using MVCC, be aware of possible inconsistencies.
Before Updates
Table X
+-------+-------+
|Key    |Col    |
+-------+-------+
|1      |$1000  |
+-------+-------+
Table Y
+-------+-------+
|Key    |Col    |
+-------+-------+
|1      |$5000  |
+-------+-------+
1. User2 : { Start of transaction }
2. User1 : UPDATE X SET Col=Col-100 WHERE Key=1;
3. User2 : SELECT Col FROM X WHERE Key=1;
4. User1 : UPDATE Y SET Col=Col+100 WHERE Key=1;
5. User1 : COMMIT;
6. User2 : SELECT Col FROM Y WHERE Key=1;
7. User2 : COMMIT;
After Updates
Table X
+-------+-------+
|Key    |Col    |
+-------+-------+
|1      |$900   |
+-------+-------+
Table Y
+-------+-------+
|Key    |Col    |
+-------+-------+
|1      |$5100  |
+-------+-------+
 
Isolation Level
Read Consistency Level
SELECT
FROM X
Returns
SELECT
FROM Y
Returns
X.col +
Y.col =
Serializable
Transaction
$1000
$5000
$6000
Read Committed
Statement
$1000
$5100
$6100
Note:  With traditional locking, selects at line 3 and 6 return $900 and $5100.