Differences between ORACLE for update and for update nowait

Differences between ORACLE for update and for update nowait
I. Differences between for update and for update nowait
First, Oracle will not apply any locks if it is only select, that is, Oracle will not impose any restrictions on the data read by select, at this time, another process may be modifying the data in the table, and the modification result may affect the results of your current select statement, but because there is no lock, therefore, the select result is the status recorded in the current timetable.
If for update is added, Oracle will not issue this select statement query once it finds that the data (which meets the query conditions) is being modified until the data is modified (commit ), the select statement is automatically executed immediately.
Similarly, if someone needs to modify this batch of data (one or several records) after the query statement is issued, it must wait until the query ends (commit.
Both for update nowait and for update lock the queried result set. The difference is that if another thread is modifying the data in the result set, for update nowait does not wait for resources, as long as some data in the result set is found to be locked, immediately return "ORA-00054 error, content is the resource is busy, but specify to get the resource in NOWAIT mode ".
For update and for update nowait are added with a row-Level Lock, that is, only data that meets the where condition is locked. If you only use the update statement to change the data, it may not respond and wait inexplicably because it is locked, for update NOWAIT statement, you can tentatively lock the data to be changed, and then you can understand the truth through the error prompt returned immediately. Maybe this is the meaning of For Update and NOWAIT.
After testing, the query locks are performed in the for update or for update nowait mode. In the select result set, if any record is locked, the entire result set is waiting for system resources (if nowait is used, an exception is thrown)

II. for update nowait and for update
Lock all rows in the table and reject other write operations on the table. Make sure that only the current transaction writes the specified table.
For update nowait and for update:
When other transactions perform write operations on the table, they are returned after waiting for a period of time or immediately being rejected by the database system. The nowait method is used for retrieval, so when the data is found to be locked by another session, it will quickly return the ORA-00054 error, the content is the resource is busy. Therefore, in the program, we can use nowait to quickly determine whether the current data is locked. If the data is locked, we need to take appropriate business measures for processing.
Instance:
Open a PL/SQL statement and execute update deployop. tt t set t. a = 'X' where t. a = '1 ';

Open another PL/SQL and execute select * from tt t where t. a in ('1', '2') for update nowait; returns a RA-00054 error with the content being busy.
For example, if you execute select * from tt t where t. a in ('1', '2') for update in the window above, the result is only blocked and no error is returned, as shown below:


Iii. SELECT... for update statement syntax
SELECT... for update [OF column_list] [WAIT n | NOWAIT] [skip locked];
Where:
The OF clause is used to specify the columns to be updated, that is, to lock specific columns on the row.
The WAIT clause specifies the number of seconds to WAIT for other users to release the lock, to prevent an indefinite WAIT.
The "for update wait" clause has the following advantages:
1. Prevent waiting for locked rows indefinitely;
2. Allow more control over lock wait time in applications
3. It is very useful for interactive applications because these users cannot wait for uncertainty
4. If skip locked is used, the locked row can be crossed and the "resource busy" exception report caused by wait n is not reported.
Instance:
Open two SQL windows in plsql develope
Run the following SQL statement in window 1:
Select * from t where a = '1' for update;
Run sql1 in window 2
Sql1: select * from t where a = '1'; this is not a problem, because row-level locks do not affect pure select statements.
Run sql2 again
Sql2: select * from t where a = '1' for update; the SQL statement is always in the waiting state during execution, unless the SQL statement in window 1 is submitted or rolled back.
How can I keep sql2 from waiting or waiting for the specified time? Run sql3 again.
Sql3: select * from t where a = '1' for update nowait; when this SQL statement is executed, an exception is reported directly.
If select * from t where a = '1' for update wait 6 is executed, a resource exception is reported after 6 seconds.
If we execute sql4
Sql4: select * from t where a = '1' for update nowait skip Locked; when the SQL statement is executed, the system does not wait, or report a busy resource exception, skipping the Locked row, show unlocked rows only
Example:
Window 1:

Window 2: Only rows with unlocked a = 2 are displayed.


4. select for update
This of clause plays a major role in associating multiple tables. If the column of the Table to be locked is not used, the related rows of all tables are locked, if the column to be modified is specified in of, only the rows of the table associated with these columns will be locked.
Instance 1:
Select * from tt, tt2 where tt. a = tt2.a2 for update; the entire table is locked for both tables.

Select * from tt2 for update wait 3; try to lock the tt2 table, 3 seconds later throw the error "ORA-30006: Resource occupied; WAIT timeout occurred while performing the operation"


Instance 2:
Select * from tt, tt2 where tt. a = tt2.a2 for update of a; only the tt table is locked and the tt2 table is not locked.

Select * from tt2 for update wait 3; the tt2 table is successfully locked.

Select * from tt for update wait 3; Attempt to lock the tt table, 3 seconds later throw the error "ORA-30006: Resource occupied; WAIT timeout occurred while performing the operation"

We can draw a conclusion that for update of columns is used when multiple tables are connected to the lock. You can specify the tables to be locked. If the columns in the table do not appear after for update, this means that this table is not actually locked. Other users can update the data of these tables. This situation often occurs when users perform operations on views with connection queries. The user only locks the data of the relevant table, and other users can still operate on the data of other original tables in the figure.
Summary: