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. This rule has the following implications:
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.
Sybase IQ supports SHARE, WRITE, and EXCLUSIVE lock enqueuing, allowing you to lock a table for a specified period. You can WRITE lock multiple tables at one time.
To avoid future version errors from subsequent DML statements, reserve a WRITE lock on the table or set of tables that you plan to modify. For details and syntax, see LOCK TABLE statement in Chapter 1, “SQL Statements,” in Reference: Statements and Options.
In the case of deadlocks, the last LOCK TABLE statement that became blocked is usually rolled back and an error returns to that transaction about the form of deadlock that occurred.
In certain cases, you must issue COMMIT or ROLLBACK statements. If SYNCHRONIZE JOIN INDEX fails due to “table x has no data with which to join the other tables,” all database tables that participate in the join indexes and join virtual tables remain locked in WRITE mode until you explicitly disconnect or issue a COMMIT or ROLLBACK statement. Explicit COMMIT or ROLLBACK is also required to release locks when DML statements fail for example, due to integrity constraints.
If a DML statement fails due to a referential integrity violation on the referenced table or an unavailable lock on other tables, Sybase IQ returns SQL Anywhere Error -210.
