HOLD

SQL Server

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.

Oracle

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.