A brief analysis of Select for UPDATE statement

Select ... for The update statement is a manual locking statement that we often use. Typically, a SELECT statement does not lock data, preventing other DML and DDL operations from affecting others. At the same time, the SELECT statement is not hindered by other types of statements, supported by the multiple-version consistent read mechanism.

With the FOR UPDATE clause, we can manually implement data lock-protection operations at the application level. In this article we will introduce the usage and function of this clause.

The following is a description of the FOR UPDATE clause in the official Oracle documentSQLLanguage Reference: (Double click to open a picture to view )

From the syntax state diagram of the FOR UPDATE clause, we can see that the clause is divided into two parts: a lock range clause and a lock behavior clause. Here we introduce the two aspects respectively.

Lock RANGE Clause

After select...for update, you can use the By clause to choose to lock specific data tables for a SELECT. By default, no by clause is used to represent locking in all data tables in the Select.

Use default format for update

Sql> SELECT * from emp where rownum<2 for update;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH Clerk 79021980-12-17 800.00 20

At this point, we look at the V$lock and V$locked_object views to see the lock information.

Transactional Information View

Sql> select addr,xidusn,xidslot,xidsqn from V$transaction;

ADDR Xidusn Xidslot xidsqn

-------- ---------- ---------- ----------

377DB5D0 7 19 808

Lock Object Information

Sql> Select xidusn,xidslot,xidsqn,object_id,session_id, oracle_username from V$locked_object;

Xidusn xidslot xidsqn object_id session_id oracle_username

---------- ---------- ---------- ---------- ---------- ------------------------------

7 808 73181 SCOTT

//

Sql> Select Owner,object_name from dba_objects where object_id=73181;

OWNER object_name

------------------------------ ------------------------------------------------------------

SCOTT EMP

//

sql> Select addr, sid, type, Id1,id2,lmode, request, block from V$lock where sid=36;

ADDR SID TYPE ID1 ID2 lmode REQUEST block

-------- ---------- ---- ---------- ---------- ---------- ---------- ----------

37E808F0 AE 100 0 4 0 0

B7de8a44 TM 73181 0 3 0 0

377DB5D0 TX 458771 808 6 0 0

From the above scenario, for the UPDATE statement by default, the effect is equivalent to starting a session-level transaction and adding a data table-level shared lock (tm,lmode=3) on the corresponding datasheet (all the data tables involved in the Select). Also, an exclusive lock (tx,lmode=6) is added to the corresponding data row.

According to our previous knowledge, if there is another session view that obtains exclusive permissions on the corresponding data row (whether with Update/delete or another for update), it will end in block.

Sql> select Sid from V$mystat where rownum<2;

Sid

----------

37

Sql> SELECT * from emp where empno=7369 for update;

System blocking

The state in the system is now switched to another user for observation:

sql> Select addr, sid, type, Id1,id2,lmode, request, block from V$lock where SID in (36,37);

ADDR SID TYPE ID1 ID2 lmode REQUEST block

-------- ---------- ---- ---------- ---------- ---------- ---------- ----------

37E808F0 AE 100 0 4 0 0

37E80ED4 100 0 4 0 0

37e80f48 Notoginseng TX 458771 808 0 6 0

B7de8a44 73181 0 3 0 0

B7de8a44 TM 73181 0 3 0 0

377DB5D0 TX 458771 808 6 0 1

6 Rows selected

Sql> select * from Dba_waiters;

Waiting_session holding_session lock_type Mode_held mode_requested Lock_id1 Lock_id2

--------------- --------------- -------------------------- ---------------------------------------- --------------- ------------------------- ---------- ----------

Panax 36Transaction Exclusive Exclusive 458771 808

From this we can get to the conclusion that the default behavior of the FOR UPDATE clause is to start a transaction automatically and lock the data with the lock mechanism of the transaction.

The of clause is a range description tag that is used with the FOR UPDATE statement. From the official syntax structure, you can follow one or more lists of data columns at a later date. This syntax scenario is often used to lock one of the data table data in the select of a connection query.

Sql> Select Empno,ename,job,mgr,sal from emp,dept where Emp.deptno=dept.deptno and empno=7369 for update of Emp.empno;

EMPNO ename JOB MGR SAL

----- ---------- --------- ----- ---------

7369 SMITH Clerk 7902 800.00

sql> Select addr, sid, type, Id1,id2,lmode, request, block from V$lock where sid=36;

ADDR SID TYPE ID1 ID2 lmode REQUEST block

-------- ---------- ---- ---------- ---------- ---------- ---------- ----------

37E808F0 AE 100 0 4 0 0

B7e1c2e8 TM 73181 0 3 0 0

377dbc0c TX 65566 747 6 0 0

In the above statement, we see that the scope of the lock is limited to the data table in which you specify the data column using for update. That is, when we use the join query to match the of clause, we can achieve targeted locking.

Also in connection with the query, if there is no clause, the same default mode, what will happen.

Sql> Select Empno,ename,job,mgr,sal from emp,dept where Emp.deptno=dept.deptno and empno=7369 for update;

EMPNO ename JOB MGR SAL

----- ---------- --------- ----- ---------

7369 SMITH Clerk 7902 800.00

sql> Select addr, sid, type, Id1,id2,lmode, request, block from V$lock where sid=36;

ADDR SID TYPE ID1 ID2 lmode REQUEST block

-------- ---------- ---- ---------- ---------- ---------- ---------- ----------

37E808F0 AE 100 0 4 0 0

B7e1c2e8 TM 73179 0 3 0 0

B7e1c2e8 TM 73181 0 3 0 0

377dbc0c TX 458777 805 6 0 0

Sql> Select Owner,object_name from dba_objects where object_id=73179;

OWNER object_name

------------------------------ --------------------------------------------------------------------------------

SCOTT DEPT

It is obvious that when we do not use the clause, the default is to lock all the select data tables.

Lock behavior Clause

The lock behavior clause is relatively easy to understand. This is described separately here.

NOWAIT clause

When we do a for update operation, there is a big difference from the normal select. A general select does not need to consider whether data is locked or not, up to the previous version of the read-only feature that is consistent with multiple versions. After adding for update, Oracle asks to start a new transaction, attempting to lock the data. If the current lock is already locked, the default behavior must be block waiting.

The use of nowait clause is to avoid waiting, when the discovery request lock resources are locked not released, the direct error returned.

In Session1

Sql> SELECT * from EMP for update;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH Clerk 79021980-12-17 800.00 20

7499 ALLEN salesman 76981981-2-20 1600.00 300.00 30

7521 WARD salesman 76981981-2-22 1250.00 500.00 30

7566 JONES MANAGER 78391981-4-2 2975.00 20

Transforms the session, carries on the execution.

Sql> SELECT * from emp for update nowait;

SELECT * from EMP for update nowait

ORA-00054: Resource is busy, but specifies to get resources in nowait mode, or timeout expired

The corresponding is also the wait clause, which is the default for update behavior. Once the corresponding resource is found locked, wait for blocking until the resource is freed or the user forces the command to terminate.

There is also a bit of data parameter for the WAIT clause, which indicates how long to wait for the blocking to occur. Unit is the second level.

Take the case above

Sql> SELECT * from emp to update wait 3;

SELECT * FROM emp to update wait 3

ORA-30006: Resource has been occupied, wait timeout occurred while performing action

Skip Locked parameters

The Skip locked parameter is the most recent one of the parameters introduced into the FOR UPDATE statement. Simply put, when you lock a data row, if you find that the data row is locked, skip processing. The for update handles locks only on unlocked data rows.

In the Session1, a part of the data is locked;

Sql> SELECT * from emp where rownum<4 for update;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH Clerk 79021980-12-17 800.00 20

7499 ALLEN salesman 76981981-2-20 1600.00 300.00 30

7521 WARD salesman 76981981-2-22 1250.00 500.00 30

In the Session2;

Sql> SELECT * from EMP for update skip locked;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

(Space reason, omitted)

7934 MILLER Clerk 77821982-1-23 1300.00 10

Rows selected

Total data 14 lines altogether. In Session1, 3 rows of data were locked first. In the Seesion2, the remaining 11 data is read and locked due to the skip locked clause parameter used.