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 a transaction begins, after which the beginning and end of transaction is controlled by DCL, i.e. each commit/rollback is marked with the end of a transaction.
Consistency: It's for the statement level, not the transaction level. The data that SQL statement gets is the image that starts with SQL statement.
The basic situation of lock:
UPDATE, INSERT, delete, select ... for update to 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 still exist after one SQL is completed, and will only be release after Commit/rollback.
SELECT .... For UPDATE [of cols] [nowait];
of cols
SELECT cols from tables [WHERE ...] For UPDATE [of cols] [nowait];
For the previous for update omitted, let's talk about the.
Transaction a Run
Select a.object_name,a.object_id from wwm2 a,wwm3 b
2 where b.status= ' VALID ' and a.object_id=b.object_id
3* for update of A.status
Then transaction B can perform a DML operation on the corresponding row in table B wwm3, but not the corresponding row in table a wwm2.
Look back.
Transaction a Run
Select a.object_name,a.object_id from wwm2 a,wwm3 b
2 where b.status= ' VALID ' and a.object_id=b.object_id
3* for update of B.status
Then transaction B can perform DML operations on the corresponding rows in table a wwm2, but not the corresponding rows in table B wwm3.
That is, the lock is still a row, except that if it is not added, the lock of all the tables involved is added to the table that will only lock the words.
NoWait (If you must use for UPDATE, I would recommend adding nowait)
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"
The following uses are also recommended and should be considered as appropriate.
For UPDATE wait 5
5 Seconds after the prompt appears:
Ora-30006:resource busy; Acquire with wait timeout expired
For UPDATE nowait SKIP LOCKED;
Prompt appears:
No rows selected
TABLE LOCKS
LOCK table Table (s) in EXCLUSIVE MODE [nowait];
Lock is also released at the end of transaction.
Deadlock:
Transaction a lock Rowa, then transaction B lock ROWB
Then transaction a tries to lock ROWB,
and transaction B tries to lock Rowa
That is, two of transaction each try to lock each other's already locked row, waiting for the other to release their lock, so that the deadlock. In addition, deadlock will also have 600 hints.
