The database server modifies the information contained in a particular record using the following procedure. As with insertions, this sequence of operations is followed for all transactions regardless of their isolation level.
Acquire a shared schema lock on the table, if one is not already held.
Acquire an intent-to-write table lock for each table to be updated, if one is not already held.
For each table to be updated, if the table has triggers then create the temporary tables for the OLD and NEW values as required.
Identify candidate rows to be updated. As rows are scanned, they are locked.
At isolation levels 2 and 3 the following differences occur that are different from the default locking behavior: intent-to-write row-level locks are acquired instead of read locks, and intent-to-write locks may be acquired on rows that are ultimately rejected as candidates for update.
For each candidate row identified in step 2.a, follow the rest of the sequence.
Write lock the affected row.
Update each of the affected column values as per the UPDATE statement.
If indexed values were changed, add new index entries. The original index entries for the row remain, but are marked as deleted. New index entries for the new values are inserted while a short-term insert lock is held. The server verifies index uniqueness where appropriate.
If a uniqueness violation occurred, a temporary "hold" table is created to store the old and new values of the row. The old and new values are copied to the hold table, and the base table row is deleted. Any DELETE triggers are not fired. Defer steps 7 through 9 until the end of row-by-row processing.
If any foreign key values in the row were altered, acquire a shared schema lock on the primary table(s) and follow the procedure for inserting new foreign key values.
Similarly, follow the procedure for WAIT_FOR_COMMIT if applicable.
If the table is a primary table in a referential integrity relationship, and the relationship's UPDATE action is not RESTRICT, determine the affected row(s) in the foreign table(s) by first acquiring a shared schema lock on the table(s), an intent-to-write table lock on each, and acquire write locks on all the affected rows, modifying each as appropriate. Note that this process may cascade through a nested hierarchy of referential integrity constraints.
Fire AFTER ROW triggers as appropriate.
After the last step, if a hold temporary table was required, each row in the hold temporary table is now inserted into the base table (but INSERT triggers are not fired). If the row insertion succeeds, steps 7-9 above are executed and the old and new row values are copied to the OLD and NEW temporary tables to permit any AFTER STATEMENT UPDATE triggers to correctly process all of the modified rows. After all of the hold rows have been processed, the AFTER STATEMENT UPDATE triggers are fired in order. Upon COMMIT, the server verifies referential integrity by ensuring that the number of orphans produced by this transaction is 0, and release all locks.
Modifying a column value can necessitate a large number of operations. The amount of work that the database server needs to do is much less if the column being modified 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 the column has been declared as 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.
See also |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |