11.7 How do I…Lock rows when I query them?Problem
In my programs, I have procedures that perform queries and later update the results. I need to ensure that other users cannot update the records while the procedures are processing data. How do I lock rows when I query them?
Technique
The FOR UPDATE clause of a SELECT statement locks the rows returned by the query until a COMMIT statement is executed. When the FOR UPDATE clause is used in a query, another user cannot update, delete, or lock the rows until the lock is released. If the rows you are attempting to query for update are locked, the query will wait until the lock is released, unless the NO WAIT clause is specified.
Step
1. Run SQL*Plus and connect as the WAITE user account. CHP11_17.SQL, shown in Figure 11.15, creates the sample tables used in this How-To.
The sample table is a simple table containing the departments in an organization. Data used to demonstrate the locking capabilities of the FOR UPDATE clause is created. Run the file to create the sample table and data.
SQL> START CHP11_17.sql
Table dropped.
Table created.
Table dropped.
Table created.
1 row inserted.
1 row inserted.
1 row inserted.
Commit complete.
2. Load CHP11_18.SQL into the SQL buffer. The file contains a query that locks the rows as they are queried.
SQL> GET CHP11_18.sql
1 SELECT DEPT_NO
2 FROM
3 DEPT11
4 WHERE
5 DEPT_NO = 25
6* FOR UPDATE
Lines 4 and 5 specify the WHERE clause identifying which rows the query will retrieve. Line 6 tells Oracle to lock the rows retrieved by the query.
3. Run the query to return the results and lock the rows.
SQL> /
DEPT_NO
-------
25
4. Load CHP11_19.SQL into the SQL buffer. The file contains a query of multiple tables but only locks one of them.
SQL> GET CHP11_19.sql
1 SELECT DEPT11.DEPT_NO, EMP_NO
2 FROM DEPT11, EMP11
3 WHERE
4 DEPT11.DEPT_NO = EMP11.DEPT_NO
5* FOR UPDATE OF DEPT11.DEPT_NO
The FROM clause in line 2 specifies the two tables used in the query. Line 4 contains the join condition between the two tables. Line 5 specifies that only DEPT11 will have rows returned by the query locked. Rows from EMP11 will not be locked and can be updated by another process.
5. Run the query to lock the rows.
SQL> /
DEPT_NO
EMP_NO
---------
---------
25
1
26
2
The query locks the DEPT11 records returning the two rows.
6. Execute a COMMIT statement to unlock the rows. When rows are locked using the SELECT statement with a FOR UPDATE clause, they remain locked until a COMMIT or ROLLBACK statement is executed or until the session is terminated.
COMMIT;
How It Works
Step 1 creates two sample tables and records used throughout this How-To. Steps 2 and 3 use the FOR UPDATE clause in a SELECT statement to lock all rows returned by the query. The query only has a single source table, so the FOR UPDATE clause knows which table to lock. Steps 4 and 5 demonstrate a query from multiple tables, only one of which is locked. The table name is specified in the FOR UPDATE clause, telling Oracle to lock only that table. If the table name is not specified in the clause, records from both tables are locked. Step 6 unlocks any locked records by executing a COMMIT statement.
Comments
If you are planning to update a record that will be first returned by a query, it is a good idea to lock the record to prevent other users from updating it. Remember to unlock the record if you decide not to commit the changes you make.