Transaction concurrency and isolation
The ANSI/ISO SQL standard defines four levels of transaction isolation: uncommitted read, committed read, repeatable read, and serializable. The Netezza Performance Server for Cloud Pak for Data and Netezza Performance Server for Cloud Pak for Data System system implements serializable transaction isolation, which provides the highest level of consistency.
With concurrent transactions, this isolation level prevents the
following:
- Dirty reads where a transaction reads data that is written by concurrent uncommitted transactions
- Non-repeatable reads where a transaction rereads data it previously read and finds that the data was modified by another transaction
- Phantom reads where a transaction re-executes a query that returns a set of rows that satisfy a search condition and finds that the set of rows has changed because of another recently committed transaction
The Netezza Performance Server system
does not use conventional locking to enforce consistency among concurrently
running transactions, but instead uses a combination of multi-versioning
and serialization dependency checking.
- With multi-versioning, each transaction sees a consistent state that is isolated from other transactions that have not been committed. The Netezza Performance Server hardware ensures that the system can quickly provide the correct view to each transaction.
- With serialization dependency checking, nonserializable executions are prevented. If two concurrent transactions attempt to modify the same data, the system automatically rolls back the youngest transaction. This is a form of optimistic concurrency control that is suitable for low-conflict environments such as data warehouses.