Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 320 / 536

How locking works
300
• Read transactions do not block write transactions.
• Write transactions do not block read transactions.
• A single update user and multiple read-only users can concurrently access
a table.
• Only a single user can update the data in a given table at one time.
The first transaction to open a table in write mode gains access to the table. A
second transaction that tries to open the table in write mode receives an error.
Any additional attempts to write to the table in the current transaction will fail.
The transaction can continue, but only with read operations or with writes to
other tables.
Locks for DDL operations
Data Definition Language (DDL) operations include
CREATE
,
DROP
, and
ALTER
. DDL operations on a given table or index lock out all other readers and
writers from any table being modified. This approach is crucial to the accuracy
of query results. It ensures, for example, that a table column does not disappear
from the database while you are selecting data from that column.
CREATE
,
DROP
, and
ALTER
commands have the following special properties:
• They cannot start while any other transaction is using the table or index
they are modifying.
• They cannot start while any other DDL command is operating in the
database. However, this restriction is in force for only a few seconds
during the operation.
• They include an automatic
COMMIT
on completion.
• Existing transactions that try to use the database object being modified
receive an error. In other words, if you are accessing an object, and a DDL
command changes that object, your command fails.
• At any given time, only one of the commands
CREATE DBSPACE
,
DROP
DBSPACE
, and
CHECKPOINT
can be executing in a database.
• They cannot execute while an IQ Multiplex is in multiplex mode. The
query servers must be stopped and the write server placed in simplex mode
to execute DDL commands.
If more than one DDL command is attempted at the same time, users may get
this error message: