SQL Locking basic information in oracle database

When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows . Rows are locked when the rows have been changed. This means that rows are locked when you issue a "Update" statement. In this situation (i.e when rows are locked by some user) other users will be able to read those records as they appeared before the change ..But they cannot change the contents of the locked row..

In some cases you might want to lock the rows first and then want to update the rows..For this purpose you can use the "for update" and "for update of xxxx" and "for update nowait" options..

For example..
SELECT * FROM EMP WHERE EMPNO=7654 FOR UPDATE NOWAIT

When you issue a SELECT...FOR UPDATE statement, the RDBMS automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, holding the records "for your changes only" as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT.

This statement (SELECT....FOR UPDATE) will block the user who issued the statement , if it cannot get a lock on any of the rows. To prevent this blocking, user can use the NOWAIT option. If the "NOWAIT" option is provided, then the statement
SELECT...FOR UPDATE NOWAIT
will return an error status if it could'nt lock all the rows identified by the SELECT statement. If its able to lock all the rows, it retuns the result set containing all the rows identified by the SELECT statement and will lock all those rows.


All these locks are realeased once the user commits or rollbacks the data.


After any row is updated and committed, sql maintains information like when a row has been locked and when the lock has been released. This information can be obtained with query like..

SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME,VERSIONS_XID,
EMPNO FROM EMP VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP

You can find this information only after committing the data..