Differences between ORACLE for update and for update

This article transferred from: http://blog.sina.com.cn/s/blog_61cd89f60102e7di.html

In the production database, in order to ensure that the data read, write the uniqueness, often encounter the following five kinds of lock statements, the difference in that? Here we illustrate with examples:

1. For UPDATE

2. For UPDATE of COLUMN

3. For UPDATE WAIT

4. For UPDATE notwait

5. For UPDATE NO WAIT SKIP LOCK

Let's take a look at a few examples below:

1, SELECT * from the EMP for UPDATE; --Lock all rows of the table, read and write only

1 Declare2     cursorEmp_cur3      is4     Select *  fromEmp for Update;--lock all rows, read and write only5 begin6      forXinchEmp_cur Loop7         UpdateEmpSetSal= 9999 where  Current  ofemp_cur;8     EndLoop;9 End;

2. SELECT * from EMP WHERE DEPTNO = ten for UPDATE; --only lines with DEPTNO = 10 locked

1 Declare2     cursorEmp_cur3      is4     SELECT *  fromEmpWHEREDEPTNO= Ten  for UPDATE;--only lines with DEPTNO = 10 locked5 begin6      forXinchEmp_cur Loop7         UpdateEmpSetSal= 9999 where  Current  ofemp_cur;8     EndLoop;9 End;

3. SELECT * from EMP E, DEPT D WHERE e.deptno = D.deptno for UPDATE--locks all records of two tables

4. SELECT * from EMP E, DEPT D WHERE e.deptno = d.deptno and E.deptno = ten for UPDATE; --lock rows that meet the criteria in two tables

5. SELECT * from EMP E, DEPT D WHERE e.deptno = d.deptno and E.deptno = Ten for UPDATE of E.deptno; --only rows that meet the criteria in the EMP table are locked

You can see that for update is to lock all tables, and for update of is to lock the corresponding table according to the conditions behind

1th:

For Single-table operations , the for update and for update is the same . no condition is a lock on the whole table, plus the condition is the locking of the row level . Give me a chestnut:

1. Do not add a where condition is to lock the whole table

SELECT * from EMP for UPDATE; --Lock the entire table

2, plus where condition is lock on row level

SELECT * from EMP WHERE DEPTNO = ten for UPDATE; --Lock only 10 doors that meet the conditions

Remind again for update and for update of the same for single-table operations.

So where is the difference between for update and for update of? When you make a multi-table query , the for update of lock column contains the table row lock, and is a table-level lock, for update is to lock multiple tables, do not understand the example:

1. SELECT * from EMP E, DEPT D WHERE e.deptno = d.deptno and E.deptno = Ten for UPDATE of E.deptno ;

--Lock only the table where the E.deptno is located and lock only the rows of Deptno = 10

2. SELECT * from EMP E, DEPT D WHERE e.deptno = d.deptno and E.deptno = ten for UPDATE;

--Lock multiple tables

The difference between 1 and 2 is that 1 locks the table emp and 2 locks the EMP table and the Dept table, which is the real difference between the two. That is, the for update locks all the tables, and for update of is to lock the corresponding table according to the appropriate conditions

2nd: About NoWait (if you must use for UPDATE, it is more recommended to use nowait)

1, when there is a lock conflict will prompt the error and end statement rather than wait there (for example: To check the row has been locked by other transactions, the current lock transaction with the conflict, plus nowait, the current transaction will end will prompt the error and immediately end the statement and no longer wait).

2. The wait clause specifies the number of seconds to wait for another user to release the lock, preventing an indefinite wait.

The advantages of the use for UPDATE WAIT clause are as follows:
1. Prevent indefinitely waiting for a locked line;
2. Allow more control over the lock's wait time in the application.
3. Useful for interactive applications, because these users cannot wait for indeterminate
4. If skip locked is used, the locked row can be crossed, and the ' Resource busy ' exception report raised by Wait N will not be reported

Differences between ORACLE for update and for update