SQL Reference

HOLD

HOLD is a SQL clause used within a cursor declaration. It allows the cursor to maintain and access result sets from the previous opened statement even after a commit operation has been executed. This is particularly useful in long running transactions, to avoid data loss in between transactions due to commit operations. The cursor with HOLD will release its resources only when it is explicitly closed or when the connection ends.

Example

BEGIN TRANSACTION;

DECLARE @tempTable TABLE (line VARCHAR(255));

INSERT INTO @tempTable
EXEC ('SELECT TOP 5 name FROM sys.objects WITH (HOLDLOCK) ORDER BY name');

SELECT * FROM @tempTable;

COMMIT;

Output

line
----
sys.all_columns
sys.all_objects
sys.all_parameters
sys.all_sql_modules
sys.all_views

Explanation

In the provided example, a transaction is opened, and a hold lock is applied to the sys.objects table during a SELECT query. The top 5 records are selected and stored in a temporary table. The contents of this table are then displayed, and the transaction is committed. The HOLDLOCK ensures the associated resources are held for the duration of the transaction.

Example

DECLARE
    CURSOR employee_cur IS
        SELECT *
        FROM employees
        WHERE department_id = 90
        FOR UPDATE OF salary NOWAIT;
    employee_rec employee_cur%ROWTYPE;
BEGIN
    OPEN employee_cur;

    FETCH employee_cur INTO employee_rec;

    COMMIT;

    CLOSE employee_cur;

END;

Output

PL/SQL procedure successfully completed.

Explanation

In this example, a PL/SQL block is used to declare a cursor that selects all rows from the employees table where department_id equals 90. The FOR UPDATE OF salary NOWAIT clause applied to this cursor implies that the retrieved rows are locked for potential updates specifically for the salary column. Any other session trying to update these rows will not wait and will receive an error message. The lock is held even after committing the transaction until the cursor is either closed or the session ends.