How Locking Works

All Sybase IQ locks occur automatically, based on the type of operation a user requests.

You do not need to request a lock explicitly. The transaction that has access to the table is said to hold the lock.

When a table is locked in Sybase IQ, no other transaction can have write access to it, but any transaction can have read access to it. Data definition operations form an exception to this universal read access; see the discussion below for details. Any other write transaction that attempts to access a table with a write lock on it receives an error.

The locks maintain the reliability of information in the database by preventing concurrent access by other transactions. The database server retains all the locks acquired by a transaction until the transaction completes, due to either a commit or a rollback.

You can reserve WRITE locks on a set of tables within a new transaction using the LOCK TABLE statement. LOCK TABLE commits the current transaction and allows transactions to enqueue until the locks are available. For syntax, see Reference: Statements and Options > SQL Statements > LOCK TABLE Statement.

• Locks for DML Operations
  Data manipulation language (DML) operations include insertions, deletions, updates, and queries. For all such operations, Sybase IQ permits one writer and multiple readers on any given table.
• Locks for DDL Operations
  Data definition language (DDL) operations include CREATE, DROP, and ALTER.
• Primary Keys and Locking
  Because only one user can update a table, primary key generation does not cause concurrency conflicts.