By default, the By clause is not used to indicate lock (GO) in all select data tables

Select ... for The update statement is a manual lock statement that we often use. Typically, the SELECT statement does not lock the data, preventing other DML and DDL operations from being affected. Also, with the support of the multi-version consistent-read mechanism, the SELECT statement is not hindered by other types of statements.

With the FOR UPDATE clause, we can manually implement the data lock protection operation 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 from the Oracle Official documentSQLLanguage Reference: ( Double click to open the picture )

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. The following is a description of the two aspects respectively.

Lock range clauses

After select...for update, you can use the of clause to select a specific data table for select to lock the operation .
By default, the By clause is not used to indicate that locks are added to all the data tables in select .

Use the 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 observe the V$lock and v$locked_object views, and we can see the lock information.

Transaction 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 73181 0 3 0 0

377DB5D0-TX 458771 808 6 0 0

From the above scenario, the FOR UPDATE statement by default is equivalent to initiating a session-level transaction and adding a data table-level shared lock (tm,lmode=3) on the corresponding data table (all data tables involved in 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 at this time to get exclusive permissions for the corresponding data rows (either with Update/delete or another for update), the block will end.

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

Sid

----------

37

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

System blocking

At this point in the system state, switch to another user to observe:

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 Notoginseng AE 100 0 4 0 0

37e80f48 Notoginseng TX 458771 808 0 6 0

B7de8a44 Notoginseng TM 73181 0 3 0 0

B7de8a44 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

Thus, we can get to the conclusion that the default behavior of the FOR UPDATE clause is to automatically start a transaction 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 grammatical structure, the following can be followed by one or more data column lists. This syntax scenario is often used to lock one of the data table data in a select that makes 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 73181 0 3 0 0

377DBC0C-TX 65566 747 6 0 0

In the above statement, we see that after using the for update of the specified data column, the locked range is limited to the data table in which it is located. That is, when we use the join query with the OF clause, we can achieve a targeted lock.

Also in connection with the query, if there is no clause, the same default mode, what happens?

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 73179 0 3 0 0

B7e1c2e8 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 of clause, the default is to lock the data table for all select.

Lock behavior Clause

The lock behavior clause is relatively easy to understand. Here are introduced separately.

NOWAIT clause

When we do the for update operation, it differs greatly from the normal select. The general select does not need to consider whether the data is locked or not, and reads the previous version at most based on the multiple-version consistent read feature. After adding a for update, Oracle requires a new transaction to be started, attempting to lock the data. If it is currently locked, the default behavior must be block waiting.

The use of the NOWAIT clause is to avoid waiting, when the discovery Request lock resource is locked is 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

The session is transformed and executed.

Sql> SELECT * from emp for update nowait;

SELECT * from EMP for update nowait

ORA-00054: The resource is busy, but the resource is specified as nowait, or the timeout expires

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

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

Pick up the case above

Sql> SELECT * FROM EMP for update wait 3;

SELECT * FROM EMP for update wait 3

ORA-30006: Resource is already occupied, wait timeout occurs while performing operation

Skip Locked parameters

The Skip locked parameter is a parameter that was recently introduced into the FOR UPDATE statement. Simply put, if the data row is locked, it skips processing when the data row is locked. This allows the for update to handle locks only on unlocked rows of data.

Session1, a part of the data is added to lock;

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

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

(length reason, omitted)

7934 MILLER Clerk 77821982-1-23 1300.00 10

Rows selected

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

Use of For update

In daily life, our use of the for update is still relatively common, especially in the manual modification of data in the case of PL/SQL developer. It is only convenient at this point and lacks understanding of the true meaning of the for update.

The for update is a special case statement provided by Oracle to manually increase the lock level and range. The lock mechanism of Oracle is the most excellent in the various types of database lock mechanism at present. Therefore, Oracle believes that users and applications are generally not required to directly control and promote the lock. It is even considered that the occurrence of deadlock-related problems is mostly related to the manual lifting lock. Therefore, Oracle does not recommend using the for update as a daily development use. Moreover, in peacetime development and operation, use for update but forget to commit, will cause a lot of lock table failure.

So, when do I need to use for update? Is those that require business-level data exclusivity, consider using the for update. Scenes, such as train ticket bookings, display stamps on the screen, and when the ticket is actually issued, you need to re-determine that the data has not been modified by other clients. So, in this confirmation process, you can use the for update. This is a unified solution solution problem that requires prior preparation

http://blog.csdn.net/liqfyiyi/article/details/7948282

By default, the By clause is not used to indicate lock (GO) in all select data tables