You cannot explicitly lock the tables in Netezza. The Netezza SQL, however, uses implicit Netezza table locking when there is a DDL operation on it.
For example, drop table command is blocked on the table if a DML commands are running on table and vice versa.
Netezza uses the serializable transaction isolation to lock the table and is ACID property compliant. That ensures no dirty reads, no non repeatable reads.
Read:
- How Netezza Updates Records in Table?
- Netezza Identify and Kill Table Locks
- nzsql Command and its Usage
- Netezza Best Practices to Improve Performance
Netezza Table Locking and Concurrency
IBM Netezza doesn’t use the conventional locking to enforce consistency among concurrently executing transactions. Instead, it uses the combination of multiversioning and Serialization dependency checking:
Multiversioning: In this case, transaction cannot see other data which is not committed in other transactions. Netezza hardware can quickly provide the correct view to each transaction.
Serialization Dependency Checking: This ensures that transactions which are not serializable are not permitted. If two concurrent transaction tries to modify same data, the Netezza system automatically roll backs latest transaction.
Netezza Table Locking
Netezza uses implicit table locking when there is a DDL operation on it, you cannot explicitly lock the table. You can check the table _T_PG_LOCKS to see the Netezza table locks
For concurrent DML operations such including selects, insert, update and delete commands, Netezza uses serialization graph checking. If there is a concurrency conflict, Netezza SQL rolls back one or sometimes several of the affected transactions.
- A select command can proceed concurrently with an update, delete, insert, or select command on the same table.
- More than one concurrent insert command can proceed against the same table, provided no more than one is also selecting from the same table.
- Concurrent update or delete commands against different tables are allowed, with some restrictions that are needed to ensure serializability
For example,
- If transaction 1 selects from table A and updates table B, while transaction 2 selects from table B and updates table A, Netezza SQL rolls back one or the other. This process is called the “cross-update case”.
- If there is a cycle of three or more transactions like transaction 1 selects from A and updates B, transaction 2 selects from B and updates C, transaction 3 selects from C and updates A, the Netezza SQL rolls back one of the transactions in the cycle.