ASA SQL User's Guide
Using Transactions and Isolation Levels
How locking works
INSERT operations create new rows. Adaptive Server Anywhere employs the following procedure to ensure data integrity.
For more information about how locks are used during inserts, see Anti-insert locks.
Make a location in memory to store the new row. The location is initially hidden from the rest of the database, so there is as yet no concern that another transaction could access it.
Fill the new row with any supplied values.
Write lock the new row.
Place an insert lock in the table to which the row is being added. Recall that insert locks are exclusive, so once the insert lock is acquired, no other transaction can block the insertion by acquiring an anti-insert lock.
Insert the row into the table. Other transactions can now, for the first time, see that the new row exists. They can't modify or delete it, though, because of the write lock acquired earlier.
Update all affected indexes and verify both referential integrity and uniqueness, where appropriate. Verifying referential integrity means ensuring that no foreign key points to a primary key that does not exist. Primary key values must be unique. Other columns may also be defined to contain only unique values, and if any such columns exist, uniqueness is verified.
The transaction can be committed provided referential integrity will not be violated by doing so: record the operation in the transaction log file and release all locks.
Insert other rows as required, if you have selected the cascade option, and fire triggers.
You can ensure that all values in a particular column, or combination of columns, are unique. The database server always performs this task by building an index for the unique column, even if you do not explicitly create one.
In particular, all primary key values must be unique. The database server automatically builds an index for the primary key of every table. Thus, you should not ask the database server to create an index on a primary key, as that index would be a redundant index.
A foreign key is a reference to a primary key, usually in another table. When that primary key doesn't exist, the offending foreign key is called an orphan. Adaptive Server Anywhere automatically ensures that your database contains no orphans. This process is referred to as verifying referential integrity. The database server verifies referential integrity by counting orphans.
You can ask the database server to delay verifying referential integrity to the end of your transaction. In this mode, you can insert one row which contains a foreign key, then insert a second row which contains the missing primary key. You must perform both operations in the same transaction. Otherwise, the database server will not allow your operations.
To request that the database server delay referential integrity checks until commit time, set the value of the option WAIT_FOR_COMMIT to ON. By default, this option is OFF. To turn it on, issue the following command:
SET OPTION WAIT_FOR_COMMIT = ON;
Before committing a transaction, the database server verifies that referential integrity is maintained by checking the number of orphans your transaction has created. At the end of every transaction, that number must be zero.
Even if the necessary primary key exists at the time you insert the row, the database server must ensure that it still exists when you commit your results. It does so by placing a read lock on the target row. With the read lock in place, any other transaction is still free to read that row, but none can delete or alter it.