Neoview SQL Reference Manual (R2.4 SP2)

SERIALIZABLE | REPEATABLE READ
specifies that the DELETE statement and any concurrent process (accessing the same
data) execute as if the statement and the other process had run serially rather than
concurrently.
SKIP CONFLICT
enables transactions to skip rows locked in a conflicting mode by another transaction.
The rows under consideration are the result of evaluating the search condition for the
DELETE statement. You cannot use the SKIP CONFLICT in a SET TRANSACTION
statement.
The default access option is the isolation level of the containing transaction.
Considerations for DELETE
Authorization Requirements
DELETE requires authority read and write to the table or view being deleted from and authority
to read tables or views specified in subqueries used in the search condition.
Transaction Initiation and Termination
The DELETE statement automatically initiates a transaction if no transaction is active. Otherwise,
you can explicitly initiate a transaction with the BEGIN WORK statement. When a transaction
is started, the SQL statements execute within that transaction until a COMMIT or ROLLBACK
is encountered or an error occurs.
Isolation Levels of Transactions and Access Options of Statements
The isolation level of an SQL transaction defines the degree to which the operations on data
within that transaction are affected by operations of concurrent transactions. When you specify
access options for the DML statements within a transaction, you override the isolation level of
the containing transaction. Each statement then executes with its individual access option.
You can explicitly set the isolation level of a transaction with the SET TRANSACTION statement.
See “SET TRANSACTION Statement” (page 187).
Long Running Delete
DELETE operations on a very large data set (the number of rows affected in a single table in a
single transaction) can be long running. This causes the locks to escalate to file locks resulting in
loss of concurrency. The Long Running Delete (LRD) feature executes these DELETE statements
in multiple statements. Each of these multiple statements is executed in a separate transaction,
thus avoiding lock escalation. These multiple transactions are independent transactions from
TMF. Therefore, any point-in-time recovery by TMF is done individually for these multiple
transactions and not for Long Running Delete as a whole.
NOTE: The multicommit-option must only be used for large tables. Using the
multicommit-option on small tables incurs unnecessary overhead from starting and
committing multiple transactions.
NOTE: This option is best used to delete unwanted data (for example, records older than 90
days) from the database and reissuing the query after a failure, which yields the same desired
result (deleting records older than 90 days). Using the multicommit-option for any other
purpose can have point-in-time recovery ramifications since these multiple transactions are
transactions independent from TMF.
The LRD feature is enabled by specifying WITH MULTI COMMIT in the DELETE statement
syntax. For example:
108 SQL Statements