Sybase 12.4.2 manual Locks for DDL operations, How locking works, 300

How locking works

•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:

300