Netezza Table Locking and Concurrency

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:3 mins read

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 table locking and concurrency

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:

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.