When an update or delete is issued against a row that participates in the index, the database will attempt to secure an exclusive lock on the row. This requires the task to check if any shared locks are held against the row, as well as check on any index nodes that will be affected. Many indexing algorithms allow for the index tree to dynamically change shape, spawning new levels as items are added and condensing levels as items are deleted.

Because most commercial databases only issue automatic locks against a row when they need to lock a row, programmatic solutions can be used to minimize the amount of locking that is used for very large update tasks. For example, in Oracle SQL, a programmer can use the SELECT...FOR UPDATE clause to explicitly lock a row or a set of rows prior to issuing the UPDATE operation. This will cause the database to issue exclusive locks (sometimes called pre-emptive locks) at the time of retrieval, and hold these exclusive locks until the task has committed or ended. In the following SQL, an exclusive lock is placed upon the target row, and no other task will be able to retrieve that row until the update operation has completed:

SELECT *
    FROM EMPLOYEE
    WHERE emp_name = 'Burleson'
    FOR UPDATE OF SALARY;

For large updates, statements can be issued to lock an entire table for the duration of the operation. This is useful when all rows in the table are going to be affected, as in the following salary adjustment routine:

LOCK TABLE emp_table IN EXCLUSIVE MODE NOWAIT;

UPDATE emp_table
    SET salary = salary * 1.1;

Sometimes the application will want to update all of the rows in a table, but it is not practical to lock the entire table. An alternative to the exclusive update is to use the Oracle’s SQL FETCH statement to lock a small segment of the table, perform the update, and then release the locks with a COMMIT statement, as shown in Listing 5.1.

Listing 5.1 Using the FETCH statement.

DECLARE     CURSOR total_cursor IS
    SELECT emp_name FROM emp_table;

DECLARE CURSOR update_cursor IS
    SELECT ROWID
    FROM emp_table
    WHERE emp_name = :my_emp_name
    FOR UPDATE OF SALARY;

BEGIN
    count = 0;
    OPEN total_cursor;

    begin_loop;

        OPEN update_cursor;
        FETCH total_cursor INTO :my_emp_name;

        FETCH update_cursor INTO :my_rowid;

        IF (update_cursor%found) THEN
        {
            UPDATE emp_table
                SET salary = salary * 1.1
            WHERE
                ROWID = :my_rowid;

            COUNT++;

            IF (COUNT = 20) THEN
            {
                COMMIT;
                COUNT = 0;
            }
        }
}
CLOSE update_cursor;
CLOSE total_cursor;
END;

As we see from the Listing 5.1, the locks are set as the rows are fetched, 20 at a time, and then released with a COMMIT. This technique consumes less memory in the lock pool and also allows other SQL statements to access other rows in the table while the update is in progress. Of course, if this code should fail, it would need to be restarted from the point of the last COMMIT statement. This would require additional logic to be inserted into the update program to record the row ID of the last COMMITted row, and to restart the program from that row.

Escalation Of Locks With Non-Oracle Servers

Some databases attempt to alleviate locking problems by performing “lock escalation.” Lock escalation increases the granularity of the lock in an attempt to minimize the impact on the database lock manager. In a relational database, the level of locking is directly proportional to the type of update that is being executed. Remember, for row-level locking, a lock must be placed in the lock storage pool for every row that the SQL statement addresses. This can lead to very heavy resource consumption, especially for SQL statements that update numerous records in many tables. For example, an SQL query that selects many (but not all) of the records in the registration table might state:

SELECT *
FROM REGISTRATION
    WHERE
    REGISTRATION.grade = 'A';

Depending on the number of students affected, this type of query will begin to hold row locks until the task has successfully completed—or until the SQL statement terminates because of a lack of space in the lock pool. However, if the database supports lock escalation, the database will set a single lock for the entire table, even if only a portion of the rows in registration are affected.

In databases such as DB2, a statement such as SELECT * FROM REGISTRATION, to return all rows in the registration table, will cause DB2 to escalate from row-level locking to page-level locking. If the REGISTRATION table resides in a single tablespace, some database engines will escalate to tablespace-level locking. This strategy can greatly reduce strain on the lock pool; but some lock mechanisms will escalate locks, even if it means that some rows will be locked although they are not used by the large task. For example,

SELECT * FROM EMPLOYEE WHERE DEPARTMENT = 'MARKETING'

may cause the entire EMPLOYEE table to lock, preventing updates against employees in other departments.

Whenever possible, large SQL updates could be run using table-level locks, thereby reducing resource consumption and improving the overall speed of the query. Some implementations of SQL provide extensions that allow for the explicit specification of the locking level and granularity. This mechanism could allow exclusive locks to be placed on a result set if the user intends to update the rows, or to turn off shared locks if the rows will never be updated.

In all relational databases, the engine must be sure that a row is “free” before altering any values within the row. The database accomplishes this by issuing an exclusive lock on the target row. The exclusive lock mechanism will then sweep the internal “lock chain” to see if shared locks are held by any other tasks for any rows in the database. If shared locks are detected, the update task will wait for the release of the shared locks until they are freed or until the maximum wait time has been exceeded. While the task is waiting for the other tasks to release their locks, it is possible that one of these tasks may issue an update. If this update affects the original task’s resources, a database deadlock will occur, and the database will abort the task that is holding the least amount of resources.

Unlike object, network, or hierarchical databases that update a single entity at a time, a relational database may update hundreds of rows in a single statement. For example:

UPDATE REGISTRATION
SET REGISTRATION.grade = 'A'
WHERE
course_id = 'CS101'
    AND
    COURSE.instructor_id = 'BURLESON';