"JAVA" for update and for update NOWAIT differences

1. The difference between for update and for update nowait:
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) and automatically executes the SELECT statement as soon as it discovers that the batch of data (matching the query criteria) is being modified.
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, for update nowait does not wait for the resource, as long as it finds some data in the result set is locked Immediately returns the "ORA-00054 error, content is that the resource is busy, but specifies to get the resource in NOWAIT way."
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 simply change the data with the UPDATE statement, you may not be able to wait for it to be unlocked and not be answered, but if the for UPDATE NOWAIT statement is tentatively locked to the data that is about to be changed, it can be understood by an error prompt that returns immediately. Maybe that's what the for update and nowait mean.
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, throw the 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 immediately to be rejected by the database system. The NoWait method is used to search, so when the data is found locked by another session, it will quickly return to the ORA-00054 error, the content is the resources are busy, However, you specify that you get the resource in NOWAIT mode. So in the program we can use the NoWait method 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 (just open a sqlwindow)
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: After the last commit or rollback of the first session, the search results in the second 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";
Blocking.
Commits the first session, the UPDATE statement executes
Open a session again
Update emp Set ename= "SMITH" where empno= ' 7396 ';
It also blocks, although the first session releases the lock because of the commit, but the update in the second session locks the row again;
For update nowait: When the first session is unlocked, the second session will function correctly. When you run the second session statement, the data is locked by your second session statement, this time as long as you have no commit after the second session statement, the 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 only pkid=1 rows
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 locks only rows that meet the criteria in Table1
For update is to lock all tables for update of the table corresponding to the condition of the following table
-----------
About NoWait (if you must use for UPDATE, I would recommend adding nowait)
When there is a lock conflict will prompt the error and end statement instead of waiting there (for example: The row to be checked has been locked by other transactions, the current lock transaction conflicts with it, plus nowait, the current transaction will end will prompt the error and immediately end the statement and no longer wait).
If a for update is added, the statement is used to lock a specific row (if there is a WHERE clause, those that satisfy the Where condition). When these rows are locked, other sessions can select the rows, but the rows cannot be changed or deleted until the statement's transaction is terminated by a commit statement or a ROLLBACK statement.
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 select...for update of columns in Oracle
The problem is as follows: SELECT * from emp where empno = 7369 for update; The record for 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 of this row in the table is locked, and other data can be updated by other users? 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 sheets
MM Execution Statement: SELECT * from scott.dept for update wait 3; --An attempt to lock the Scott User's Dept table
The result is:
ERROR on line 1th:
ORA-30006: Resources have been occupied; WAIT timeout occurs while performing an action
Now, the Scott 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 1th:
ORA-30006: Resources have been occupied; WAIT timeout occurs while performing an action
With this code case, we can conclude that for update of columns when using a multi-table connection lock, you can specify which tables 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. Other users are able to update the data for these tables. 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];
which
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:
Open two SQL windows in Plsql develope,
Running SQL in the 1 window
SELECT * from t where a= ' 1 ' for update;
Run SQL1 in the 2 window
1. Select * FROM t where a= ' 1 '; This is not a problem because row-level locks do not affect pure SELECT statements
Run SQL2 again
2. Select * FROM t where a= ' 1 ' for update; This sentence of SQL is always in a wait state when it executes, unless SQL is committed or rolled back in window 1.
How can I get sql2 to wait or wait for a specified time? We'll run Sql3 again.
3. Select * FROM t where a= ' 1 ' for update nowait; The exception that the resource is busy is reported directly when the SQL is executed.
If you execute select * from t where a= ' 1 ' for update wait 6; After waiting for 6 seconds, the resource is reported as busy exception.
If we execute SQL4
4. Select * FROM t where a= ' 1 ' for update nowait skip Locked; When SQL is executed, it does not wait or report a resource busy exception.
Now let's see what happens when we do the following:
In Windows 1, do the following:
SELECT * FROM t where rownum<=3 nowait skip Locked;
In Windows 2, do the following:
SELECT * FROM t where rownum<=6 nowait skip Locked;
The Select for update is the same, and the INSERT, update, and delete operations are added by default to row-level locks, and the principle and operation are no different than the select for update.
Select for update of, which has a greater effect when implicated in multiple tables, such as not using the column of the specified locked table, all related rows of all tables are locked, and if the column to be modified in is specified, only the rows of the tables associated with those columns will be locked.

JAVA for update and for update NOWAIT differences (RPM)