Table locking

As a user, you can explicitly lock tables, although you should do so with great caution as accidental locks could impact users and queries on your system. The IBM® Netezza® SQL also implicitly locks a table when there is a DDL operation on it. For example, a drop table command is blocked if somebody is running a select command on the same table (or vice versa).

For concurrent DML operations (select, insert, update, and delete commands), Netezza SQL uses serialization graph checking, which is a form of optimistic concurrency control that does not use locks. Instead, if there is a concurrency conflict, Netezza SQL rolls back one (or sometimes several) of the affected transactions.

• A select command on a table can proceed concurrently with an update, delete, insert, or select command on the same table. Invisibility lists, and other mechanisms, ensure that each transaction sees a consistent state.
• 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 (or deletes from) table B, while transaction 2 selects from table B and updates table A, Netezza SQL rolls back one or the other (typically the transaction that started more recently). This process is called the cross-update case.
  • If there is a cycle of three or more transactions (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.