ASA SQL User's Guide
Using Transactions and Isolation Levels
How locking works
The database server modifies the information contained in a particular record using the following procedure.
Write lock the affected row.
If any entries changed are included in an index, delete each index entry corresponding to the old values. Make a record of any orphans created by doing so.
Update each of the affected values.
If indexed values were changed, add new index entries. Verify uniqueness where appropriate and verify referential integrity if a primary or foreign key was changed.
The transaction can be committed provided referential integrity will not be violated by doing so: record the operation in the transaction log file, including the previous values of all entries in the row, and release all locks.
Cascade the insert or delete operations, if you have selected this option and primary or secondary keys are affected.
You may be surprised to see that the deceptively simple operation of changing a value in a table can necessitate a rather large number of operations. The amount of work that the database server needs to do is much less if the value you are changing is not part of a primary or foreign key. It is lower still if it is not contained in an index, either explicitly or implicitly because you have declared that attribute unique.
The operation of verifying referential integrity during an UPDATE operation is no less simple than when the verification is performed during an INSERT. In fact, when you change the value of a primary key, you may create orphans. When you insert the replacement value, the database server must check for orphans once more.