Differences between for update and for update nowait in Oracle

Original source http://bijian1013.iteye.com/blog/1895412

First, the difference between the for update and for update nowait

First of all, if only select, Oracle will not add any locks, that is, Oracle to select There is no limit to the data read, 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 has been modified (commit) and automatically executes the SELECT statement as soon as it discovers that the batch of data (which matches the query criteria) is being modified.
Similarly, if someone needs to modify the batch of data (one or more of them) after the query statement is issued, 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 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, which 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. The
has been tested for query locking with the for update or for update nowait, and the entire result set waits for system resources (if nowait, throws an appropriate exception) in the result set of the Select, as long as any one record is locked.

Second. 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 returned by the database system to reject it. The NoWait method is used for retrieval, so when the data is found to be locked by another session, it will quickly return to the ORA-00054 error, the content is the resources are busy. 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.

Instance:

Open a PL/SQL, perform update deployop.tt t set t.a = ' X ' where t.a = ' 1 ';

Open another PL/SQL, execute SELECT * from TT t where t.a in (' 1 ', ' 2 ') for update nowait; Returns a RA-00054 error with content that the resource is busy.
As in the above window, execute SELECT * from TT t where t.a in (' 1 ', ' 2 ') for update, the result is just blocking and does not return an error as follows:

Third. SELECT ... Syntax for the FOR UPDATE statement

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. 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 is not reported

Example:

Open two SQL windows in Plsql develope
Run the following SQL in the 1 window
SELECT * from t where a= ' 1 ' for update;
Run SQL1 in the 2 window
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; 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.
Sql3: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
Sql4:select * from T where a= ' 1 ' for update nowait skip Locked; When you execute SQL, you do not wait, do not report resource busy exceptions, skip locked rows, show only unlocked rows

Example:

Window 1:

Window 2: The row of the locked a=1 is skipped, only the rows of the unlocked a=2 are displayed

Fourth. Select for update of

This clause, when implicated in multiple tables, has a greater effect, such as not using the column of the specified locked table, all related rows of the table are locked, and if the column to be modified is specified in the, only the rows of the tables associated with those columns will be locked.



Example 1:

SELECT * from tt,tt2 where tt.a = tt2.a2 for update; full table locked for two tables

SELECT * FROM TT2 for update wait 3; Attempt to lock tt2 table, throw "ORA-30006: resource is occupied; Wait timeout occurs while performing operation" error after 3 seconds


Example 2:

SELECT * from tt,tt2 where tt.a = tt2.a2 for update of A; only the TT table is locked, the TT2 table is not locked

SELECT * FROM TT2 for update wait 3; TT2 table successfully locked

SELECT * from the TT for update wait 3; Attempt to lock the TT table, throw "ORA-30006: resource is occupied; Wait timeout occurs while performing operation" error after 3 seconds

It can be concluded 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.

Differences between for update and for update nowait in Oracle

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch