Question & Answer
Question
What is Relaxed Transaction Serialization in Netezza and how can I use it with Informatica?
Answer
Background
With Netezza’s default configuration, the granularity of serialization checking is at the table level. While this is generally considered acceptable for “read-mostly” data warehouse environments, it does impose some restrictions on concurrent writes within the system. Such concurrent writes that violate the transaction serializability rules (conflicting transactions) abort by default. (For additional details, refer to the document referenced below, Error: Could not serialize - transaction aborted.)
While there are transaction queuing configurations that can mitigate this for implicit transactions, Netezza is not able to queue requests from explicit transactions. Thus the client application remains responsible for coping with serialization errors and resubmitting transactions when necessary.
Practically speaking, BI workflows that execute on more traditional DBMSs with weaker transaction semantics can run into serialization errors on NPS systems resulting in the need to redesign these workflows to comply with Netezza’s transaction serialization rules. This most frequently occurs with Informatica workflows.
About Relaxed Transaction Serialization
In NPS 4.5.4P6 and later, 4.6.8P2 and later, and Netezza 6.0.2 and later, Netezza introduced an approach that supports SQL repeatable read transaction isolation mode with write-write conflict checking at the row level. (It is also known as relaxed transaction serialization.) In moving to Repeatable Read transaction semantics, if workflows are written to preserve inter-row constraints, these constraints may not be preserved after concurrent non-serializable execution of the transactions. (For more information about data consistency as it relates to transaction isolation, refer to the Snapshot Isolation document in Wikipedia.)
Note: This configuration should only be applied when Netezza Support deems that an existing application would require significant redesign to avoid transaction serialization errors.
In most cases, Repeatable Read semantics should allow workflows that may have been failing with “could not serialize” errors to complete with minor (if any) design changes.
To enable repeatable read isolation mode (relaxed transaction serialization):
1. Edit the /nz/data.1.0/postgresql.conf file to add or modify the following line to the Transaction Management section:
- serializable = false
Note: If this line doesn’t exist or it is set to true, transaction serialization is on because it is the default.
2. Restart the system by running the following command:
- nzsystem restart
3. Run the following command to verify that serializable mode is turned off (or on depending on your settings):
- SYSTEM(ADMIN)=> show serializable;
NOTICE: SERIALIZABLE is off
SHOW VARIABLE
Informatica Support
In Informatica 8.6.1, Informatica introduced “Target Control Groups” in an effort to catch serialization issues before they made it to the Netezza system. However, this feature needs to be disabled in order for the relaxed serialization functionality to work. Contact your Informatica support representative to verify if this patch is available for your platform and release level.
Related Information
Historical Number
NZ015675
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21577436