DB2 - Problem description
Problem IT22050 | Status: Closed |
DB2 : MISSING DATA ON QUERY WITH CURRENTLY COMMITTED FEATURE, WHEN SUCH QUERY IS EXECUTED IN THE MIDDLE OF COMMIT PROCESSING | |
product: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problem description: | |
You may observe that if there are some transactions running alongside each other, accessing from tables in which data is being inserted or updated, some data might be missing. You will eventually see the complete data on later accesses. Example to explain the problem: ------------------------------ Given : Transactions : UOW1 and UOW2 Tables : T1 and T2 Working : 1. UOW1 INSERTs/UPDATEs data in T1 2. UOW1 INSERTs record in T2, suggesting INSERT/UPDATE to T1 is complete 3. UOW2 SELECTs from T2 if it has a record, if yes, SELECTs * from T1 Problem : UOW1 performs INSERT/UPDATE on T1, then INSERT/UPDATE T2 to indicated the new data in T1. UOW2 queries T2 (possibly in a loop) for indication of new data in T1, then when it sees the indication in T2 by UOW1, SELECT from T1 for the new data, but does not see all of the changes in T1 made by UOW1. Later on SELECT from T1 will eventually see all the changes made by UOW1. Explanation : Lock attributes are used to implement currently committed feature. It means that queries will not be blocked when seeing uncommitted changes. However, during COMMIT processing, locks are released serially. If another transaction executes SELECT during the COMMIT processing, in particular when some but not all of the locks acquired by the committing transaction have been released, the SELECT will see some but not all of the changes. Note: the issue may be more pronounced in pureScale. NOTE: In order for the fix to take effective, you will also need to turn on the Db2 registry variable DB2_SYNC_RELEASE_LOCK_ATTRIBUTES after applying the fix and changes to this variable do not require the database instance to be restarted: db2set DB2_SYNC_RELEASE_LOCK_ATTRIBUTES=YES | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All users using db2 V10.5 FP8 and below * **************************************************************** * PROBLEM DESCRIPTION: * * You may observe that if there are some transactions running * * alongside each other, accessing from tables in which data is * * being inserted or updated, some data might be missing. You * * will * * eventually see the complete data on later accesses. * * * * Example to explain the problem: * * ------------------------------ * * Given : * * Transactions : UOW1 and UOW2 * * Tables : T1 and T2 * * * * Working : * * 1. UOW1 INSERTs/UPDATEs data in T1 * * 2. UOW1 INSERTs record in T2, suggesting INSERT/UPDATE to T1 * * is * * complete * * 3. UOW2 SELECTs from T2 if it has a record, if yes, SELECTs * * * * * from T1 * * * * Problem : * * UOW1 performs INSERT/UPDATE on T1, then INSERT/UPDATE T2 to * * indicated the new data in T1. * * * * UOW2 queries T2 (possibly in a loop) for indication of new * * data * * in T1, then when it sees the indication in T2 by UOW1, * * SELECT * * from T1 for the new data, but does not see all of the * * changes in * * T1 made by UOW1. Later on SELECT from T1 will eventually * * see * * all the changes made by UOW1. * * * * Explanation : * * * * Lock attributes are used to implement currently committed * * feature. It means that queries will not be blocked when * * seeing * * uncommitted changes. However, during COMMIT processing, * * locks * * are released serially. If another transaction executes * * SELECT * * during the COMMIT processing, in particular when some but * * not * * all of the locks acquired by the committing transaction have * * been released, the SELECT will see some but not all of the * * changes. * * * * Note: the issue may be more pronounced in pureScale * * environment. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 V10.5 FP10 or above to resolve the problem. * * * * NOTE: In order for the fix to take effective, you will also * * need to turn on the Db2 registry variable * * DB2_SYNC_RELEASE_LOCK_ATTRIBUTES after applying the fix and * * changes to this variable do not require the database * * instance to be restarted: * * * * db2set DB2_SYNC_RELEASE_LOCK_ATTRIBUTES=YES * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Comment | |
NOTE: In order for the fix to take effective, you will also need to turn on the Db2 registry variable DB2_SYNC_RELEASE_LOCK_ATTRIBUTES after applying the fix and changes to this variable do not require the database instance to be restarted: db2set DB2_SYNC_RELEASE_LOCK_ATTRIBUTES=YES | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 18.08.2017 16.07.2018 13.02.2019 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |