Differences between for update and for update nowait in Oracle

Http://www.cnblogs.com/quanweiru/archive/2012/11/09/2762223.html 1,for update and for update nowait differences:

First of all, if only Select,Oracle will not add any lock, that is, Oracle to Select read the data there is no limit, although it is possible that another process is modifying the data in the table, And the result of the modification may affect the result of your current SELECT statement, but because there is no lock, the Select result is the state recorded in the current timetable.

If a for update is added, Oracle will not issue the SELECT statement query until the data is modified (commit) once it discovers that the batch of data is being modified (which matches the query criteria), and this is done automatically immediately. SELECT statement.

Similarly, if a query statement is issued, someone needs to modify the batch of data (one or more of them), and it must wait until the end of the query (commit) before it can be modified.

The for update nowait and for update will lock the result set that is queried, unlike if another thread is modifying the data in the result set, the forupdate nowait does not wait for the resource. As soon as you find that some data in the result set is locked, you immediately return "ORA-00054 error, content is the resource is busy , but specifies to get the resource NOWAIT".

the for update and for update nowait are added with a row-level lock, which means that only data that meets the where condition is locked. If you only use the UPDATE statement to change the data, it may not be locked up and not respond, inexplicably wait, but if before, the forupdate NOWAIT statement will change the data to be tentatively locked, It can be understood by the error prompt to return immediately, perhaps this is the meaning of for update and nowait.

After testing, for update or for update nowait query Lock, in the result set of SELECT, as long as any one record in the lock, then the entire result set is waiting for system resources (if it is nowait, throws a corresponding exception)

2.for update nowait and for update purposes :
Locks all rows of the table, rejecting other writes against the table. Ensures that only the current transaction writes to the specified table.
differences between for update nowait and for update:

When another transaction is going to write to the table, it waits for some time or is immediately rejected by the database system to return. Develop the adoptionNoWait way to retrieve it, so when the data is discovered by otherWhen the session is locked, it will return quickly.ORA-00054 error, content is resource busy, but specify toNOWAIT way to get resources. So in the program we can useNoWait way to quickly determine whether the current data is locked, if locked, it is necessary to take appropriate business measures to deal with.
How to understand the above words.
Open a session(is to open aSqlwindow)
Select Empno,ename from emp where empno= ' 7369 ' for update nowait;
Get the following result set:
Empno ename
7369 Smith
Open another session
Select Empno,ename from emp where empno= ' 7369 ' for update nowait;
Returns a RA-00054 error with content that the resource is busy , but specifies to get the resource in NOWAIT way
All the above sessions commit commits ;
~~~~~~~~~~~~~~~~~~~~~
Open a session ,
Select Empno,ename from emp where empno= ' 7369 ' for update;
Get the following result set :
Empno ename
7369 Smith
Open another session
Select Empno,ename from emp where empno= ' 7369 ' for update;

block, do not return an error.
Commit the first session, and the second one automatically executes
Submit a second session
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For update: When the firstSession LastCommit orAfter the rollback, a secondThe search results in the session are automatically popped out and the data is locked.
To open a session:
Select Empno,ename from emp where empno= ' 7369 ' for update;
Get the following result set: 
    empno  ename 
    7369     SMITH 
Open another session,  
   update emp set ename= ' ALLEN '   where empno= "7396";  
Block.  
   Submit First session, update  statement execute  
Open a session  
    update emp set ename= "SMITH" where empno= ' 7396 ';  
Also blocked, although the first session released the lock because of the commit, but the second session of the update  to this line is also locked ; 
for update NoWait: When you first session release the lock after session in order to operate properly. When your second session statement runs, the data is locked by your second session statement, as long as you have the second commit, other session can not lock the data to update and so on.

Contrast difference:
SELECT * from TTable1 for update locks all rows of the table, can read only and cannot be written
2 Select * from TTable1 where Pkid = 1 for update locks onlyPkid=1 's Line
3 SELECT * from Table1 a join Table2 B in a.pkid=b.pkid for update lock all records for two tables
4 SELECT * from Table1 a joins Table2 B on a.pkid=b.pkid where a.pkid = Ten for update locks two tables in a row that satisfies the criteria
5. Select * FROM Table1 a joins Table2 B on a.pkid=b.pkid where a.pkid = Ten for update of A.pkid only locksRows that meet the criteria in Table1
For update is to lock all the tables.For update of based onTable corresponding to conditional locking of the after table
-----------
about NOWAIT (if you must use for UPDATE, I would recommend adding NOWAIT)
When there isLock conflict will prompt error and endStatement instead of waiting there(such as: The line to be checked has been locked by another transaction., the current lock transaction conflicts with itPlusNoWait, the current transaction will end with prompt error and end immediatelyStatement and no longer waiting).
If you add aFor update, the statement is used to lock a specific row if there isThe WHERE clause is the one that satisfiesThose rows of the Where condition). When these rows are locked, other sessions can select those rows, but they cannot be changed or deleted until the statement's transaction isThe commit statement or rollback statement ends.
Because the FOR UPDATE clause obtains a lock, commit releases the locks. When the lock is released, the cursor is invalid.
That's the difference.

about Oracle in the select...for update of columns
the problem is as follows:SELECT * from emp where empno = 7369 for update; The record with employee number 7369 in the table is locked. Other users cannot operate on the record, only queries. SELECT * from emp where empno = 7369 for update of SAL; Does this statement mean that only 7369 of the data in the Sal field in the table is locked, and other data can be made by other users Update operation. The result of the student test is the same as two statements. Other users cannot update the entire row, does that mean that the for update of columns is meaningless?

This problem is estimated that many of the students who play Oracle have not thought of "online related posts are not many." Now let's take a look at its features.

Judging from the operation of a single table, the above two statements are actually the same effect. But when it comes to multi-table operations, the for update of columns plays a big role. We now assume that there are two users,Scott and mm.

Scott execution Statement:SELECT * from emp e,dept d where E.deptno = D.deptno for update;--full table locking on two tables
MM Execution Statement:SELECT * from scott.dept for update wait 3;--attempt to lock Scott User's dept table

The result is:
ERROR on Line 1 :
ORA-30006: Resource is already occupied , WAIT timeout occurs while performing operation

Now, theScott user unlocks the rollback and adds the columns after the FOR UPDATE statement to test

Scott executes the statement:SELECT * from emp e,dept d where E.deptno = D.deptno for update of Sal;
MM Execution Statement:SELECT * from scott.dept for update wait 3;

The result is:
The data for the Dept table was successfully locked.

MM EXECUTE statement again:select * from scott.emp for update wait 3;

The result is:
ERROR on Line 1 :
ORA-30006: Resource is already occupied , WAIT timeout occurs while performing operation

Through this code case, we can get the conclusion thatfor update of columns with multi-table connection locking, you can specify which tables you want to lock, and if the columns in the table do not appear after the for update of, it means that the table is not actually locked, and other users can make the data for those tables of the update operation. This situation often occurs when the user is working on a view with a connection query. Users only lock data from related tables, and other users can still manipulate data from other original tables on the view.

Oracle for update row locks
SELECT ... The syntax for the FOR UPDATE statement is as follows:
SELECT ... For UPDATE [of Column_list][wait n| Nowait][skip LOCKED];
Among them:
The of clause is used to specify the column that is about to be updated, that is, a specific column on the lock row.
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 allows more control over the wait time for locks in the application.
3 is useful for interactive applications because these users cannot wait for indeterminate
4 If the skip locked is used, the locked row can be crossed, and the ' resource busy ' exception report raised by Wait n will not be reported

Example:
CREATE TABLE T (a varchar2 (a), B varchar2 (20));
INSERT into T values (' 1 ', ' 1 ');
INSERT into t values (' 2 ', ' 2 ');
INSERT into t values (' 3 ', ' 3 ');
INSERT into t values (' 4 ', ' 4 ');
Now perform the following actions:
InPlsql Develope Open TwoSQL window,
In1 Running in WindowsSql
SELECT * from t where a= ' 1 ' for update;
In2 Running in WindowsSql1
1. Select * FROM t where a= ' 1 '; This is not a problem because row-level locks do not affect purelySELECT statement
Run againSql2
2. Select * FROM t where a= ' 1 ' for update; Then this sentenceSQL is always in a wait state when it executes, unless the window1 inSQL is committed or rolled back.
How can you makeSQL2 do not wait or wait for a specified time? We'll run it again.Sql3
3. Select * FROM t where a= ' 1 ' for update nowait; When you perform thisSQL, directly report the resource busy exception.
If you performSELECT * from t where a= ' 1 ' for update wait 6; Is waiting for theAfter 6 seconds, the resource is reported as busy exception.
If we executesql4 
4. Select * FROM t where a= ' 1 ' for update nowait skip locked;  execute SQL, you do not wait, and you do not report resource busy exceptions.  
Now let's see what happens when we do the following:  
in window 1:  
Select * from t where rownum <=3 nowait skip locked; 
Execute in Window 2:  
Select * from t where Rownum<=6 nowait Skip locked; 
Select for update  is the case, insert, update, delete operate the default row-level lock, the principle and operation of select for update is no different.  
select for update of of clause has a greater effect when implicated in multiple tables, such as not using of to specify columns of locked tables, The related rows of all tables are locked, and if the columns to be modified are specified in of, only the rows of the tables associated with those columns will be locked.

Other articles: Csdn-select for UPDATE statement analysis

Differences between for update and for update nowait in Oracle