Differences between for update and for update no wait in Oracle-Alibaba Cloud

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

First, the difference between the for update and for update no wait

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 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 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 no wait and for the 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 no wait is added with a row-level lock, which means that only data that meets the where the 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 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 no wait mean. The
has been tested for query locking with the for an update or for update no wait, and the entire result set waits for system resources (if no wait, throws an appropriate exception) in the result set of the Select, as long as anyone record is locked.
Second. For update no wait 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 no wait 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 it. 

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 no wait; Returns an 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 the 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 develop
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 no wait; 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 the busy exception.
If we execute SQL4
Sql4:select * from T where a= ' 1 ' for update no wait 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 the update 

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.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值