On Oracle Select for Update____oracle

1, the problem is: if the multiple threads under the same time query a data is not found, then go to insert, insert the time is also a multi-threaded insert.

So think of using select for update to control not allowing multithreaded inserts (in fact, this way does not solve the problem)

2, first to understand a few nouns:

Statement: An SQL statement.

Session: An Oracle user-generated connection in which a user can produce multiple sessions, but is independent of each other.

Transaction: All changes can be divided into transaction, and a transaction contains one or more SQL. When a session is established, it is a time when the transaction begins, and then transaction open

The beginning and end are controlled by DCL, i.e. each commit/rollback is marked with the end of a transaction.

3. Usage Introduction:

Select ... for update will lock the corresponding row.

Only one transaction can lock the corresponding line, which means that if a row is locked, it cannot be locked by the other transaction.

Lock is generated by statement but ends with transaction (Commit,rollback), which means that lock will exist after a SQL is completed, and only after the Commit/rollback is released.

The use of this row lock is generally the case of concurrent requirements are relatively high, the need to lock a row to do some update statements after the release, and then let other transaction to operate. Good use of this to solve the concurrent question

Problem. Lock type in database: There are two basic types of locks, exclusive locks (Exclusive Locks, x locks) and shared locks (Share Locks, or S locks). When a data object is added to the exclusive lock, the other transaction cannot read to it and

Modify. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic lock types to concurrently control the transactions of the database.

4, Oracle to select result set lock, skip Locked (skip Gathing obtain can be locked result set), please refer to: Click to open the link

5, for update can also be followed by [of Cols] [nowait]

The use of is primarily for multiple table associations, and if you do not use the, the rows involved in the two tables will be locked, using the to specify which table to lock.

For example: Select A.mobile,b.name from connector a,student b where a.stu_id=b.id and a.mobile= ' 13937134399 ' for Updata of A.mobil E

In this case, the corresponding row in the student table is unlocked, connector a table, and does not use two tables to lock.

The use of [nowait] is prompted when a lock conflict occurs:

When there is a lock conflict, it prompts for an error and ends the statement instead of waiting there. The return error is "Ora-00054:resource busy and acquire with nowait specified" and waits if not used, Executes until after the lock is released. When debugging on the page due to abnormal handling is not good, the data locked not submitted, nor rollback, when the situation can be resolved by the following ways:

-----View the serial number of the locked object, sid
SELECT o.owner,o.object_name,o.object_type,s.sid,s.serial#
From V$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
and L.session_id=s.sid
ORDER BY O.object_id,xidusn DESC
/
------using SID and serial number deletion
Alter system kill session ' 243,10265 ';
243 is Sid 10265 is the serial number

6, the effect chart demonstration

Open two windows:

1 First window with select for Update query

2 in another window with the same statement query, will show the query has been ... The description was blocked.

3, click on the first window of the Submit Transaction button, another window can immediately get the results of the query

7. Go back to the question we started, why use for update does not avoid problems with multithreaded inserts because: Select for update can only have exclusive locks on existing data, if the query data is

NULL, there is no such thing as a lock. One of the solutions is: Add uniqueness constraints. We're going to give Open_uid and login_type the uniqueness constraint unique. So the database guarantees that there is only one

Uniquely determined records, when two requests insert the same open_uid and login_type into the database at the same time, a preemptive insert is used, and no data can be inserted until the other side is inserted.

The exception that is the only constraint is reported:

###
### ### Error Updating database. Cause:java.sql.sqlintegrityconstraintviolationexception:ora-00001:unique constraint (Slprod. sys_c0019132) violated
### The error occurred while setting parameters
### Sql:insert into Sl$third_login (Open_uid, Request_source, Login_type, Bind_aid, Nick_name, ACCESS_TOKEN, EXPIRES_IN,       UNION_ID) VALUES (?,?,?,?,?,?,?,? )
### cause:java.sql.sqlintegrityconstraintviolationexception:ora-00001:unique constraint (SLPROD. sys_c0019132) violated
; SQL []; Ora-00001:unique constraint (Slprod. sys_c0019132) violated
; Nested exception is Java.sql.sqlintegrityconstraintviolationexception:ora-00001:unique constraint (Slprod. sys_c0019132) violated
... More
caused By:java.sql.sqlintegrityconstraintviolationexception:ora-00001:unique constraint (SLPROD. sys_c0019132) violated

Reference: http://www.blogjava.net/sunchaojin/archive/2007/05/14/117327.html